2

In my php script,

$names = $_GET['part'];
$result = mysql_query("SELECT * FROM  namestable where names LIKE'%$names%' LIMIT 10");
while ($row = mysql_fetch_assoc($result)) {
        $colors[]=$row['publishers'];
}

checks for matches and works well.

But suppose my table has a name Alfred, the suggestion will appear only if i type Alfr and not appearing if i type alfr

hakre
  • 193,403
  • 52
  • 435
  • 836
Alfred
  • 21,058
  • 61
  • 167
  • 249

1 Answers1

1

The example you've provided will work if you're using a case insensitive collation such as utf8_general_ci. (See the MySQL Character Set Support manual section for more information.)

Alternatively, you could simply use the LOWER function as follows:

$names = strtolower(mysql_real_escape_string($_GET['part']));
$result = mysql_query("SELECT * FROM namestable WHERE names LIKE LOWER('%$names%') LIMIT 10");

Incidentally, if you're attempting to catch differences beyond simple case changes, you could also use MySQL's SOUNDEX function to obtain and find a match for strings that sound similar.

Incidentally, you need to use mysql_real_escape_string on your $names variable, or (better still) use prepared statements via the mysqli or PDO interfaces.

John Parker
  • 54,048
  • 11
  • 129
  • 129
  • so can u suggest me the corrections i have to make in the code?? – Alfred Feb 13 '11 at 15:41
  • it is saying in the documentation that in soundex `All nonalphabetic characters in str are ignored.` I want to include numbers too.. – Alfred Feb 13 '11 at 15:43
  • @blasteralfred I've updated my answer with a simple solution. As I've said soundex if only if you want to compare similar sounding words - the other solutions I've mentioned would be more appropriate. – John Parker Feb 13 '11 at 15:44
  • 1
    not working.. i think i have to spend a lot with my code ... :( – Alfred Feb 13 '11 at 16:05