SQL query works in phpmyAdmin, not in php

Question

This query works if I run it in phpMyAdmin (returns the rows):

SELECT breweries.name AS brew_name,
       employees.f_name,
       employees.l_name,
       employees.age,
       employees.position,
       employees.pay_hr,
       beers.name AS fav_beer
  FROM employees
  INNER JOIN beers ON employees.beer_id = beers.id
  INNER JOIN breweries ON employees.brewery_id = breweries.id
  INNER JOIN locations ON breweries.location_id = locations.id
  WHERE locations.city = "San Diego"
    AND locations.state = "CA";

However, when I get the user input via a form and use variable in city/state place, like so, I get no result:

$loc_str = $_GET["e_location"]; // Input is "San Diego, CA"
$loc_pieces = explode(",", $loc_str);
$city = $loc_pieces[0];
$state = $loc_pieces[1];

echo <<<res
  Showing all employees working at breweries in $city, $state.
res; //Correctly echos San Diego, CA

$query = <<<stmt
   SELECT breweries.name AS brew_name, employees.f_name, employees.l_name,
          employees.age, employees.position, employees.pay_hr,
          beers.name AS fav_beer
     FROM employees INNER JOIN beers ON employees.beer_id = beers.id
     INNER JOIN breweries ON employees.brewery_id = breweries.id
     INNER JOIN locations ON breweries.location_id = locations.id
     WHERE locations.city = "$city"
     AND locations.state = "$state";
stmt;
$stmt = $mysql->prepare($query);
$stmt->execute();
$stmt->bind_result($b_name, $f_name, $l_name, $age, $pos, $pay, $fav_beer);
while ( $stmt->fetch() ) {

  echo <<<res
    <tr>
      <td>$b_name</td>
      <td>$f_name, $l_name</td>
      <td>$age</td>
      <td>$pos</td>
      <td>$pay</td>
      <td>$fav_beer</td>
    </tr>

  res;
} // NO HTML OUTPUT

What am I doing wrong? Thanks in advance.

You use `->prepare()` but place user data directly in your query? Why? [How can I prevent SQL-injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Sean, Nov 26 '15 at 21:04
Have you looked at [`$mysqli->error`](http://php.net/manual/en/mysqli.error.php) to see why your query failed? — Sean, Nov 26 '15 at 21:07
I hope that closing "res" is at the exact beginning of the line in your real code. Turn error output on in PHP and add checks for MySQL errors and the problem will almost certainly become obvious. And as Sean notes, watch out for injection (your code will die if you look for O'Fallon, IL). Oh, and if your input is "San Diego, CA", you're probably searching where state is " CA", with a leading space. — Matt Gibson, Nov 26 '15 at 21:44

score 1 · Accepted Answer · answered Nov 26 '15 at 21:50

You still have a space in your state after the eplode. Use trim on the resulting exploded parts before doing the query:

$city = trim($loc_pieces[0]);
$state = trim($loc_pieces[1]);

and then:

$query = <<<stmt
   SELECT breweries.name AS brew_name, employees.f_name, employees.l_name,
          employees.age, employees.position, employees.pay_hr,
          beers.name AS fav_beer
     FROM employees INNER JOIN beers ON employees.beer_id = beers.id
     INNER JOIN breweries ON employees.brewery_id = breweries.id
     INNER JOIN locations ON breweries.location_id = locations.id
     WHERE locations.city = ?
     AND locations.state = ? ;
stmt;
$stmt = $mysql->prepare($query);
$stmt->bind_param('ss', $city, $state);
$stmt->execute();

SQL query works in phpmyAdmin, not in php

1 Answers1