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? — Sean
– Sean, Commented Nov 26, 2015 at 21:04
Have you looked at $mysqli->error to see why your query failed? — Sean
– Sean, Commented Nov 26, 2015 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
– Matt Gibson, Commented Nov 26, 2015 at 21:44

trincot · Accepted Answer · 2015-11-26 21:50:21Z

1

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();

answered Nov 26, 2015 at 21:50

trincot

357k38 gold badges282 silver badges338 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL query works in phpmyAdmin, not in php

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related