database search question PHP

Question

hi

my code is this,

$query = "SELECT * FROM  `cars` WHERE  (color LIKE '%". $key ."%' OR name LIKE '%". $key ."%') AND enabled = 'yes'  ORDER BY  `ID`";

database is like this

ID     color       name        enabled
----  ------     --------     ---------
1       red        red car       yes
2      blue        blue car      yes
3       brown      brown car     yes

r

when i search with the key "red" it returns first field (ID 1) but if i search with "red car" it returns nothing.

how can i search both 2 field ?

thanks

EDIT: i fixed brackets but still i cant get results with more than 1 word keys.

Tried this with no luck

  $query = "SELECT * FROM  `cars` WHERE  (MATCH (color,name) AGAINST ('$key' IN BOOLEAN MODE)) AND enabled = 'yes'  ORDER BY  `ID`";

EDIT 2: Peter is right. There is no problen in query. weird thing is i use this

$key = $_GET['key'];

if $_GET['key'] is more than 1 word, $key is returns empty for a reason.

Answer 1

Your SQL is wrong :

$query = "SELECT * FROM  `cars` WHERE  (color LIKE '%". $key ."%' OR name LIKE '%". $key ."%') AND enabled = 'yes'  ORDER BY  `ID`";

You need to put color and name in brackets, otherwise you might find you will return rows when the field enabled is false.

Answer 2

You could use a prepared statement to prevent SQL injection:

$statement = $db_connection->prepare("SELECT * FROM  `cars` WHERE  (color LIKE ? OR name LIKE ?) AND enabled = 'yes'  ORDER BY  `ID`");
$statement->bind_param("s", "%".$key."%");
$statement->execute();

Answer 3

I can't duplicate your error. Here's the steps I took

New table

CREATE TABLE  `cars` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `color` varchar(45) NOT NULL,
  `name` varchar(45) NOT NULL,
  `enabled` varchar(45) NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Then added values

INSERT INTO `cars` (`ID`, `color`, `name`, `enabled`) VALUES
  (1,'red','red car','yes')
, (2,'blue','blue car','yes')
, (3,'brown','brown car','yes');

Then testing the queries

mysql> SELECT * FROM  `cars`
    ->  WHERE (color LIKE '%red%' OR name LIKE '%red%')
    ->    AND enabled = 'yes'
    ->  ORDER BY  `ID`;
+----+-------+---------+---------+
| ID | color | name    | enabled |
+----+-------+---------+---------+
|  1 | red   | red car | yes     |
+----+-------+---------+---------+
1 row in set (0.00 sec)

mysql> SELECT * FROM  `cars`
    ->  WHERE (color LIKE '%red car%' OR name LIKE '%red car%')
    ->    AND enabled = 'yes'
    ->  ORDER BY  `ID`;
+----+-------+---------+---------+
| ID | color | name    | enabled |
+----+-------+---------+---------+
|  1 | red   | red car | yes     |
+----+-------+---------+---------+
1 row in set (0.00 sec)

Maybe look elsewhere for your error

Answer 4

"EDIT 2: Peter is right. There is no problen in query. weird thing is i use this

$key = $_GET['key'];

if $_GET['key'] is more than 1 word, $key is returns empty for a reason."

How are you passing in the $_GET? Are you using a form or just typing in your variables

blah.php?key=test+and

NOT

blah.php?key=test and