How to use PDO to read data from the database?
Reading data from the database is not so different than inserting data, with steps 1 to 5 being almost identical while the sixth step is different.
1) Write the regular select statement and again, instead of values, put named placeholders. For example:
|
1 |
$sql = "SELECT * FROM users"; |
2) Prepare the query:
|
1 |
$query = $dbh -> prepare($sql); |
3) Execute the query:
|
1 |
$query -> execute(); |
4) Assign the data which you pulled from the database (in the preceding step) to a variable.
|
1 |
$results = $query -> fetchAll(PDO::FETCH_OBJ); |
Here I used the parameter PDO::FETCH_OBJ that returns the fetched data as an object. If you’d like to fetch the data in the form of an array, use: PDO::FETCH_ASSOC. 5) Make sure that you were able to retrieve the data from the database, by counting the number of records.
|
1 |
if($query -> rowCount() > 0){} |
6) In case that the query returned at least one record, we can echo the records within a foreach loop:
|
1 2 3 4 5 6 7 8 9 |
if($query -> rowCount() > 0) { foreach($results as $result) { echo $result -> name . ", "; echo $result -> city . ", "; echo $result -> date_added; } } |
All code together now:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
$sql = "SELECT * FROM users WHERE city = :city"; $query = $dbh -> prepare($sql); $query -> bindParam(':city', $city, PDO::PARAM_STR); $city = "New York"; $query -> execute(); $results = $query -> fetchAll(PDO::FETCH_OBJ); if($query -> rowCount() > 0) { foreach($results as $result) { echo $result -> name . ", "; echo $result -> city . ", "; echo $result -> date_added; } } |
For the localhost Database name is: pdo. SQL file available inside the package.
This tutorial was very useful, thank you.