PHP Tutorial: How to Run MySQL Queries with PHP Obtaining Data from a MySQL Database Using the PHP Mysqli Object

SQL statements are used to obtain data from databases. A PHP programmer can use them with the mysqli object to build a very powerful application.

SQL (Structured Query Language) is the computer language with which humans (such as PHP programmers) communicate with the majority of databases (such as MySQL). It is pseudo-English - meaning that the queries can be understood relatively easily by anyone who reads them for example:

Create database if not exists db_demo;

This means, “create a database named db_demo (unless it already exists). Another example is:

Select user, host from user;

Which means, “display the contents of two fields (user and host) from the table named ‘user’”. These two queries show that there are different types of query and the type of query uses affects the result returned to the PHP application. Fortunately PHP handles all types of queries equally as well.

PHP and MySQL Query Results

SQL queries can be divided into two categories:

1-Queries used for data definition and manipulation such as:

• Create
• Insert
• Update
• Delete

2-Queries used for data retrieval. Technically these are still data manipulation queries and always starts with the same key word:

• Select

The type of query used affects the result returned to the database:

• Data retrieval queries return a set of records (or recordset) to the user
• All queries return a True if the query is successful or False if it fails

It is, therefore, just a matter of sending the queries to the database and then processing the results.

Sending Queries to MySQL from PHP

Regardless of the query to be used the process is always the same:

• Connect to the database
• Send the query to the database
• Process the results returned from the database

The PHP programmer carries out all of these by creating a PHP mysqli (MySQL – improved) object:

$user = "db_user";
$user_password = "db_password";
$db_name = "db_demo";
$db = new mysqli ("localhost", $user, $user_password, $db_name);

Here a new mysqli object is instantiated and this automatically connects to the database. Next the object’s query method is used to send SQL to the database:

$sql = "insert into articles
(title, url)
values
('How to Use the PHP Text Database API',
'https://steemit.com/@alv/";
if (!($db->query($sql))) {
echo $db->error . "
";
} else {
echo "Added " . $db->affected_rows . " record(s)
";
}

In the example above only true or false is returned, but the query methods can also be used to return a record set:

$sql = "select * from articles";
if ($rs = $db->query($sql)) {

If the record set has been obtained then it can be processed record by record. Each record in the record set is accessed as an array (containing both associative and numerical elements):

echo "";
while ($record = $rs->fetch_array(MYSQLI_BOTH))
#Alternatives are MYSQLI_ASSOC and MYSQLI_NUM
{
echo "";
}
echo "

" . $record['title'] . "
" . $record[2] . "
";
}

Finally the memory used by the record set and the connection can be freed up once they’ve been finished with:

$rs->close();
$db->close();

At the end of this process a new record will have been added to the database and the web page user will see the records stored in the table displayed in the web browser.

Summary

SQL is the language with which humans communicate with many databases. The PHP programmer can use SQL to write queries that they can send to MySQL databases by using the mysqli object and its query method.

Once the resulting record set is returned to the PHP application then the programmer can process the results and load the records into arrays by using the fetch_array method. They can then use the information elsewhere in their application or display them to the users of their web site.

---

Posted on Utopian.io - Rewarding Open Source Contributors

---