CODEWITHSUNDEEP
phpmysql
Steven
Steven

Reputation: 19425

PDO query returns 0 when it should return rows

I'm using PHP PDO to run queries. I have the following function:

protected function isStoreRegistered($name, $street1 ,$city, $country_id, $id) {

if($id == '0') {
  $sql = " SELECT a.name, b.street1, b.city, c.id
           FROM tablea a
           LEFT JOIN tableb b ON a.fk_addressID = b.id
           LEFT JOIN tablec c ON b.fk_countryID = c.id
           WHERE a.name = '$name'
           AND b.street1 = '$street1'
           AND b.city = '$city'
           AND b.fk_countryID = '$country_id'";
  $result = $this->db->exec($sql);
} else {
  // some other query
}

return $result;

}

The query sent to MySQL looks like this:

 SELECT a.name, b.street1, b.city, c.id
 FROM sl_store a
 LEFT JOIN sl_address b ON a.fk_addressID = b.id
 LEFT JOIN sl_country c ON b.fk_countryID = c.id
 WHERE a.name = 'test store'
 AND b.street1 = 'Weselsgate 2'
 AND b.city = 'Oslo'
 AND b.fk_countryID = 'NO'

Runnign this query in Toad, returns 1 row.

But if I do a print_r(result), it outputs 0 (zero)

How can I find out what is wrong here?

Upvotes: 0

Views: 1425

Answers (3)

Kalessin
Kalessin

Reputation: 2292

As mentioned previously and in the manual, PDO::exec() does not return results from a SELECT statement. Instead you should try the following (note the parameter binding):

protected function isStoreRegistered($name, $street1 ,$city, $country_id, $id) {

if ($id == '0') {
  $sql = " SELECT a.name, b.street1, b.city, c.id
           FROM tablea a
           LEFT JOIN tableb b ON a.fk_addressID = b.id
           LEFT JOIN tablec c ON b.fk_countryID = c.id
           WHERE a.name = :name
           AND b.street1 = :street
           AND b.city = :city
           AND b.fk_countryID = :country";
  $stmt = $this->db->prepare( $sql );
  $vars = array(
    'name'      => $name,
    'street'    => $street1,
    'city'      => $city,
    'country'   => $country_id,
  );
  $result = $stmt->execute( $vars );
} else {
  // some other query
}

return $result;

Upvotes: 1

ajreal
ajreal

Reputation: 47311

exec is returning the number of rows affected by the statement,
in your case, is zero because you are doing a select

docs:- http://php.net/manual/en/pdo.exec.php

your sql is not using PDO in proper manner,
is vulnerable for SQL injection,
spent some reading on the parameters binding

Upvotes: 0

prodigitalson
prodigitalson

Reputation: 60413

From the docs:

PDO::exec() does not return results from a SELECT statement.

And since youre not modifying anything (no insert, delete, or update) there are no rows affected, which is what PDO::exec does return.

If you want the data use a prepared statement or PDO::query. If you want the number of rows the use a count in your select.

Upvotes: 2

Related Questions