How can I convert this mysqli to PDO?

Tags: , , ,



<?php

    require_once('dbconfig.php');
    global $con;

    $query = $con->prepare("SELECT * FROM userinfo order by id DESC");
    $query->execute();
    mysqli_stmt_bind_result($query, $id, $name, $username, $password);

Answer

You should use ->bindColumn Manual

See also This answer.

  • Best Practise: Do not use SELECT * instead define each column you need to grab from the table.
  • Do not globalise your connection variable. This is a security risk as well as adding bloat and should be unneeded on your code.
  • Because it is a static statement you can use ->query rather than prepare, as nothing needs to be prepared.

Solution:

$query = $con->query("SELECT id,name,username,password FROM userinfo ORDER BY id DESC");
try {
   $query->execute();
   $query->bindColumn(1, $id);
   $query->bindColumn(2, $name); 
   $query->bindColumn(3, $username); 
   $query->bindColumn(4, $password); 
}
catch (PDOException $ex) {
   error_log(print_r($ex,true);
}

Alternatively:

A nice feature of PDO::query() is that it enables you to iterate over the rowset returned by a successfully executed SELECT statement. From the manual

foreach ($conn->query('SELECT id,name,username,password FROM userinfo ORDER BY id DESC') as $row) {
    print $row['id'] . " is the IDn";
    print $row['name'] . " is the Namen";
    print $row['username'] . " is the Usernamen";
}

See Also:

Mzea Has some good hints on their answer, you should use their $options settings as well as using their suggested utf8mb4 connection character set.

And their suggestion for using ->fetchAll is also completely valid too.



Source: stackoverflow