MySQL query doesn't work in website, works in MySQL program

Question

I have this code:

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 just refers to the value of a text input.

When I run the SELECT statement in sequel pro locally - my mysql program - it works perfectly, selecting the appropriate rows. When I run the query however locally through my PHP page, it returns no rows, even though it should. Help anyone?

Are you able to profile the mySql instance to see what command is reaching it? (It's called "profiling" in MS SQL, not sure about mySql sorry). — Sepster
– Sepster, Commented Sep 17, 2012 at 10:28
Either not connected at all or not connected to the database you think you are. — duffymo
– duffymo, Commented Sep 17, 2012 at 10:28
What does it means it returns "no row" ? Have you execute the statement or only declared it? Have yo fetched any row? Please, take a look at my answer here — DonCallisto
– DonCallisto, Commented Sep 17, 2012 at 10:28
Have you checked whether the query executed or it failed? Are you sure you are 1) connected at all 2) connected to the right database? Are you sure your query is even valid? — N.B.
– N.B., Commented Sep 17, 2012 at 10:32

LSerni · Accepted Answer · 2012-09-17 10:39:47Z

WHERE does not work on calculated columns, therefore your query, per se, will not work.

It probably works because you locally have a @concat variable declared. It will also appear to work if you run the query twice in specific conditions, because your query might actually assign a @concat variable.

What you want is

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

As test:

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

Prasath Albert · Accepted Answer · 2012-09-17 10:39:14Z

0

Try this one:

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']) ."%');");

answered Sep 17, 2012 at 10:39

Prasath Albert

1,45710 silver badges20 bronze badges

Comments

Habibillah · Accepted Answer · 2012-09-17 10:43:47Z

0

It's because your Sequel Pro translate the query statement to become MySql query, and MySql not understand what mean about "where @concat LIKE...".

Try change your where clause on PHP code become "where CONCAT(firstname,' ',surname) LIKE '%..."

bellow the complete code:

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']) ."%');");

answered Sep 17, 2012 at 10:43

Habibillah

28.8k5 gold badges40 silver badges59 bronze badges

Collectives™ on Stack Overflow

MySQL query doesn't work in website, works in MySQL program

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related