MySQL查询在网站中不起作用，在MySQL程序中工作

Question

我有这个代码：

mysql_query("
   SELECT id as tc_id, firstname, surname, position, @concat := CONCAT(firstname,' ',surname) 
   FROM tc 
   WHERE (@concat LIKE '%". mysql_real_escape_string($_REQUEST['search_term']) ."%');");

search_term仅指文本输入的值。

当我在本地运行续集专业版中的 SELECT 语句时 - 我的 mysql 程序 - 它运行良好，选择适当的行。但是，当我通过我的 PHP 页面在本地运行查询时，它不返回任何行，即使它应该返回。帮助任何人？

Answer 1

WHERE不适用于

计算列，因此查询本身将不起作用。

它可能有效，因为您在本地声明了一个@concat变量。如果在特定条件下运行查询两次，它看起来也会起作用，因为您的查询实际上可能会分配一个@concat变量。

你想要的是

SELECT id as tc_id, firstname, surname, position,
    CONCAT(firstname,' ',surname)  AS concat
FROM tc HAVING concat LIKE '%<YOUR SEARCH TERM>%';

作为测试：

-- Declare a minimum table to match the query
CREATE TABLE tc (id integer, firstname varchar(20), surname varchar(20), position integer);
INSERT INTO tc (firstname, surname) VALUES ('alfa', 'bravo');
-- Your query...
SELECT id as tc_id, firstname, surname, position,
    @concat := CONCAT(firstname,' ',surname) FROM tc WHERE (@concat LIKE '%alfa%');
-- ...returns nothing
Empty set (0.00 sec)
-- The proper query works.
SELECT id as tc_id, firstname, surname, position, CONCAT(firstname,' ',surname)  AS concat FROM tc HAVING concat LIKE '%alfa%';
+-------+-----------+---------+----------+------------+
| tc_id | firstname | surname | position | concat     |
+-------+-----------+---------+----------+------------+
|  NULL | alfa      | bravo   |     NULL | alfa bravo |
+-------+-----------+---------+----------+------------+
1 row in set (0.00 sec)
-- But if I declare a @concat variable    
SELECT @concat := 'alfa';
+-------------------+
| @concat := 'alfa' |
+-------------------+
| alfa              |
+-------------------+
1 row in set (0.00 sec)
-- Then your query SEEMS to work.
mysql> SELECT id as tc_id, firstname, surname, position, @concat := CONCAT(firstname,' ',surname)  FROM tc  WHERE (@concat LIKE '%alfa%');
+-------+-----------+---------+----------+------------------------------------------+
| tc_id | firstname | surname | position | @concat := CONCAT(firstname,' ',surname) |
+-------+-----------+---------+----------+------------------------------------------+
|  NULL | alfa      | bravo   |     NULL | alfa bravo                               |
+-------+-----------+---------+----------+------------------------------------------+
1 row in set (0.00 sec)
-- "SEEMS" because the select query isn't actually working:
UPDATE tc SET firstname = 'delta';
Query OK, 1 row affected (0.28 sec)
Rows matched: 1  Changed: 1  Warnings: 0
-- Having renamed the only row to "delta", a search for "alpha" should fail,
-- but since @concat still holds 'alpha', then the query matches ALL rows:
mysql> SELECT id as tc_id, firstname, surname, position, @concat := CONCAT(firstname,'    ',surname)  FROM tc  WHERE (@concat LIKE '%alfa%');
+-------+-----------+---------+----------+------------------------------------------+
| tc_id | firstname | surname | position | @concat := CONCAT(firstname,' ',surname) |
+-------+-----------+---------+----------+------------------------------------------+
|  NULL | delta     | bravo   |     NULL | delta bravo                              |
+-------+-----------+---------+----------+------------------------------------------+
1 row in set (0.00 sec)

Answer 2

试试这个：

mysql_query("
   SELECT id as tc_id, firstname, surname, position, CONCAT(firstname,' ',surname) as concatt 
   FROM tc 
   WHERE (CONCAT(firstname,' ',surname) LIKE '%". mysql_real_escape_string($_REQUEST['search_term']) ."%');");

Answer 3

这是因为您的 Sequel Pro 将查询语句转换为 MySql 查询，而 MySql 不明白"@concat喜欢..."是什么意思。

尝试将 PHP 代码上的 where 子句更改为"where CONCAT（firstname"，"，surname） LIKE '%..."

下面完整代码：

mysql_query("SELECT id as tc_id, firstname, surname, position, CONCAT(firstname,' ',surname) FROM tc WHERE (CONCAT(firstname,' ',surname) LIKE '%". mysql_real_escape_string($_REQUEST['search_term']) ."%');");