Reading databases is easy, you must have at least basic notions of SQL to start extracting the information stored in your tables.
The execution of queries with PDO always follows the same procedure, be it reading, updating, deleting or creating, where it will only differ in the returned result (0 or more rows). In this particular tutorial, I’m going to focus on reading the rows stored in your tables. I am also going to use the PDO class for its versatility and security options.
Content
- First thing, database connection
- Prepare queries to read data
- Query Execution
- View data of rows returned by PDO
First thing, database connection
To start we always have to have connected to the database. We will do this by creating a class object PDO. In this example I am going to connect to MySQL installed locallywith a database called proof with the user root and without password.
$objetoPDO = new PDO('mysql:host=localhost;dbname=prueba','root', '');
In $objectPDO I have a PDO class object. With this object we can prepare the queries as we will see below and forget about closing the connection.
Prepare queries to read data
Once the PDO class object has been created, we can start launching (executing) the queries we want, as many as are necessary in our code. For example, to read from a user table with the columns name, e-mail and that in the column surnames appears “perez” we will:
Now, if the query is correct, we have an object of the PDOStatement class in our variable $objectoPDOStatement, in the case of a possible syntax error in the query (for example, having written SELECT or WHERE wrong) the value of the variable will be false.
$query = "SELECT nombre, email FROM usuario WHERE apellidos LIKE '%perez%'";
$objetoPDOStatement = $objetoPDO->prepare($query);
if($objetoPDOStatement ¡= false){
//la consulta es buena
}else{
Echo ‘Error, la consulta no es válida’.
}
Query Execution
We have the query prepared, this means that it has been validated for possible SQL syntax errors, but to obtain the result of the query we will have to execute it:
$exito = $objetoPDOStatement->execute();
The variable $success contains a boolean that indicates if the query has gone well or badly, in this case the possible error would not be from SQL syntax but from having written a wrong table column or a non-existent table, for example. In the event that $success is true, the PDOStatement object in the $objectoPDOStatement variable will contain the result rows of the query. The following code would get the array with the rows:
When executing the method fetchAll() of PDOStatement and assign its return value in $arrayFilas, this is an array with the rows produced by the result of executing the previous SELECT query on our MySQL database.
if($exito == true){
$arrayFilas = $objetoPDOStatement->fetchAll();
}else{
Echo “La consulta tiene errores”;
}
View data of rows returned by PDO
We have almost seen the whole process, in the case of the query select previous I know that it is very probable that it returns rows, therefore I am going to go through the array contained in $arrayRows and display its content.
As we have said before, each result row of our MySQL database obtained in php with the method fetchAll() is nothing more than an array where each key is the name of the column, within $arrayRows I have a matrix. The following example code will make it clearer:
echo 'Usuarios totales resultado: '. sizeof($arrayFilas);
foreach($arrayFilas as $fila){
echo 'Usuario con nombre: ' . $fila['nombre'] . ' email: ' . $fila['email'];
}