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 theproductCategory
table does not exist - Have a
productCategory
row, but the status on theproductCategory
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.