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'%'");
12 years, 11 months ago. it's for php to know how to handle the parameters, %d – the argument is treated as an integer, and presented as a (signed) decimal number. %s – the argument is treated as and presented as a string. in your examples, $slug is a string and $this->id is an integer.
STRCMP() function in MySQL is used to compare two strings. If both of the strings are same then it returns 0, if the first argument is smaller than the second according to the defined order it returns -1 and it returns 1 when the second one is smaller the first one.
The MySQL LIKE OperatorThe LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards often used in conjunction with the LIKE operator: The percent sign (%) represents zero, one, or multiple characters. The underscore sign (_) represents one, single character.
Multiple statements or multi queries must be executed with mysqli::multi_query(). The individual statements of the statement string are separated by semicolon. Then, all result sets returned by the executed statements must be fetched.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With