how to make LIKE %word% not case sensitive

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

psychotomus
Forum Contributor
Posts: 487
Joined: Fri Jul 11, 2003 1:59 am

how to make LIKE %word% not case sensitive

Post by psychotomus »

I have a search feature, but it wont return results unless there case sensitive

for example: searching for naruto will not find results Naruto


any solution to this?

Code: Select all

$result = mysql_query("SELECT user_avatar,user_join_date, username,user_points,user_friends,user_high_scores, user_poems,user_fanfics,user_comics FROM users WHERE $select_from LIKE '%$search_phrase%' LIMIT $start,50") or die(mysql_error());
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

psychotomus
Forum Contributor
Posts: 487
Joined: Fri Jul 11, 2003 1:59 am

Post by psychotomus »

then explain to me how

mine is "naruto"
http://www.simsportal.net/common-intere ... me/naruto/

and

his is "Naruto"
http://www.simsportal.net/common-intere ... me/Naruto/


each displays 1 result

but if i change mine to "Naruto" it will display 2 results?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

I have no idea. I'm not in the mood for guess-my-code-and-my-database-from-some-arbitrary-output today.
psychotomus
Forum Contributor
Posts: 487
Joined: Fri Jul 11, 2003 1:59 am

Post by psychotomus »

volka wrote:I have no idea. I'm not in the mood for guess-my-code-and-my-database-from-some-arbitrary-output today.
say what?
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

Post by aaronhall »

No one can guess at what your problem is without seeing some code. It's always good to post code. How bout a little code? Some code, please. Overall: code.
psychotomus
Forum Contributor
Posts: 487
Joined: Fri Jul 11, 2003 1:59 am

Post by psychotomus »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


