1

I got a search query fully work but only if i use the correct term (i.e. 'iPhone 6 Plus' but if i search only 'iPhone' it will return nothing.

My search query is: SELECT * FROM products WHERE name LIKE '" . $term . "' OR brand LIKE '" . $term . "' LIMIT 0 , 30"

So how I can create a better query that can search better?

Thanks a Lot to all who can help.

andreaem
  • 1,635
  • 2
  • 20
  • 49

1 Answers1

2

In order to do matches on partial search terms you have to add some wildcards to your queries, the %:

SELECT * FROM products WHERE name COLLATE UTF8_GENERAL_CI LIKE '%" . $term . "%' OR brand COLLATE UTF8_GENERAL_CI LIKE '%" . $term . "%' LIMIT 0 , 30"

You could just add COLLATE UTF8_GENERAL_CI to your column definitions and then you would not have to modify your queries. For example:

ALTER TABLE products MODIFY COLUMN name VARCHAR(...) CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI.

If you need something more complex you will want to look a fulltext searches.

The Latin collation (LATIN1_GENERAL_CS)is one of those which is known to work well with case insensitive searches. If the one I specified (I try to always use UTF-8) doesn't work, substitute the Latin collation.

Jay Blanchard
  • 34,243
  • 16
  • 77
  • 119
  • Thanks Jay! but is case sensitive, there is a way to search without case sensitives? – andreaem Feb 15 '16 at 19:16
  • Ah - you didn't indicate that earlier. Outside of something much more complex [it is all about the collation used](http://dev.mysql.com/doc/refman/5.7/en/case-sensitivity.html) on the table @andreaem See my update. – Jay Blanchard Feb 15 '16 at 19:19
  • Don't work, got no result even if i use 'iPhone' instead of 'iphone' – andreaem Feb 15 '16 at 19:23
  • Did you alter the table? Or the query? – Jay Blanchard Feb 15 '16 at 19:25
  • Nope, if i send the query using PHPmyadmin i got this: COLLATION 'UTF8_GENERAL_CI' is not valid for CHARACTER SET 'binary' – andreaem Feb 15 '16 at 19:25
  • If that is the case you might have to modify your MySQL .ini file and restart. Try my suggested change above first though. – Jay Blanchard Feb 15 '16 at 19:28
  • Thanks, i've Alter the table but nothing changed, what type of modify to do in my.cfg ? – andreaem Feb 15 '16 at 19:32
  • Can you run `SHOW VARIABLES LIKE '%char%';` first and post the results? – Jay Blanchard Feb 15 '16 at 19:33
  • I got character_set_client utf8 character_set_connection utf8 character_set_database utf8 character_set_filesystem binary character_set_results utf8 character_set_server latin1 character_set_system utf8 character_sets_dir /usr/share/mysql/charsets/ – andreaem Feb 15 '16 at 19:34
  • Make sure `character-set-server = utf8` and `collation-server = utf8_general_ci` are uncommented in your my.cnf. If you make a change to the file you'll have to restart your MySQL server. – Jay Blanchard Feb 15 '16 at 19:37
  • I haven't these lines in my.cfg so I've added and restart mysql, retry the search and got nothing – andreaem Feb 15 '16 at 19:49
  • character_set_client utf8 character_set_connection utf8 character_set_database utf8 character_set_filesystem binary character_set_results utf8 character_set_server utf8 character_set_system utf8 character_sets_dir /usr/share/mysql/charsets/ – andreaem Feb 15 '16 at 19:52
  • The character set changed properly. Can you setup a SQL fiddle? – Jay Blanchard Feb 15 '16 at 19:57
  • Damn, i'm not able, how can I do? – andreaem Feb 15 '16 at 19:59
  • Case insensitivity works there right out of the gate @andreaem :-/ http://sqlfiddle.com/#!9/0069ab/2 Your column collation is ` COLLATE utf8_bin` – Jay Blanchard Feb 15 '16 at 20:34
  • Yes, i see and working using query posted on your answer, i can't understand why not working on my c9 workspace – andreaem Feb 15 '16 at 20:36
  • Try with `COLLATE utf8_bin` in the query. – Jay Blanchard Feb 15 '16 at 20:37
  • i've tried to change the query as `SELECT * FROM 'products' WHERE name LIKE '%" . $term . "%' OR brand LIKE '%" . $term . "%SELECT * FROM products WHERE name COLLATE utf8_bin LIKE '%" . $term . "%' OR brand COLLATE utf8_bin LIKE '%" . $term . "%' LIMIT 0 , 30` – andreaem Feb 15 '16 at 20:46
  • At this point, without being able to look at your MySQL server configuration, it would be hard to know what to try next. Everything that we have done to this point should have gotten you up and running. You could try recreating the table using a non-binary collation (still use UTF-8 though) and see if that works. If not, try one of the Latin collations. – Jay Blanchard Feb 15 '16 at 20:49
  • 1
    I've tried to delete and import again, nothing changed... Thanks Jay, don't worry, let it be for now and I will back to this later – andreaem Feb 15 '16 at 21:03