12

I am trying to find the number of rows that match a specific pattern. In this example, all that START with "123":

This is working:

$query = mysql_query("SELECT * FROM table WHERE the_number LIKE '123%'");
$count = mysql_num_rows($query);

The problem is the LIKE will vary, so I'm trying to define it in the script, then execute the query, but this is NOT working:

$prefix = "123";
$query = mysql_query("SELECT * FROM table WHERE the_number LIKE $prefix.'%'");
$count = mysql_num_rows($query);

How can I get this query to work properly in the second example?

EDIT: I've also tried it without the period (also not working):

$query = mysql_query("SELECT * FROM table WHERE the_number LIKE $prefix'%'");
MultiDev
  • 10,389
  • 24
  • 81
  • 148
  • John, something that I have found helps me when using variables in strings, is to use the curly braces. `$var1 = "foo"; $var2 = "{$var1} bar"; echo $bar2;` Will output: foo bar and you don't have to worry about concatenation, etc etc. Doesn't work as you may think with single quotes for strings, however. – Blake Apr 13 '12 at 00:10

2 Answers2

21

You have the syntax wrong; there is no need to place a period inside a double-quoted string. Instead, it should be more like

$query = mysql_query("SELECT * FROM table WHERE the_number LIKE '$prefix%'");

You can confirm this by printing out the string to see that it turns out identical to the first case.

Of course it's not a good idea to simply inject variables into the query string like this because of the danger of SQL injection. At the very least you should manually escape the contents of the variable with mysql_real_escape_string, which would make it look perhaps like this:

$sql = sprintf("SELECT * FROM table WHERE the_number LIKE '%s%%'",
               mysql_real_escape_string($prefix));
$query = mysql_query($sql);

Note that inside the first argument of sprintf the percent sign needs to be doubled to end up appearing once in the result.

Jon
  • 428,835
  • 81
  • 738
  • 806
  • 2
    There is a danger of injection if you are using any user-inputted data. There is zero danger for: `$var = "rawr"; ... query("SELECT * FROM table WHERE col='{$var}'";` You are controlling your own data on-server. – Blake Apr 13 '12 at 00:06
  • @Blake: Obviously, but I feel obliged to say it anyway. Code has a way of being adapted to contexts other than the one it was meant for. – Jon Apr 13 '12 at 00:14
  • @Jon, Surely. However, you have to specify that queries can be vulnerable to injections... with user-inputted data. There's a difference between saying "Look at that house" and "Look at that house on fire." :) – Blake Apr 13 '12 at 01:38
  • This is incomplete; `LIKE` needs additional escaping. See [this answer](http://stackoverflow.com/a/3683868/1553851) for details. – shmosel Mar 27 '17 at 20:24
4

DO it like

$query = mysql_query("SELECT * FROM table WHERE the_number LIKE '$yourPHPVAR%'");

Do not forget the % at the end

Starx
  • 77,474
  • 47
  • 185
  • 261