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 $searchString
Trying 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 errors
But 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.
{ 16 comments… read them below or add one }
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
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.
Thx Hall, was forgotten how to do this. ‘%%%s%%’ was indeed the most obvious solution. Two % signs ends up in one, leaving the sql as intended.
Great.
Great.. big help.. thanks
Great help, thanks
Brilliant thanks. Was really pulling my hair out over this!
Thanks for the trick. You made my day.
How can I execute properly this code:
mysql_select_db($database_a, $a);
$query_searchCri = sprintf(“SELECT cv_name, cv_skill,cv_status FROM cv WHERE cv_skill = %s and cv_status=%s ORDER BY cv_name ASC “, GetSQLValueString($colname_searchCri, “text”));
$query_limit_searchCri = sprintf(“%s LIMIT %d, %d”, $query_searchCri, $startRow_searchCri, $maxRows_searchCri);
It always return : Warning: sprintf() [function.sprintf]: Too few arguments in …
and You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘LIMIT 0, 10′ at line 1.
Thank you.