MySQL query works in phpmyadmin but no rows returned in PHP result set

Question

I have the following MySQL query:

SELECT SQL_CALC_FOUND_ROWS p.*
FROM product AS p
LEFT JOIN productCategory AS c ON FIND_IN_SET(c.id, REPLACE(TRIM(p.categories), ' ',','))
WHERE (
    c.id IS NULL
    OR c.status = 'D'
    OR p.categories IS NULL
    OR TRIM(p.categories) = ''
)
AND p.deprecated = 'N'
LIMIT 0,30

This query is to pick up products which:

• Has something set in p.categories column, but the row on the productCategory table does not exist
• Have a productCategory row, but the status on the productCategory row is set to 'D'
• Have nothing set in the p.categories column

p.categories is a row with space separated numeric values representing ids on the productCategory table. Not the best design, granted but I have been given this task, so need a way of doing it.

In the PHP code, this query is executed directly afterwards:

SELECT FOUND_ROWS() as count

When executed from phpmyadmin, I get thousands of rows returned and when I append the second query, I get a count value of the correct number of rows. When I execute the same first query through mysqli_query(), I get no rows at all returned and the following query returns a count of 1.

Both phpmyadmin and my PHP code are connecting to the same database.

Can anyone see what is going wrong?!

Update:

I only want the first 30 rows returned because there could be a huge total number, in the subsequent query, I should get a count of what that total would be, hence SQL_CALC_FOUND_ROWS after the first SELECT.

Answer 1

You need to perform the first query only to get the record-set returned to PHP. You can count in your PHP once you have it. You shouldn't pass concatenated queries from PHP to MySQL.

i.e.

$result = mysql_query($query);
$recordCount = mysql_num_rows($result);

Answer 2

For some reason, changing the first line of the query:

SELECT SQL_CALC_FOUND_ROWS p.*

To:

SELECT SQL_CALC_FOUND_ROWS p.id

And then looping through the results getting the data I needed from each row in separate queries got the result I was after.