任何想法都会有所帮助,我已经在这个问题上有一段时间了,只是不知道哪里出了问题。
问题:查询工作正常,直到我添加了多字搜索,你可以看到下面的粗体。然而,我回了sql查询,然后粘贴在phpmyadmin,它工作完美,但通过php它的结果与0记录。这没有任何意义,我不知道是什么导致了结果为0。
SELECT
DISTINCT c.id
FROM
carpets AS c
INNER JOIN carpet_relations AS r1 ON c.id = r1.carpet_id
INNER JOIN carpet_relations AS r2 ON c.id = r2.carpet_id
INNER JOIN carpet_relations AS r3 ON c.id = r3.carpet_id
WHERE
c.active = '1'
AND ((c.title LIKE '%north tabriz%') OR **(c.title LIKE '%north%') OR (c.title LIKE '%tabriz%')** OR (c.item_no LIKE '%north tabriz%') OR **(c.item_no LIKE '%north%') OR (c.item_no LIKE '%tabriz%')** OR (c.pattern LIKE '%north tabriz%') OR **(c.pattern LIKE '%north%') OR (c.pattern LIKE '%tabriz%')** OR (c.period LIKE '%north tabriz%') OR **(c.period LIKE '%north%') OR (c.period LIKE '%tabriz%')** OR (c.country LIKE '%north tabriz%') **OR (c.country LIKE '%north%') OR (c.country LIKE '%tabriz%')**)
AND (c.width_feet BETWEEN '0' AND '22')
AND (c.width_inches BETWEEN '0' AND '11')
AND (c.height_feet BETWEEN '0' AND '49')
AND (c.height_inches BETWEEN '0' AND '11')
ORDER BY
c.item_no
id int(11) NO PRI NULL auto_increment
active int(11) NO NULL
title varchar(250) NO NULL
item_no varchar(250) NO NULL
country varchar(250) NO NULL
period varchar(250) NO NULL
pattern varchar(250) NO NULL
price float NO NULL
web_special float NO NULL
notes text NO NULL
width_feet int(11) NO NULL
width_inches int(11) NO NULL
height_feet int(11) NO NULL
height_inches int(11) NO NULL
restrict int(11) NO NULL
views_amount int(11) NO NULL
last_modified datetime NO NULL
modified_by int(11) NO NULL
也要密切注意在PHP中如何使用引号。对于带有where子句的SQL查询,这可能是一个问题,因为where子句需要在单引号中表示where x = 'value'。因此,确保整个SQL字符串使用双引号。此外,单引号中的PHP变量将不会被求值,因此,如果您基于PHP变量的where子句,则需要在SQL语句中使用该变量之前将其用单引号括起来。我希望所有这些都是有意义的,当我开始学习PHP/MySQL时,我被这个问题困了2天。
试试这个:
SELECT
DISTINCT c.id
FROM
carpets AS c
INNER JOIN carpet_relations AS r1 ON c.id = r1.carpet_id
INNER JOIN carpet_relations AS r2 ON c.id = r2.carpet_id
INNER JOIN carpet_relations AS r3 ON c.id = r3.carpet_id
WHERE
c.active = '1'
AND ((c.title LIKE '%north%')
OR (c.title LIKE '%tabriz%')
OR (c.item_no LIKE '%north%')
OR (c.item_no LIKE '%tabriz%')
OR (c.pattern LIKE '%north%')
OR (c.pattern LIKE '%tabriz%')
OR (c.period LIKE '%north%')
OR (c.period LIKE '%tabriz%')
OR (c.country LIKE '%north%')
OR (c.country LIKE '%tabriz%'))
AND (c.width_feet BETWEEN 0 AND 22)
AND (c.width_inches BETWEEN 0 AND 11)
AND (c.height_feet BETWEEN 0 AND 49)
AND (c.height_inches BETWEEN 0 AND 11)
ORDER BY
c.item_no
我去掉了"LIKE '%north tabriz%'"部分,因为它们是多余的,因为如果它只匹配"north tabriz",那么"north"answers"tabriz"也将为真。
同时,我去掉了between子句中数字周围的单引号。
不能复制表来测试它,因为你只有一个表显示,但希望这有助于
先尝试简单查询(以测试连接)如果他们不工作查看数据库主机,用户名,密码(他们可能是错误的输入)
一旦你确定了db连接,尝试在php中使用多查询功能,删除不必要的空格,在for循环或多个步骤中运行查询
希望能有所帮助
这很可能不是查询,而是PHP代码中的某个错误。你能把你的PHP代码贴出来让我们看看吗?
有时我得到这个问题,它只发生在有任何问题的连接或或您的查询。请打印您的查询,看看它如何打印。在查询中,您必须使用$var
尝试在php代码中正确处理(")。如果你的查询在phpMyAdmin中工作得很好,那么它应该在你的php代码中工作。或者您的数据库连接可能出现错误。