dounanyin3179
2010-01-27 14:35
浏览 68

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.

图片转代码服务由CSDN问答提供 功能建议

我有以下MySQL查询:

  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 
    
 
 

此查询用于获取以下产品:

  • p.categories 中设置了一些内容 列,但 productCategory 表中的行不存在
  • 有一个 productCategory 行,但 productCategory <上的状态< / code> row设置为'D'
  • p.categories 列中没有设置任何内容 < p> p.categories 是一行,其中空格分隔的数值代表 productCategory 表中的ID。 不是最好的设计,但我已经完成了这个任务,所以需要一种方法。

    在PHP代码中,此查询直接执行: \ n

      SELECT FOUND_ROWS()as count 
       
     
     

    从phpmyadmin执行时,我返回了数千行,当我追加第二个查询时 ,我得到正确行数的计数值。 当我通过mysqli_query()执行相同的第一个查询时,我得不到所有返回的行,并且以下查询返回计数为1。

    phpmyadmin和我的PHP代码都连接到 相同的数据库。

    任何人都可以看到出了什么问题吗?!

    更新: \ n

    我只想要返回前30行,因为可能有一个巨大的总数,在后续查询中,我应该得到总数的计数,因此在第一个SELECT之后的SQL_CALC_FOUND_ROWS。 \ n

  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 邀请回答

2条回答 默认 最新

相关推荐 更多相似问题