We have already seen how simple it is connect to a MySQL database using PHP. Now we are going to take advantage of this connection to make a simple query to a database.
The first thing our program will do is connect to MySQL. For this we use the mysqli object of the language PHP.
@ $db = new mysqli(localhost, "root", "", "library");
Once the connection is made we will verify that it has been done correctly. To do this we check the value of connect_error to find out if an error has occurred:
if ($db->connect_error)
die('Connection Error ('.$db->connect_errno.')'.$db->connect_error);
The next thing will be to prepare the query on the database. In this case we are going to have a table that contains books with the following structure:
| Books |
| ISBN |
| Title |
| Author |
| Editorial |
| Publication Date |
The query that we will execute will be the following:
$query = "SELECT * FROM books";
To execute the statement we use the query(), which receives the query that we have defined as a parameter.
$result = $db->query($query);
In the $result variable we have the result of elements as a response to the executed query. That is why we will have to go through it to show all the content.
To go through the result, the first thing we have to do is know how many records it has returned. To do this we use the property num_rows.
$numrows = $result->num_rows;
echo "The number of elements is ".$numrows;
Now we establish a for loop with the number of elements that the result has, in which, for each row we execute the method fetch_object to obtain an object that represents the database tuple.
for ($x=0;$x<$numrows;$x++) {
$row = $result->fetch_object();
echo ""
echo "".$row->ISBN."";
echo "".$row->Title.""
echo "".$row->Date.""
echo ""
}
In this way we see that to access the column names we use the modifier -> with the structure:
$row->field_name;
This attribute contains the content of the field and the row we are in.
In the code we have mounted the results in a table. So we need some code HTML before our code PHP.
<$numrows;$x++) { $row = $result->fetch_object(); echo “” echo “” echo “” echo “” echo “” } ?>
| ISBN | Title | Date |
| “.$row->ISBN.” | “.$row->Title.” | “.$row->Date.” |
We only have to free the resultset and the connection to the database with the methods free() and close() respectively.
$result->free();
$db->close();
With this code we can make queries to our MySQL database at PHP.