Query returns different (no) results when using a variable

Question

I have two select queries returning different results as follows:

This query returns 29 rows:

SELECT SQL_CALC_FOUND_ROWS `last`,`first`,`mate`,`address`
  FROM `homeownersnew` 
 WHERE `last` LIKE "s%" AND 
       `address` != "" 
 LIMIT ' . (($pagination->get_page() - 1) * $records_per_page) . ', ' . $records_per_page . '';

This query uses a variable to attempt to accomplish the same results but returns 0 rows:

@$last = "s%";

SELECT SQL_CALC_FOUND_ROWS `last`,`first`,`mate`,`address`
  FROM `homeownersnew` 
 WHERE `last` LIKE "$last" AND 
       `address` != "" 
 LIMIT ' . (($pagination->get_page() - 1) * $records_per_page) . ', ' . $records_per_page . '';

I'm sure I am overlooking something but am unable to find the problem.

@Darren be careful when changing the content of the question when editing it, as it may give a different meaning to the question itself or potential answers. — Prix, Sep 12 '14 at 00:22
Make sure to include the *actual* and *syntactically valid* code used. — user2864740, Sep 12 '14 at 00:27
@Prix Apologies, thanks for picking that one up! Too early in the morning here :( — Darren, Sep 12 '14 at 00:31

score 0 · Answer 1 · edited May 23 '17 at 12:05

'single quoted strings are not interpolated'

The most important feature of double-quoted strings [that is not found in single-quote strings] is the fact that variable names will be expanded. See string parsing for details.

Consider this trivial example which shows the issue, mainly that the query literally used LIKE "$last":

$hello = "world";
echo "Hello $hello!"; // => Hello world!
echo 'Hello $hello!'; // => Hello $hello!

The correct solution in this case is to use parameterized queries. It solves the string interpolation issue, cleans up the code, and prevents SQL injection or unexpected data from corrupting queries.

Query returns different (no) results when using a variable

1 Answers1