Jump to content


Photo

MySQL LIKE being case sensitive!


  • Please log in to reply
2 replies to this topic

#1 2muchspam

2muchspam
  • Members
  • Pip
  • Newbie
  • 5 posts
  • LocationPortland, OR

Posted 15 October 2003 - 06:14 PM

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

<?phpinclude \'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 searchif (!$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 tableecho \'<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\'].\'&nbsp;\'.$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\';?>


#2 shivabharat

shivabharat
  • Members
  • PipPipPip
  • Advanced Member
  • 371 posts
  • LocationChennai, India

Posted 15 October 2003 - 06:58 PM

Read more about BINARY


mysql>SELECT "a" = "A";        -> 1mysql> SELECT BINARY "a" = "A";        -> 0

Knowledge --- Reading Enriches Mind But Sharing Enhances It.[br][br]Note: Before you request help enusre that you have had a look at the tutorials @phpfreaks

#3 2muchspam

2muchspam
  • Members
  • Pip
  • Newbie
  • 5 posts
  • LocationPortland, OR

Posted 15 October 2003 - 08:00 PM

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!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users