php - Retrieve corresponding and non corresponding values from many to many table


I have a following table structure:

Brands => Histories <= Users

Histories table contains following columns:

brandId, userId, points (for each brand how many points user scored points).. I need to sum all the points for user for which he scored in between two dates in history table.

The query would accept 4 parameters: userId, brandId, startDate, endDate... The following output would be something like:

userId        brandId       points

1               64           155 sum of all points in between two dates specified by the parameter. 

If the passed value of BrandId is = null (ie. not passed). Query should return as well whole data about the brand and user if it doesn't finds any data in history table on that BrandId and UserId... I have written my own query in Zend Framework 2 but it doesn't outputs that I want (returns only records that are actually found in history table)...

It looks like this:

public function BrandWallBrandsById($userId,$brandId,$startDate,$endDate)
    $select = new Select();
    $select->from(array('p' => $this->table), array('id'));
    $select->join(array('b' => 'brands'), 'p.brandId =', array('id','name', 'cover', 'slogan', 'startDate', 'homepage','endDate','active'));
    $select->columns(array(new Expression('SUM(p.points) as points'), "userId", "brandId"));
    $select->order("p.points asc");
    $where = new Where();
    $where->notEqualTo("p.points", 0);
    $where->equalTo("p.userId", $userId);
    $where->equalTo("p.brandId", $brandId);
    $where->between("p.time", $startDate, $endDate);
    return $this->historyTable->selectWith($select)->toArray()[0];

Note that the query only returns single record at a time when its called.. I wanna write a pure SQL statement, since I think it might be easier than this... Can someone help me out?




Here is a SQL statement to get you what you want in SQL Server, though i'm not familiar with zend-framework.

declare @startDate datetime, @endDate datetime, @userId int = null, @brandId int = null
set @startDate = '6/1/2016'
set @endDate = '6/9/2016'
set @userId = 1      --this can be NULL
set @brandId = 64    --this can be NULL

    sum(points) as points
    (@userId is null or userId = @userId)
    and (@brandId is null or brandId = @brandId)
    and dateColumn >= @startDate
    and dateColumn < dateadd(day,1,@endDate) 
group by
    userId, brandId

People are also looking for solutions to the problem: JSON Output Cant Able to Get PHP


Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.