why does mysqli and PDO give me different results

Question

here is the snippet of the code i'm working with -

    //mysqli
    $db_conn = mysqli_connect('localhost', 'root', 'XXXX', 'core');        
    $db = mysqli_select_db($db_conn, 'core');
    $result = mysqli_query($db_conn, 'SELECT * FROM customers WHERE email = "juz_acool1@gmail.com"');
    $result_rows= mysqli_fetch_assoc($result);
    var_dump($result_rows);

    //pdo
    $dsn = "mysql:host=localhost;dbname=core;";
    $u = 'root';
    $p = 'XXXX';
    $pdo = new PDO($dsn, $u, $p);
    var_dump($pdo);
    $test = $pdo->exec('show databases;');
    var_dump($test);
    var_dump($pdo);

    $query = $pdo->prepare("SELECT * FROM customers WHERE email = 'juz_acool1@gmail.com'");
    var_dump($query);        
    $r = $query->execute();
    var_dump($r);

and here is what i get as the result -

  //mysqli 
  array (size=10)
  'id' => string '1' (length=1)
  'fullname' => string 'Shalom Sam' (length=10)
  'username' => string 'shalom.sam' (length=10)
  'email' => string 'juz_acool1@gmail.com' (length=19)
  'birthdate' => string '05/25/1986' (length=10)
  'gender' => string 'm' (length=1)
  'location' => string 'Mumbai, Maharashtra, India' (length=26)
  'verified' => string '1' (length=1)
  'timezone' => string '5.5' (length=3)
  'lastupdatetime' => string '2014-05-02 15:42:35' (length=19)

//pdo
int 0

object(PDO)[4]

object(PDOStatement)[5]

  public 'queryString' => string 'SELECT * FROM customers WHERE email = 'juz.cool1@gmail.com'' (length=59)

boolean false

I know all this is a lot to read. But I really am looking to figure where i may be going wrong. Hope someone can help me out here.

Because your PDO query is failing, that's why it's returning `false` when you `var_dump($r)` — random_user_name, May 02 '14 at 13:50
Read [How to squeeze an error message out of PDO](http://stackoverflow.com/questions/3726505/how-to-squeeze-error-message-out-of-pdo) and setup your PDO object to always throw exceptions with `PDO::ERRMODE_EXCEPTION`. You'll see at what point it fails. — Michael Berkowski, May 02 '14 at 13:51
By the way, passing the e-mail in straight to your PDO query defeats the purpose of using PDO. You should bind parameters so it has the opportunity to sanitize... — random_user_name, May 02 '14 at 13:51
(beyond that, you need to do `$results = $query->fetch(PDO::FETCH_ASSOC)` to actually get a row from PDO, comparably to your `mysqli_fetch_assoc()` call) — Michael Berkowski, May 02 '14 at 13:52
@cale_b i know that i'm suppose to bind the params ..i did this here only for the purpose of testing. — Shalom Sam, May 02 '14 at 13:57
@cale_b, there's nothing wrong with putting a literal constant value into an SQL expression. It's when you put untrusted PHP variables into an SQL expression that you should use query parameters. — Bill Karwin, May 02 '14 at 13:59
@MichaelBerkowski yes that would be how i proceeded if i didn't get false on execute() — Shalom Sam, May 02 '14 at 13:59
@ShalomSam right, but first you need to enable exceptions as I suggested in my first comment so you can find out why the execute fails. — Michael Berkowski, May 02 '14 at 14:00
If you don't like exceptions, then check the return value of `execute()` and if that is **false** then dump `$query->errorInfo()`. — Bill Karwin, May 02 '14 at 14:04
@MichaelBerkowski 's method mentioned in the second comment gave me this - `Warning: PDOStatement::execute(): SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute. in` [edit] but when i do u se fetchAll() as suggested then it just returns false and all errors disappear. [edit] — Shalom Sam, May 02 '14 at 14:27
@ShalomSam [This question addresses your problem](http://stackoverflow.com/questions/17434102/causes-of-mysql-error-2014-cannot-execute-queries-while-other-unbuffered-queries) You have to fetch all rows from the `SHOW DATABASES` query before MysQL will successfully execute the next statement (MySQLi would behave the same way, but you didn't do the `show databases` query in that block) — Michael Berkowski, May 02 '14 at 14:57
@MichaelBerkowski your ans. and the answer below together helped me solve the problem. So thanks a lot. — Shalom Sam, May 03 '14 at 05:48

score 1 · Answer 1 · answered May 02 '14 at 14:55

1

use fetch on your PDO result

$r = $query->execute();
$res = $r->fetch(PDO::FECTH_ASSOC);
var_dump($res);

here, read that, it should answer all those types of questions

http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers

answered May 02 '14 at 14:55

Félix Adriyel Gagnon-Grenier

8,422
10
52
65

score -1 · Answer 2 · answered May 02 '14 at 13:53

It looks like you are missing a step, you need to define how PDO fetches your data before attempting to display it.

Try this

$host = 'localhost';
  $dbname = 'database name';
  $user = 'user name';
  $pass = 'password';

$DB = new pdo("mysql:host=$host;dbname=$dbname", $user, $pass);

$STH = $DB->query('SELECT * FROM some_table');

# setting the fetch mode  
$STH->setFetchMode(PDO::FETCH_ASSOC); 

# fetching
while($row = $STH->fetch()) { 
    print('assoc'); 
    echo $row['ID'] . "\n";  
    echo $row['Book'] . "\n";  
    echo $row['colour'] . "\n";  
    print('<p>');
}

why does mysqli and PDO give me different results

2 Answers2