PDO get data from database
Your code is missing a call to execute()
after prepare()
. After your prepared statement is executed you can get the array of records using fetchAll()
.
$getUsers = $DBH->prepare('SELECT * FROM users ORDER BY id ASC');
$getUsers->execute();
$users = $getUsers->fetchAll();
// Then in your presentation logic you can use the following code to display the data:
if ($users) {
foreach ($users as $user) {
echo $user['username']."<br/>";
}
} else {
// Whatever your requirement is to handle the no user case, do it here.
echo 'Error: No users.';
}
However, in you example you are not passing any variable data to the query and you only execute it once, which defeats a purpose of having prepared statement. You should use prepared statements if you need to have 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 then you can simply use query()
method.
$users = $DBH->query('SELECT * FROM users ORDER BY id ASC')->fetchAll();
If you do not need to fetch all records at once, you can simply loop on 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/>";
}
The PDO
method fetchAll()
returns an array/result-set, which you need to assign to a variable and then use/iterate through that variable:
$users = $getUsers->fetchAll();
foreach ($users as $user) {
echo $user['username'] . '<br />';
}
UPDATE (missing execute()
)
Also, it appears you aren't calling the execute()
method which needs to happen after you prepare the statement but before you actually fetch the data:
$getUsers = $DBH->prepare("SELECT * FROM users ORDER BY id ASC");
$getUsers->execute();
$users = $getUsers->fetchAll();
...
This code below will do what you are asking for:
$sql = $dbh->prepare("SELECT * FROM users ORDER BY id ASC");
$sql->execute();
while ($result = $sql->fetch(PDO::FETCH_ASSOC)) {
echo $result['username']."<br/>";
}