database:
[syntax="sql"]CREATE TABLE `users` (
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(25) NOT NULL,
  `user_pass` varchar(30) NOT NULL,
  `user_email` varchar(100) NOT NULL,
  `user_verified` varchar(6) NOT NULL,
  `user_rank` int(2) NOT NULL,
  `user_avatar` varchar(200) NOT NULL,
  `user_gender` varchar(6) NOT NULL,
  `user_country` varchar(40) NOT NULL,
  `user_state` varchar(40) NOT NULL,
  `user_zip` varchar(25) NOT NULL,
  `user_sig` varchar(255) NOT NULL,
  `user_aim` varchar(30) NOT NULL,
  `user_yim` varchar(100) NOT NULL,
  `user_msn` varchar(50) NOT NULL,
  `user_website` varchar(255) NOT NULL,
  `user_interest` blob NOT NULL,
  `user_fav_anime` blob NOT NULL,
  `user_fav_videogames` blob NOT NULL,
  `user_about` blob NOT NULL,
  `user_dob` int(11) NOT NULL,
  `user_friends` int(11) NOT NULL default '0',
  `user_high_scores` int(11) NOT NULL default '0',
  `user_poems` int(11) NOT NULL default '0',
  `user_fanfics` int(11) NOT NULL default '0',
  `user_comics` int(11) NOT NULL default '0',
  `user_points` int(10) NOT NULL default '0',
  `user_last_login` int(11) NOT NULL,
  `user_join_date` int(11) NOT NULL,
  `user_anime_downloads` int(5) NOT NULL default '0',
  `user_manga_downloads` int(5) NOT NULL default '0',
  `user_midi_downloads` int(5) NOT NULL default '0',
  `user_mp3_downloads` int(5) NOT NULL default '0',
  `user_software_downloads` int(5) NOT NULL default '0',
  `user_profile_views` int(5) NOT NULL default '0',
  `user_poem_views` int(5) NOT NULL default '0',
  `user_fanfic_views` int(5) NOT NULL default '0',
  `user_comic_views` int(5) NOT NULL default '0',
  `user_visits` int(5) NOT NULL default '0',
  `user_my_profile_views` int(5) NOT NULL default '0',
  `reported` varchar(1) NOT NULL default 'n',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=25 ;

code[/syntax]

Code: Select all

<?

$select_from = 'user_' . htmlspecialchars(str_replace("\'", "'", $_GET['select_from']));
$search_phrase = htmlspecialchars(str_replace("\'", "'", $_GET['search']));

//if start is set
if (!empty($_GET["start"]))
{
	$start = htmlspecialchars(str_replace("\'", "'", $_GET['start']));
}
else
{
	$start = 0;
}

//if order is set
if (!empty($_GET["order"]))
{
	$order = htmlspecialchars(str_replace("\'", "'", $_GET['order']));
}
else
{
	if(!empty($_POST['select2']))
	{
		$order = htmlspecialchars(str_replace("\'", "'", $_POST['select2']));
	}
	else
	{
		$order = "ASC";
	}
}

//if order by is set
if (!empty($_GET["order_by"]))
{
	$order = htmlspecialchars(str_replace("\'", "'", $_GET['order_by']));
}
else
{
	if(!empty($_POST['select']))
	{
		$order_by = htmlspecialchars(str_replace("\'", "'", $_POST['select']));
	}
	else
	{
		$order_by = "id";
	}
}

$result = mysql_query("SELECT user_avatar,user_join_date, username,user_points,user_friends,user_high_scores, user_poems,user_fanfics,user_comics FROM users WHERE $select_from LIKE '%$search_phrase%' LIMIT $start,50") or die(mysql_error());
$result2 = mysql_query("SELECT id FROM users WHERE $select_from LIKE '%$search_phrase%'");
$num_results = mysql_num_rows($result2);
while ($user_info = mysql_fetch_object($result))
{ 

?>
  <tr>
    <td><div align="center"><?= display_date($user_info->user_join_date) ?></div></td>
    <td><div align="center"><a href="<?= $siteURL ?>profile/<?= $user_info->username ?>"><?= $user_info->username ?></a></div></td>
    <td><div align="center"><?= $user_info->user_points ?></div></td>
    <td><div align="center"><?= $user_info->user_friends ?></div></td>
    <td><div align="center"><?= $user_info->user_high_scores ?></div></td>
    <td><div align="center"><?= $user_info->user_poems ?></div></td>
    <td><div align="center"><?= $user_info->user_fanfics ?></div></td>
    <td><div align="center"><?= $user_info->user_comics ?></div></td>
  </tr>
<?
}
?>
</table>
<?
echo '<center>' . pagination("common-interest.php?select_from=$select_from&search_phrase=$search_phrase&order=$order&order_by=$order_by",$num_results, 50, $start) . '</center>';
?>

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

I don't see anything from http://dev.mysql.com/doc/refman/5.0/en/ ... ivity.html in your query or the database definition. Did you actually read the doc?
psychotomus
Forum Contributor
Posts: 487
Joined: Fri Jul 11, 2003 1:59 am

Post by psychotomus »

yes I read the doc, it said SQL is not case sensitive so it should work.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

uh, today's not my day. I got the question wrong and will take the rest of the day off. good luck.
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Post by Mordred »

Code: Select all

$search_phrase = htmlspecialchars(str_replace("\'", "'", $_GET['search']));
This is NOT an adequate protection against ANYTHING. Use mysql_real_escape_string() before putting things in a db query and htmlentities with PROPER parameters before outputting to HTML. As it is, your script is vulnerable to multiple SQL injections, check your PM.
User avatar
neel_basu
Forum Contributor
Posts: 454
Joined: Wed Dec 06, 2006 9:33 am
Location: Picnic Garden, Kolkata, India

Post by neel_basu »

Did You Tried This At the time of table creation

Code: Select all

DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Post by Mordred »

Strange indeed, his collation is latin1_swedish_ci, it is supposed to work :(

Oh wait.
`user_fav_anime` blob NOT NULL,

This forces binary comparison I think, try to use COLLATE around LIKE as in the manual and see if it would work.
That, or make user_fav_anime a varchar, or if you expect to have lots of values, refactor as a one to many relation.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Mordred wrote:Strange indeed, his collation is latin1_swedish_ci, it is supposed to work :(

Oh wait.
`user_fav_anime` blob NOT NULL,

This forces binary comparison I think, try to use COLLATE around LIKE as in the manual and see if it would work.
That, or make user_fav_anime a varchar, or if you expect to have lots of values, refactor as a one to many relation.
...or just make them TEXT fields. ;)
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

Not a mysql person but couldn't you just use something like

Code: Select all

SELECT * from table WHERE UPPER(columnname) like '%value%';
and make sure the value is also in uppercase ?

Only downside is it may be a bit slower than a natural search but in my experience the impact is minimal.
Post Reply