MySQL Like Statement and case sensitivity

andrew has asked for the wisdom of the Perl Monks concerning the following question:

Ok i have a dtabase full of items, and im making a search script, $search contains what the user is searching for and I need to search columns description OR longdescription, and I need it to search them case-insensitive. The one I tried just doesnt work good at all.

    $sth = $dbh->prepare("SELECT * FROM `items` WHERE description AND 
+longdescription LIKE '$search'");
     $sth->execute or die $dbh->errstr;
[download]

Comment on MySQL Like Statement and case sensitivity Download Code

Replies are listed 'Best First'.
Re: MySQL Like Statement and case sensitivity by Zaxo (Archbishop) on Sep 06, 2002 at 21:12 UTC
You're testing for `description` being logically true and `longdescription` matching whatever $search looks like. To get a case-insensitive match, you could transform everything to one case with UPPER(), say. Use placeholders. $sth = $dbh->prepare( "SELECT * FROM `items` WHERE UPPER(description) LIKE UPPER(?) OR UPPER(longdescription) LIKE UPPER(?)"); $sth->execute($search,$search) or die $dbh->errstr; [download] You should consider listing the columns you want, instead of using the wildcard. That way you don't fetch unneeded data, and can control the order of results. After Compline, Zaxo	[reply] [d/l]
Re: Re: MySQL Like Statement and case sensitivity by andrew (Acolyte) on Sep 06, 2002 at 21:51 UTC
oops I ment case sensitive and if descrtion conatins "blalala ahaha aa" and the user puts "ah" in search that should come up!	[reply]
Re: MySQL Like Statement and case sensitivity by gryphon (Abbot) on Sep 06, 2002 at 23:46 UTC
Greetings andrew, I think what you're wanting is to use LIKE's wild-card character. `$sth = $dbh->prepare(q{ SELECT * FROM items WHERE description LIKE ? OR longdescription LIKE ? }); $sth->execute('%' . $search . '%', '%' . $search . '%') or die $dbh->e +rrstr;` [download] Notes: Placeholders are your friend. (a.k.a. bind values) Always use them. gryphon code('Perl') \|\| die;	[reply] [d/l]
Re: MySQL Like Statement and case sensitivity by greywolf (Priest) on Sep 07, 2002 at 02:31 UTC
Here is another way to use placeholders. This can be more readable when you have lots of them. I would like to second the motion that 'SELECT ' is a bad thing. Always specify what you want to retrieve. `$query = "SELECT FROM items WHERE description LIKE ? OR longdescri +ption LIKE ?"; $sth = $dbh->prepare($query); $sth->bind_param(1, $search); $sth->bind_param(2, $search); unless ($sth->execute()) { die $dbh->errstr() }` [download] mr greywolf	[reply] [d/l]