CODEWITHSUNDEEP
phpmysql
John Doe
John Doe

Reputation: 215

SQL query works in phpmyAdmin, not in php

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.

Upvotes: 0

Views: 78

Answers (1)

trincot
trincot

Reputation: 350147

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

Upvotes: 1

Related Questions