-
php sprintf + sql like
Posted on July 6th, 2008 9 comments
Sometimes I do my best programming when I’m tired. Don’t ask me why, I just do. It’s a skill I picked up in college.
Being tired and producing good code wasn’t the case last night. I was trying to use the sprintf PHP function with a SQL “Like” statement. I made some dumb mistakes that tripped me up for a while. Hopefully someone out there will find this post and help them not make the problems I made, sleepy or not!
Normally the LIKE is used in mysql like this:
SELECT name FROM users WHERE name LIKE 'J%';That would get all names including: Josh, Jason, Jimi, etc.
In a SQL Like statement the % is a wild card, so the command is to match everything starting with “J”Now when you use the sprintf() function it looks kind like this:
$query = sprintf("SELECT name FROM users WHERE name='%s'", $searchString);
The %s will be replaced with the value of $searchStringTrying to combined them is where I had some problems….
At first I tried to do something like this:
$query = sprintf("SELECT name FROM users WHERE name LIKE'%s'", $searchString);didnt return what I was looking for at all, it had no wild cards in it!
Then I tried this:
$query = sprintf("SELECT name FROM users WHERE name LIKE'%s%'", $searchString);
didnt work either, this time it threw errorsBut this worked great
$query =sprintf("SELECT name FROM users WHERE name LIKE '%s'", $searchString . "%");So the moral of the story is, if you want to use a SQL Like statement, appent the wildcard for the Like statement to the string to be inserted by the sprintf funtion.
9 responses to “php sprintf + sql like”
-
Or: sprintf(”… LIKE ‘%s%%’”, $searchString);
Inside the *printf format string the double percent sign means to replace by a single percent sign without consuming an argument to the *printf call.
I notice both “Ads by Google” below are about protecting your site from SQL Injection attacks.
-
Thanks Josh, I appreciate this tutorial. It worked out great once I had my string properly formated. Great Job!
Brian http://www.classifiedart.com -
Nice simple solution! Thanks, I was scratching my head on this one.
-
hooray! someone found it useful! the purpose of this blog post has been fulfilled
-
Jimmyjames November 10th, 2008 at 13:08
Nice solution! Thanks for posting.
Using ‘%s%%’ the final string came out as ’s’% instead of ’s%’
-
Hi Josh,
I would modify the query to use mysql_real_escape_string:
$query = sprintf(”SELECT name FROM users WHERE name LIKE ‘%s’”, mysql_real_escape_string($searchString) . “%”);
Slavi
-
Helped me out
Ended up using it like
…OR `CaseType` LIKE ‘%s’)”, mysql_real_escape_string(”%”.”%”.$extra_search.”%”.”%”));
-
Thanks I had the same problem:
old code:
$sql = “SELECT wmenuid, wsubmenus FROM “.$prefix.”_woordenboekpaginas WHERE themasite=’$template’ AND wlevel=’$lid’ AND wsubmenus LIKE ‘%$pagina%’”;Solution 1:
$sql = sprintf(”SELECT wmenuid, wsubmenus FROM “.$prefix.”_woordenboekpaginas WHERE themasite=’%s’ AND wlevel=%d AND wsubmenus LIKE ‘%%%s%%’”, mysql_real_escape_string($template), mysql_real_escape_string($lid), mysql_real_escape_string($pagina));Or solution 2:
$sql = sprintf(”SELECT wmenuid, wsubmenus FROM “.$prefix.”_woordenboekpaginas WHERE themasite=’%s’ AND wlevel=%d AND wsubmenus LIKE ‘%s’”, mysql_real_escape_string($template), mysql_real_escape_string($lid), mysql_real_escape_string(”%”.”%”.$pagina.”%”.”%”));Thanks you all ! (and google for giving this paga almost on top of the search results !)
-
You know, the thing about SQL is, that there is virtually nothing that can replace it.
Does anyone know if a substitute exists for sql? I mean besides MS SQL and Oracle and all that jazz. Thanks.
Leave a reply
notPopular.com Podcast
My PodcastProfiles
