Jump to content

Archived

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

2muchspam

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????

 


<?php

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.\';

exit;

}



//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.