php sprintf + sql like

by Josh Highland on July 6, 2008

grey php sprintf + sql like

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.


{ 17 comments… read them below or add one }

1 rq July 11, 2008 at 6:05 am

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.

2 Brian July 28, 2008 at 4:48 am

Thanks Josh, I appreciate this tutorial. It worked out great once I had my string properly formated. Great Job!
Brian http://www.classifiedart.com

3 J October 21, 2008 at 7:22 am

Nice simple solution! Thanks, I was scratching my head on this one.

4 Josh Highland October 21, 2008 at 7:55 pm

hooray! someone found it useful! the purpose of this blog post has been fulfilled

5 Jimmyjames November 10, 2008 at 1:08 pm

Nice solution! Thanks for posting.

Using ‘%s%%’ the final string came out as ‘s’% instead of ‘s%’

6 Slavi November 26, 2008 at 8:46 am

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

7 Dave December 21, 2008 at 1:55 pm

Helped me out :-)

Ended up using it like

…OR `CaseType` LIKE ‘%s’)”, mysql_real_escape_string(“%”.”%”.$extra_search.”%”.”%”));

8 Hall January 22, 2009 at 7:58 am

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 !)

9 SQL Tutorials April 30, 2009 at 6:09 pm

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.

10 Stalski July 13, 2009 at 10:27 am

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.

11 Marcelo September 26, 2009 at 3:38 pm

Great.

12 Jheric January 18, 2010 at 3:54 am

Great.. big help.. thanks

13 xberni July 23, 2010 at 1:56 am

Great help, thanks

14 ChoonyFish August 24, 2010 at 11:20 am

Brilliant thanks. Was really pulling my hair out over this!

15 Piotr September 24, 2010 at 2:18 am

Thanks for the trick. You made my day.

16 faye March 31, 2011 at 9:08 pm

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.

17 Marcos July 18, 2012 at 12:02 pm

Or too:

sprintf(“Select …… like %s”, “%” . valueString . “%”);

Thanks you webmaster!

Leave a Comment

Previous post:

Next post: