Jump to content


This topic is now archived and is closed to further replies.


MySQL LIKE being case sensitive!

Recommended Posts

Hello again,

I\'m stuck again.


I\'ve built a search page for my db. I have a record for the name \"Bill Gates\". If I search for the first name Bill, it finds it. If I search for bill (lower case B) it doesn\'t find it. I\'m using LIKE so it should fing it. Ex:

mysql> SELECT \'abc\' LIKE \'ABC\';

-> 1


But it\'s not working so what the heck is going on????



include \'header.php\';

include \'db.php\';

//Create short variables from POST data

$searchtype = $_POST[\'searchtype\'];

$searchterm = $_POST[\'searchterm\'];

//Trim off whitespace

$searchterm = trim($searchterm);

//Checks for null search

if (!$searchterm || !$searchtype){

echo \'You did not select anything to search for! Please go back and try again.\';



//Add MySQL slashes so as to not destroy the query with bad data. I know this is anal.

$searchterm = addslashes($searchterm);

$searchtype = addslashes($searchtype);

//Query database

$db_query = "SELECT * FROM incident.violators WHERE ".$searchtype." LIKE \'%".$searchterm."%\'";

$db_result = mysql_query($db_query) or die("Error in query:".mysql_error());

$num_results = mysql_num_rows($db_result);

echo \'<p><br /><b>UP Incident Reporter</b></p>\';

echo \'<br /><br />\';

echo \'<div class="h">Search Results</div>\';

echo \'<div class="t">\';

echo \'<p>Nuber of Matches: \'.$num_results.\'</p>\';

//Loop through database results and build table

echo \'<center><table width="80%" border="0" cellspacing="0" cellpadding="2"><tr>\';

echo \'<td bgcolor="cccccc"><b>Name</b></td>

  <td bgcolor="cccccc"><b>Phone</b></td>

  <td bgcolor="cccccc"><b>Email</b></td>

  <td bgcolor="cccccc"><b>Address</b></td>

  <td bgcolor="cccccc"><b>Violation</b></td>

  <td bgcolor="cccccc"><b>Disconnect</b></td></tr>\';

while ($result = mysql_fetch_array($db_result)) {

echo \'<tr>\';

echo \'<td>\'.$result[\'firstName\'].\' \'.$result[\'lastName\'].\'</td>\';

echo \'<td>\'.$result[\'phone\'].\'</td>\';

echo \'<td>\'.$result[\'email\'].\'</td>\';

echo \'<td>\'.$result[\'address\'].\'</td>\';

echo \'<td>\'.$result[\'violation\'].\'</td>\';

echo \'<td>\'.$result[\'disconnect\'].\'</td>\';

echo \'</tr>\';


echo \'</table></center>\';

echo \'</div>\';

echo \'<br /><br /><br /><br />\';

include \'footer.php\';


Share this post

Link to post
Share on other sites

Read more about BINARY



mysql>SELECT "a" = "A";

       -> 1

mysql> SELECT BINARY "a" = "A";

       -> 0

Share this post

Link to post
Share on other sites

WooHoo! Found the problem. My table field (varchar) was set as BINARY. I changed it to nothing and all is well with the world.

Thanks for your time!

Share this post

Link to post
Share on other sites


Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.