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.
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
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.
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();
"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