Querying a MySQL Database with PHP

The reason for using PHP as an interface to MySQL is to format the results of SQL queries in a form visible in a web page. As long as you can log into your MySQL installation using your username and password, you can also do so from PHP.

 

However, instead of using MySQL’s command line to enter instructions and view output, you will create query strings that are passed to MySQL. When MySQL returns its response, it will come as a data structure that PHP can recognize instead of the formatted output you see when you work on the command line. Further PHP com‐mands can retrieve the data and

format it for the web page

 

 

The Process of connecting MYSQL with PHP

The process of using MySQL with PHP is as follows:

1. Connect to MySQL and select the database to use.

2. Build a query string

3. Perform the query.

4. Retrieve the results and output them to a web page.

5. Repeat steps 2 to 4 until all desired data has been retrieved.

6. Disconnect from MySQL.

 




Creating a Login File

Most websites developed with PHP contain multiple program files that will require  access to MySQL and will thus need the login and password details.

Therefore, it’s sensible to create a single file to store these and then include that file wherever it’s needed. Example:

<?php // login.php

 $hn = 'localhost';

 $db = 'publications';

 $un = 'username';

 $pw = 'password';

?>

Type the example, replacing username and password with the values you use for your MySQL database, and save it to the document root directory We’ll be making use of the file shortly.The hostname localhost should work as long as you’re using a

MySQL database on your local system, and the database publications should work if you’re typing the same as the one in the xammp or any other local server you are using.

 

<?php and ?> tags mean that the lines between can be interpreted only as PHP code.

The $hn variable will tell PHP which computer to use when connecting to a database.

 

Connecting to a PHP MySQL Database 

 

Now that you have the login.php file saved, you can include it in any PHP files that will need to access the database by using the require_once statement. This is preferable to an include statement, as it will generate a fatal error if the file is not found.

 

And believe me, not finding the file containing the login details to your database is a fatal error.

 

Also, using require_once instead of require means that the file will be read in only when it has not previously been included, which prevents wasteful duplicate disk accesses.

 

Connecting to a MySQL server with mysqli

<?php

 require_once 'login.php';

 $conn = new mysqli($hn, $un, $pw, $db);

 if ($conn->connect_error) die($conn->connect_error);

?>

 

This example creates a new object called $conn by calling a new instance of the mysqli method, passing all the values retrieved from the login.php file. Error checking is achieved by referencing the

 $conn->connect_error property.

 

The -> operator indicates that the item on the right is a property or method of the object on the left. In this case, if connect_error has a value, then there was an error,so we call the die function and display that property, which details the connection error.

 

The $conn object is used in the following examples to access the MySQL database.

 

Building and executing a query

Sending a query to MySQL from PHP is as simple as issuing it using the query method of a connection

object.

 




 Querying a database with mysqli

 

<?php

 $query = "SELECT * FROM classics";

 $result = $conn->query($query);

 if (!$result) die($conn->error);

 ?>

 

Here the variable $query is assigned a string containing the query to be made, and then passed to the query method of the $conn object, which returns a result that we place in the object $result. If $result is FALSE, there was a problem and the error property of the connection object will contain the details, so the die function is called to display that error.

 

All the data returned by MySQL is now stored in an easily interrogatable format in the $result object.

 

Fetching a result

 

Once you have an object returned in $result, you can use it to extract the data you want, one item at a time, using the fetch_assoc method of the object.

 

Fetching results one cell at a time

 

 <?php // query.php

 require_once 'login.php';

 $conn = new mysqli($hn, $un, $pw, $db);

 if ($conn->connect_error) die($conn->connect_error);

 $query = "SELECT * FROM classics";

 $result = $conn->query($query);

 if (!$result) die($conn->error);

 $rows = $result->num_rows;

 for ($j = 0 ; $j < $rows ; ++$j)

 {

 $result->data_seek($j);

 echo 'Author: ' . $result->fetch_assoc()['author'] . '<br>';

 $result->data_seek($j);

 echo 'Title: ' . $result->fetch_assoc()['title'] . '<br>';

 $result->data_seek($j);

 echo 'Category: ' . $result->fetch_assoc()['category'] . '<br>';

 $result->data_seek($j);

 echo 'Year: ' . $result->fetch_assoc()['year'] . '<br>';

 $result->data_seek($j);

 echo 'ISBN: ' . $result->fetch_assoc()['isbn'] . '<br><br>';

 }

 $result->close();

 $conn->close();

?>

 

Fetching a row

 

To fetch one row at a time, replace the for loop from Example  above with the one highlighted in bold

Fetching results one row at a time

 

