Searching MySQL data

Question

Hi guys.. I am trying to search MySQL database with a search key entered by the user. My data contain upper case and lower case. My question is how to make my search function not case sensitive. ex:data in mysql is BOOK but if the user enters book in search input. The result is not found....Thanks..

My search code

$searchKey=$_POST['searchKey'];
$searchKey=mysql_real_escape_string($searchKey);

$result=mysql_query("SELECT *
            FROM product
            WHERE product_name like '%$searchKey%' ORDER BY product_id
                            ",$connection); 

Answer 1

This is an easy way to do it:

$searchKey=strtoupper($searchKey);

SELECT *
FROM product
WHERE UPPER(product_name) like '%$searchKey%' ORDER BY product_id

Answer 2

Just uppercase the search string and compare it to the uppercase field.

$searchKey= strtoupper($_POST['searchKey']);
$searchKey=mysql_real_escape_string($searchKey);

$result=mysql_query("SELECT * FROM product
        WHERE UPPER(product_name) like '%$searchKey%' ORDER BY product_id
         ",$connection); 

Answer 3

First of all, try to avoid using * as much as possible. It is generally considered a bad idea. Select the columns using column names.

Now, your solution would be -

$searchKey=strtoupper($_POST['searchKey']);
$searchKey=mysql_real_escape_string($searchKey);

$result=mysql_query("SELECT product_name,
                            // your other columns
                    FROM product
                    WHERE UPPER(product_name) like '%$searchKey%' ORDER BY product_id
                        ",$connection);

EDIT

I will try to explain why it is a bad idea to use *. Suppose you need to change the schema of the product table(adding/deleting columns). Then, the columns that are being selected through this query will change, which may cause unintended side effects and will be hard to detect.

Answer 4

According to the MySQL manual, case-sensitivity in searches depends on the collation used, and should be case-insensitive by default for non binary fields.

Make sure you have the field types and the query right (maybe there's an extra space or something). If that doesn't work, you can convert the string to upper case in PHP (ie: $str = strtoupper($str)) and do the same on the MySQL side (@despart)

EDIT: I posted the article above (^). AndI just tested it. Searches on CHAR, VARCHAR, and TEXT fields are case-insensitive (collation = latin1)

Answer 5

If possible, you should avoid using UPPER as a solution to this problem, as it incurs both the overhead of converting the value in each row to upper case, and the overhead of MySQL being unable to use any index that might be on that column.

If your data does not need to be stored in case-sensitive columns, then you should select the appropriate collation for the table or column. See my answer to how i can ignore the difference upper and lower case in search with mysql for an example of how collation affects case sensitivity.

The following shows the EXPLAIN SELECT results from two queries. One uses UPPER, one doesn't:

DROP TABLE IF EXISTS `table_a`;
CREATE TABLE `table_a` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` varchar(255) DEFAULT NULL,
  INDEX `value` (`value`),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO table_a (value) VALUES
('AAA'), ('BBB'), ('CCC'), ('DDD'),
('aaa'), ('bbb'), ('ccc'), ('ddd');

EXPLAIN SELECT id, value FROM table_a WHERE UPPER(value) = 'AAA';
+----+-------------+---------+-------+---------------+-------+---------+------+------+--------------------------+
| id | select_type | table   | type  | possible_keys | key   | key_len | ref  | rows | Extra                    |
+----+-------------+---------+-------+---------------+-------+---------+------+------+--------------------------+
|  1 | SIMPLE      | table_a | index | NULL          | value | 258     | NULL |    8 | Using where; Using index |
+----+-------------+---------+-------+---------------+-------+---------+------+------+--------------------------+

EXPLAIN SELECT id, value FROM table_a WHERE value = 'AAA';
+----+-------------+---------+------+---------------+-------+---------+-------+------+--------------------------+
| id | select_type | table   | type | possible_keys | key   | key_len | ref   | rows | Extra                    |
+----+-------------+---------+------+---------------+-------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | table_a | ref  | value         | value | 258     | const |    2 | Using where; Using index |
+----+-------------+---------+------+---------------+-------+---------+-------+------+--------------------------+

Notice that the first SELECT which uses UPPER has to scan all the rows, whereas the second only needs to scan two - the two that match. On a table this size, the difference is obviously imperceptible, but with a large table, a full table scan can seriously impact the speed of your query.

Answer 6

you can try MATCH too.