PHP and SQL

PHP is often to use form data to read from, or update, SQL databases.

Database Connection

Remember the order of parameters – you connect to the server with your name and password. Then you select the database to be used.

In this example, three possible responses are given to a connection failure – it would be unlikely to use all three.

PHP
01
02
03
04
05
06
07
08
09
10
11
12
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "studentlist";
 
$conn = mysqli_connect($servername, $username, $password, $dbname);
 
if (!$conn) {
    echo '<img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" data-wp-preserve="%3Cscript%3Ealert(%22Error%20inserting%20data%22)%3B%3C%2Fscript%3E" data-mce-resize="false" data-mce-placeholder="1" class="mce-object" width="20" height="20" alt="&lt;script&gt;" title="&lt;script&gt;" />'; // pop-up message
    <p>Error inserting data</p> die("Error adding data");   // error on page
    die("Connection to database failed");                   // halt script
}

At the end of the script, the database connection should be closed:

PHP
1
mysqli_close($conn);

SQL Insert/Update/Delete

These SQL queries retrun a Boolean True/False result that can be used to check for success:

PHP
1
2
3
4
5
$sql = "INSERT INTO students VALUES ($forename, $surname);
$result = mysqli_query($conn, $sql);
if (!result) {
    echo '<img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" data-wp-preserve="%3Cscript%3Ealert(%22Error%20inserting%20data%22)%3B%3C%2Fscript%3E" data-mce-resize="false" data-mce-placeholder="1" class="mce-object" width="20" height="20" alt="&lt;script&gt;" title="&lt;script&gt;" />';
}

SQL Select

Select queries return will 0, 1 or more records:

PHP
01
02
03
04
05
06
07
08
09
10
11
12
$sql = "SELECT forename, surname FROM students";
$result = mysqli_query($conn, $sql);
  
$studentsFound = mysqli_num_rows($result);
if ($studentsFound >= 0) {
    echo "<table>";
    while($row = mysqli_fetch_array($result)) {
        echo "<tr><td" . $row["forename"] . "</td><td>" . $row["surname"] . "</td></tr>";
    echo "/table>";
} else {
    echo "<p>No results found</p>"
}