Page 1 of 2
how to make LIKE %word% not case sensitive
Posted: Tue Mar 13, 2007 3:17 am
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());
Posted: Tue Mar 13, 2007 3:27 am
by volka
Posted: Tue Mar 13, 2007 3:42 am
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?
Posted: Tue Mar 13, 2007 4:11 am
by volka
I have no idea. I'm not in the mood for guess-my-code-and-my-database-from-some-arbitrary-output today.
Posted: Tue Mar 13, 2007 4:13 am
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?
Posted: Tue Mar 13, 2007 4:17 am
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.
Posted: Tue Mar 13, 2007 4:24 am
by psychotomus
feyd | Please use 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
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]
Posted: Tue Mar 13, 2007 4:27 am
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?
Posted: Tue Mar 13, 2007 4:29 am
by psychotomus
yes I read the doc, it said SQL is not case sensitive so it should work.
Posted: Tue Mar 13, 2007 4:32 am
by volka
uh, today's not my day. I got the question wrong and will take the rest of the day off. good luck.
Posted: Tue Mar 13, 2007 6:17 am
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.
Posted: Tue Mar 13, 2007 6:48 am
by neel_basu
Did You Tried This At the time of table creation
Code: Select all
DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
Posted: Tue Mar 13, 2007 7:43 am
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.
Posted: Tue Mar 13, 2007 8:18 am
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.

Posted: Tue Mar 13, 2007 8:30 am
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.