PHP MySQL Returning Different Results From MySQL

Question

When I run a query in MySQL I get an expected result. The query uses MySQL defined variables to group by top n. However, when running in PHP, it seems that the "IF(@businessType=businessType...) is not being evaluated correctly and causing it to select all results. The query is:

SELECT RowNum,store_ID,businessType,createDate,expirationDate 
FROM (
    SELECT IF(@businessType = businessType, @ctr := @ctr + 1, @ctr := 1) as RowNum, 
    @businessType := businessType as businessType, store_ID, createDate, expirationDate 
    FROM stores
    JOIN (SELECT @ctr := 1) AS a 
    WHERE businessType IN (1,2,3) 
    AND ZIP = '11217' 
    AND state = 'NY' 
    ORDER BY businessType, createDate DESC) AS b 
WHERE RowNum in (1,2,3)

The MySQL results are:

+--------+----------+--------------+---------------------+----------------+
| RowNum | store_ID | businessType | createDate          | expirationDate |
+--------+----------+--------------+---------------------+----------------+
|      1 |  4455977 |            1 | 2014-12-27 04:16:38 | 2014-12-31     |
|      2 |  4455977 |            1 | 2014-12-27 04:16:38 | 2014-12-31     |
|      3 |  1971257 |            1 | 2014-12-27 04:01:35 | 2014-12-31     |
|      1 |  3883533 |            2 | 2014-12-27 04:10:26 | 2015-01-01     |
|      2 |  3718085 |            2 | 2014-12-27 04:10:18 | 2015-01-01     |
|      3 |  3718085 |            2 | 2014-12-27 04:10:17 | 2015-01-01     |
|      1 |  2170979 |            3 | 2014-12-27 04:09:56 | 2015-01-10     |
|      2 |  2034241 |            3 | 2014-12-27 04:09:56 | 2015-01-10     |
|      3 |  2220899 |            3 | 2014-12-27 04:09:56 | 2015-01-10     |
+--------+----------+--------------+---------------------+----------------+

In PHP it is returning 50 results and the RowNum field isn't incrementing as expected. How can I correct this query so that it correctly respects the '@' symbol? Note: I am using single quotes in PHP so it shouldn't be evaluating the '@' symbol. Here is a snippet of the output:

Array
(
    [RowNum] => 1
    [businessType] => 1
    [createDate] => 2014-12-27 04:16:38
    [expirationDate] => 2014-12-31
)

Array
(
    [RowNum] => 1
    [businessType] => 1
    [createDate] => 2014-12-27 04:16:38
    [expirationDate] => 2014-12-31
)

Array
(
    [RowNum] => 1
    [businessType] => 1
    [createDate] => 2014-12-27 04:01:35
    [expirationDate] => 2014-12-31
)

Array
(
    [RowNum] => 1
    [businessType] => 1
    [createDate] => 2014-12-27 04:01:35
    [expirationDate] => 2014-12-31
)

Array
(
    [RowNum] => 1
    [businessType] => 1
    [createDate] => 2014-12-27 03:45:55
    [expirationDate] => 2014-12-29
)

...

Array
(
    [RowNum] => 1
    [businessType] => 3
    [createDate] => 2014-12-27 03:27:28
    [expirationDate] => 2014-12-29
)

score 1 · Accepted Answer · answered Dec 27 '14 at 09:29

Initialize @businessType variable

Try this:

SELECT RowNum,store_ID,businessType,createDate,expirationDate 
FROM (SELECT IF(@businessType = @businessType:=businessType, @ctr := @ctr + 1, @ctr := 1) as RowNum, 
             businessType, store_ID, createDate, expirationDate 
      FROM stores, (SELECT @ctr := 1, @businessType := 0) AS a 
      WHERE businessType IN (1,2,3) AND ZIP = '11217' AND state = 'NY' 
      ORDER BY businessType, createDate DESC
    ) AS b 
WHERE RowNum in (1,2,3);

Just curious, do you know why it worked in MySQL directly but not PHP? — user2694306, Dec 27 '14 at 10:39

PHP MySQL Returning Different Results From MySQL

1 Answers1