MySQL 查询在 phpmyadmin 中有效，但在 PHP 结果集中没有返回行 [英] MySQL query works in phpmyadmin but no rows returned in PHP result set

问题描述

我有以下 MySQL 查询:

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:

• 在p.categories 列中设置了一些内容，但productCategory 表上的行不存在
• 有一个 productCategory 行，但 productCategory 行上的状态设置为D"
• 在 p.categories 列中没有设置任何内容

• 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 是一行，用空格分隔的数值表示 productCategory 表上的 id.不是最好的设计，当然，但我被赋予了这项任务，所以需要一种方法.

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.

在PHP代码中，这个查询是在之后直接执行的:

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

SELECT FOUND_ROWS() as count

当从 phpmyadmin 执行时，我得到数千行返回，当我附加第二个查询时，我得到正确行数的计数值.当我通过 mysqli_query() 执行相同的第一个查询时，我根本没有返回任何行，并且以下查询返回的计数为 1.

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.

phpmyadmin 和我的 PHP 代码都连接到同一个数据库.

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

谁能看出哪里出了问题?！

Can anyone see what is going wrong?!

更新:

我只想要返回前 30 行，因为总数可能很大，在后续查询中，我应该得到总数的计数，因此在第一个 SELECT 之后返回 SQL_CALC_FOUND_ROWS.

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.

推荐答案

由于某些原因，更改了查询的第一行:

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

SELECT SQL_CALC_FOUND_ROWS p.*

致:

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.

这篇关于MySQL 查询在 phpmyadmin 中有效，但在 PHP 结果集中没有返回行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！