<?php //fetchrow.php

 require_once 'login.php';

 $conn = new mysqli($hn, $un, $pw, $db);

 if ($conn->connect_error) die($conn->connect_error);

 $query = "SELECT * FROM classics";

 $result = $conn->query($query);

 if (!$result) die($conn->error);

 $rows = $result->num_rows;

 for ($j = 0 ; $j < $rows ; ++$j)

 {

 $result->data_seek($j);

 $row = $result->fetch_array(MYSQLI_ASSOC);

 echo 'Author: ' . $row['author'] . '<br>';

 echo 'Title: ' . $row['title'] . '<br>';

 echo 'Category: ' . $row['category'] . '<br>';

 echo 'Year: ' . $row['year'] . '<br>';

 echo 'ISBN: ' . $row['isbn'] . '<br><br>';

 }

 $result->close();

 $conn->close();

?>

 

In this modified code, only one-fifth of the interrogations of the $result object are made (compared to the previous example), and only one seek into the object is made in each iteration of the loop, because each row is fetched in its entirety via the fetch_array method. This returns a single row of data as an array, which is then assigned to the array $row.

 

The fetch_array method can return three types of array according to the value

passed to it:

 

MYSQLI_NUM

 

Numeric array. Each column appears in the array in the order in which you defined it when you created (or altered) the table. In our case, the zeroth element of the array contains the Author column, element 1 contains the Title, and so on.

 

Querying a MySQL Database with PHP

 

MYSQLI_ASSOC

Associative array. Each key is the name of a column. Because items of data are referenced by column name (rather than index number), use this option where possible in your code to make debugging easier and help other programmers better manage your code.

 

MYSQLI_BOTH

 

Associative and numeric array.Associative arrays are usually more useful than numeric ones because you can refer to each column by name, such as$row['author'], instead of trying to remember where

it is in the column order. So this script uses an

associative array, leading us to pass

MYSQLI_ASSOC.

 

Closing a connection PHP will eventually return the memory it has allocated for objects after you have finished with the script, so in small scripts, you don’t usually need to worry about releasing memory yourself.

 

However, if you’re allocating a lot of result objects or fetching large amounts of data, it can be a good idea to free the memory you have been using to prevent problems later in your script.

 

This becomes particularly important on higher-traffic pages, because the amount of memory consumed in a session can rapidly grow. Therefore, note the calls to the close methods of the objects $result and $conn in the preceding scripts, as soon as each object is no longer needed, like this:

$result->close();

$conn->close();

 

A Practical Example

It’s time to write our first example of inserting data in and deleting it from a MySQL table using PHP.

 <?php // sqltest.php

 require_once 'login.php';

 $conn = new mysqli($hn, $un, $pw, $db);

 if ($conn->connect_error) die($conn->connect_error);

 if (isset($_POST['delete']) && isset($_POST['isbn']))

 {

 $isbn = get_post($conn, 'isbn');

 $query = "DELETE FROM classics WHERE isbn='$isbn'";

 $result = $conn->query($query);

 if (!$result) echo "DELETE failed: $query<br>" .

 $conn->error . "<br><br>";

 }

 if (isset($_POST['author']) &&

 isset($_POST['title']) &&

 isset($_POST['category']) &&

 isset($_POST['year']) &&

 isset($_POST['isbn']))

 {

 $author = get_post($conn, 'author');

 $title = get_post($conn, 'title');

 $category = get_post($conn, 'category');

$year = get_post($conn, 'year');

 $isbn = get_post($conn, 'isbn');

 $query = "INSERT INTO classics VALUES" .

 "('$author', '$title', '$category', '$year', '$isbn')";

 $result = $conn->query($query);

 if (!$result) echo "INSERT failed: $query<br>" .

 $conn->error . "<br><br>";

 }

 echo <<<_END

 <form action="sqltest.php" method="post"><pre>

 Author <input type="text" name="author">

 Title <input type="text" name="title">

 Category <input type="text" name="category">

 Year <input type="text" name="year">

 ISBN <input type="text" name="isbn">

 <input type="submit" value="ADD RECORD">

 </pre></form>

_END;

 $query = "SELECT * FROM classics";

$result = $conn->query($query);

 if (!$result) die ("Database access failed: " . $conn->error);

 $rows = $result->num_rows;

 for ($j = 0 ; $j < $rows ; ++$j)

 {

 $result->data_seek($j);

 $row = $result->fetch_array(MYSQLI_NUM);

 echo <<<_END

 <pre>

 Author $row[0]

 Title $row[1]

 Category $row[2]

 Year $row[3]

 ISBN $row[4]

 </pre>

 <form action="sqltest.php" method="post">

 <input type="hidden" name="delete" value="yes">

 <input type="hidden" name="isbn" value="$row[4]">

 <input type="submit" value="DELETE RECORD"></form>

_END;

 }

 $result->close();

 $conn->close();

 function get_post($conn, $var)

 {

 return $conn->real_escape_string($_POST[$var]);

 }

?>

 

N/B:The get_post function,  passes each item it retrieves through thereal_escape_string method of the connection object to strip out any characters that

a hacker may have inserted in order to break into or alter your database, like this:

function get_post($conn, $var)

{

 return $conn->real_escape_string($_POST[$var]);

}