When you aren’t logged into the MySQL command line, here’s a handy piece of code that you can use to verify that a table has been correctly created from inside a browser. It simply issues the query DESCRIBE devices and then outputs an HTML table with four headings—Column, Type, Null, and Key—underneath which all columns. Within the table are shown. To use it with other tables, simply replace the name devices in the query with that of the new table.

Describing the devices table

The output from the program should look like this:
Column Type Null Key
id smallint(6) NO PRI
family varchar(32) NO
name varchar(32) NO
age tinyint(4) NO

Let’s assume that you are working for a audio store and need to create a database to hold details about all the types of devices in the store. You are told that there are nine families of devices—Amplifier, minidisc, DVD, C-casette, Turntable, DAT, DCC, Turner, and DAB—so you’ll need a column for that. Then each device has been given a name, so that’s another column, and you also want to keep track of their ages, which is another. Of course, you will probably need more columns later, perhaps to hold voltage requirements, and other details, but for now that’s enough to get going. A unique identifier is also needed for each device, so you also decide to create a column for that called id.

The code you might use to create a MySQL table to hold this
data, with the main query assignment in bold text.

Creating a table called devices

As you can see, the MySQL query looks just like what you would type directly at the command line, except without the trailing semicolon.

You are now ready to look at some practical techniques that you can use in PHP to access the MySQL database, including tasks such as creating and dropping tables; inserting, updating, and deleting data; and protecting your database and website from malicious users. Note that the following examples assume that you’ve already created the login.php program discussed earlier in this website.

Query is sent to MySQL asking to see all the records in the classics table, like this:

$query = “SELECT * FROM classics”;
$result = $conn->query($query);

After that, $rows is set to a value representing the number of rows in the table:

$rows = $result->num_rows;

Using the value in $rows, a for loop is then entered to display the contents of each row. Then the program populates the array $row with a row of results by calling the fetch_array method of $result, passing it the constant value MYSQLI_NUM, which forces the return of a numeric (rather than associative) array, like this:

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

With the data in $row, it’s now a simple matter to display it within the heredoc echo statement that follows, in which I have chosen to use a <pre> tag to line up the display of each record in a pleasing manner.
After the display of each record, there is a second form that also posts to sqltest.php (the program itself) but this time contains two hidden fields: delete and isbn. The delete field is set to yes and isbn to the value held in $row[4], which contains the ISBN for the record.
Then a submit button with the name DELETE RECORD is displayed, and the form is closed. A curly brace then completes the for loop, which will continue until all records have been displayed, at which time the $result and $conn object’s close methods are closed to release resources back to PHP:

$result->close();
$conn->close();

program sanitizes copies of the elements we will be outputting from the $row array into the variables $r0 through $r4 by passing them to the htmlspecialchars function, to replace any potentially dangerous HTML characters with harmless HTML entities.
Then the part of code that displays the output follows, using an echo
<<<_END…_END structure as seen in previous chapters, which outputs everything between the _END tags.

Instead of using the echo command, the program could drop out
of PHP using ?>, issue the HTML, and then reenter PHP processing
with <?php. Which style is used is a matter of programmer
preference, but I always recommend staying within PHP code, for
these reasons:

• It makes it very clear when you’re debugging (and also for other users) that everything within a .php file is PHP code. Therefore, there is no need to go hunting for dropouts to HTML.
• When you wish to include a PHP variable directly within HTML, you can just type it. If you had dropped back to HTML, you would have had to temporarily reenter PHP processing, access the variable, and then drop back out again.

The HTML form section simply sets the form’s action to sqltest.php. This means that when the form is submitted, the contents of the form fields will be sent to the file sqltest.php, which is the program itself. The form is also set up to send the fields as a POST rather than a GET request. This is because GET requests are appended to the URL being submitted and can look messy in your browser. They also allow users to easily modify submissions and try to hack your server (although that can also be
achieved with in-browser developer tools). Additionally, avoiding GET requests prevents too much information appearing in server log files. Therefore, whenever possible, you should use POST submissions, which also have the benefit of revealing less posted data.

Having output the form fields, the HTML displays a submit button with the name ADD RECORD and closes the form. Note the <pre> and </pre> tags here, which have been used to force a monospaced font that lines up all the inputs nearly. The carriage returns at the end of each line are also output when inside <pre> tags.

Prior to checking whether new data has been posted, the program checks whether the variable $_POST[‘delete’] has a value. If so, the user has clicked the DELETE RECORD button to erase a record. In this case, the value of $isbn will also have been posted.
As you’ll recall, the ISBN uniquely identifies each record. The HTML form appends the ISBN to the DELETE FROM query string created in the variable $query, which is then passed to the query method of the $conn object to issue it to MySQL.
If $_POST[‘delete’] is not set (and so there is no record to be deleted), $_POST[‘author’] and other posted values are checked. If they have all been given values, $query is set to an INSERT INTO command, followed by the five values to be inserted. The string is then passed to the query method, which upon completion returns either TRUE or FALSE. If FALSE is returned an error message is displayed, like this:

if (!$result) echo “INSERT failed”;

The POST request is usually preferred (because it avoids placing unsightly data in the browser’s address bar), and so we use it here. The
web server bundles up all of the user input (even if the form was filled out with a hundred fields) and puts in into an array named $_POST.

Depending on whether a form has been set to use the POST or the GET method, either the $_POST or the $_GET associative array will be populated with the form data. They can both be read in exactly the same way.

Each field has an element in the array named after that field. So, if a form contains a field named isbn, the $_POST array contains an element keyed by the word isbn. The PHP program can read that field by referring to either $_POST[‘isbn’] or $_POST[“isbn”] (single and double quotes have the same effect in this case).

There is no reason to write to an element in the $_POST array. Its
only purpose is to communicate information from the browser to
the program, and you’re better off copying data to your own variables before altering it.

So, back to the get_post function, which passes each item it retrieves through the real_escape_string method of the connection object to escape any quotes 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]);
}

It’s time to write our first example of inserting data in and deleting it from a MySQL table using PHP.
Save file it to your web development directory using the filename sqltest.php

It first checks for any inputs that may have been made and then either inserts new data into the table classics of the publications database or deletes a row from it, according to the input supplied. Regardless of whether there was input, the program then outputs all rows in the table to the browser. So, let’s see how it works. The first section of new code starts by using the isset function to check whether values for all the fields have been posted to the program. Upon confirmation, each line within the if statement calls the function get_post, which appears at the end of the program. This function has one small but critical job: fetching input from the browser.

For reasons of clarity and brevity, and to explain things as simply as
possible, many of the following examples omit certain very sensible
security precautions that would have made them longer and possibly detract from explaining their function in the clearest manner.
Therefore, it is important that you don’t skip past the section later
in this chapter on preventing your database from being hacked , in which you will learn about additional actions you can take with your code to secure it.

Fetching results one row at a time, code here

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 column, and so on.

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. 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();

Ideally, you should close each result object when you have finished
using it, and then close the connection object when your script will
not be accessing MySQL anymore. This best practice ensures that
resources are returned to the system as quickly as possible to keep
MySQL running optimally, and alleviates doubt over whether PHP
will return unused memory in time for when you next need it.

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. Type this script in and save it using the filename query-mysqli.php

Fetching results one cell at a time:
Link to code

Here, each time around the loop, we call the fetch_assoc method to retrieve the value stored in each cell, and output the result using echo statements.