Geek Answers Handbook

PDO get data from database

I started using PDO recently, before I used only MySQL. Now I am trying to get all the data from the database.

$getUsers = $DBH->prepare("SELECT * FROM users ORDER BY id ASC"); $getUsers->fetchAll(); if(count($getUsers) > 0){ while($user = $getUsers->fetch()){ echo $user['username']."<br/>"; } }else{ error('No users.'); }

But it does not show any users, just a blank page.

+7
source share
3 answers

The PDO method fetchAll() returns an array / result set that you need to assign to a variable, and then use / iterate through this variable:

 $users = $getUsers->fetchAll(); foreach ($users as $user) { echo $user['username'] . '<br />'; }

UPDATE (missing execute() )
In addition, it looks like you are not calling the execute() method, which should happen after preparing the statement, but before actually receiving the data:

 $getUsers = $DBH->prepare("SELECT * FROM users ORDER BY id ASC"); $getUsers->execute(); $users = $getUsers->fetchAll(); ...
+19
source

This code below will do what you ask for:

 $sql = $dbh->prepare("SELECT * FROM users ORDER BY id ASC"); $sql->execute(); while ($result = $sql->fetch(PDO::FETCH_ASSOC)) { echo $user['username']."<br/>"; }
+5
source

In your code there is no call to execute() after prepare() . After executing the prepared statement, you can get an array of records using fetchAll() .

 $getUsers = $DBH->prepare('SELECT * FROM users ORDER BY id ASC'); $getUsers->execute(); $users = $getUsers->fetchAll(); if ($users) { foreach ($users as $user) { echo $user['username']."<br/>"; } } else { trigger_error('No users.'); }

However, in your example, you do not pass variable data to the request, but execute it only once, which contradicts the purpose of preparing the statement. You should use prepared statements if you need variables in your SQL, for example:

 $getUsers = $DBH->prepare('SELECT * FROM users WHERE id=?'); $getUsers->execute([ $_GET['user_id'] ]); $user = $getUsers->fetch(); // get single user by unique id

If there are no variables, you can simply use the query() method.

 $users = $DBH->query('SELECT * FROM users ORDER BY id ASC')->fetchAll();

If you don’t need to retrieve all the records at once, you can simply loop the statement with foreach :

 $getUsers = $DBH->prepare('SELECT * FROM users WHERE username LIKE ? ORDER BY id ASC'); $getUsers->execute([ '%' . $_GET['search'] . '%' // search for username with wildcards ]); foreach ($getUsers as $user) { echo $user['username']."<br/>"; }
0
source

More articles:


All Articles