MySQL查询在phpmyadmin中工作，但PHP结果集中没有返回任何行

问题描述：

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.

我有以下MySQL查询： p>

  SELECT SQL_CALC_FOUND_ROWS p  。* 
FROM产品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 
  code>   pre> 
 
 此查询用于获取以下产品： p> 
 
 
在 p.categories  code>中设置了一些内容 列，但 productCategory  code>表中的行不存在 li> 
 
有一个 productCategory  code>行，但 productCategory  row设置为'D' li> 
 
  p.categories  code>列中没有设置任何内容 li> 
  ul> 
 
   p.categories  code>是一行，其中空格分隔的数值代表 productCategory  code>表中的ID。 不是最好的设计，但我已经完成了这个任务，所以需要一种方法。 p> 
 
 在PHP代码中，此查询直接执行： p> \  n 
 
  SELECT FOUND_ROWS（）as count 
  code>  pre> 
 
 从phpmyadmin执行时，我返回了数千行，当我追加第二个查询时 ，我得到正确行数的计数值。 当我通过mysqli_query（）执行相同的第一个查询时，我得不到所有返回的行，并且以下查询返回计数为1。 p> 
 
 
 phpmyadmin和我的PHP代码都连接到 相同的数据库。 p> 
 
 
任何人都可以看到出了什么问题吗？！ p> 
 
 
 更新： strong>  p> 
 \  n 
我只想要返回前30行，因为可能有一个巨大的总数，在后续查询中，我应该得到总数的计数，因此在第一个SELECT之后的SQL_CALC_FOUND_ROWS。 p> \  n  div>

答

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.

答

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);

MySQL查询在phpmyadmin中工作，但PHP结果集中没有返回任何行

相关推荐