Mysql search case insensitive

Question

I'm having problems searching in multiple tables for different values. When I search for "paul" I get nothing, but if I search for "Paul" I get the corresponding persons for orders with the name Paul.

$get_orders = mysql_query("
SELECT
  co.id, co.final_id, co.shop_id, co.customer_id, co.payment_type, co.payment_currency, co.billing_email, co.billing_first_name, co.billing_last_name, co.delivery_first_name, co.delivery_last_name, UNIX_TIMESTAMP(co.order_created) AS order_created, c.email, s.site_name, 
MATCH(co.final_id, co.billing_first_name, co.billing_last_name, co.delivery_first_name, co.delivery_last_name, co.order_created) 
AGAINST ('$match_against' IN BOOLEAN MODE) AS score 
FROM customer_orders AS co 
LEFT JOIN customers AS c ON c.id = co.customer_id 
LEFT JOIN shops AS s ON s.id = co.shop_id WHERE co.status = '{$os}' 
ORDER BY score DESC 
LIMIT $offset,$views_page") or die(mysql_error());

I've search all over for a solution to this problem. I've tried using UPPER, changing the database collation from utf8_general_ci to utf8_bin (binary) but my problem still remains unsolved..

All suggestions are appreciated..

Regards

adamdehaven · Accepted Answer · 2013-06-05 20:24:27Z

1

from the mysql manual:

The default character set and collation are latin1 and latin1_swedish_ci, so nonbinary string comparisons are case insensitive by default. This means that if you search with col_name LIKE 'a%', you get all column values that start with A or a. To make this search case sensitive, make sure that one of the operands has a case sensitive or binary collation. For example, if you are comparing a column and a string that both have the latin1 character set, you can use the COLLATE operator to cause either operand to have the latin1_general_cs or latin1_bin collation:

see: mysql case sensitivity

edited Jun 5, 2013 at 20:24

adamdehaven

5,92010 gold badges64 silver badges84 bronze badges

answered Apr 14, 2011 at 22:00

FoneyOp

3361 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Zirak · Accepted Answer · 2011-04-14 21:59:31Z

1

See http://bugs.mysql.com/bug.php?id=22343

From my understanding, make sure everything is a string if you want to search for a string.

Also, switch charset back to case-insensitive. No need for it to be binary.

answered Apr 14, 2011 at 21:59

Zirak

40k13 gold badges85 silver badges92 bronze badges

Collectives™ on Stack Overflow

Mysql search case insensitive

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related