PHP pages can access a SQL database using the mysqli extension.

This section won’t make sense until you have the following:

• Ability to write a bit of PHP code
• Ability to write SELECT and INSERT queries in SQL
• A SQL database to work with

Creating the SQL database connection

First you need to create a mysqli object in your PHP file. This is the connection to a database inside your server.
Cloud 9 – Connecting PHP to mySQL

<?php

$conn = mysqli_connect("localhost", "dniemitalo", "", "shoes");

?>

This creates a database object called $conn which you will use later to run database queries.

Most examples show this process in a different way. The following does the same thing except with the parameters stored as variables:

$host = "localhost";
$user = "dniemitalo";
$password = "";
$database = "shoes";

$conn = mysqli_connect($host, $user, $password, $database);

Explanation:

The host is the website with the database, and “localhost” tells the server to reference itself rather than connecting to a remote site.

User should be your cloud9 username, and password should be blank for a Cloud9 database. In a real website,  you would of course use a secure password.

The database name must match an existing database, not a table name.

INSERT INTO query: Create a Record

INSERT INTO is an easy SQL query to try first, because you can look in your SQL database to see if it worked.

First, craft an SQL query and test it in the mySQL command line.
(Reminder: go to bash terminal and type “mysqli-ctl cli”)

Example SQL query:

INSERT INTO shoe (style) VALUES ('slippers');

Once you’ve proven to yourself that your query works, you can use it in your PHP code. If it doesn’t work, fix it before moving on!

$sql = "INSERT INTO shoe (style) VALUES ('slippers')";
mysqli_query($conn, $sql);

The SQL query command text is stored as the string variable $sql, and notice that it is enclosed in double quotes. Use single quotes around values like ‘slippers’ to avoid errors. The semicolon that we needed in SQL command line is omitted, BUT we need a semicolon after double quotes to finish the PHP command.

Add a command to close your database connection, and then you can run this page. Make sure the page ends with a .php extension.

PHP file so far:

<?php
$conn = mysqli_connect("localhost", "dniemitalo", "", "shoes");
$sql = "INSERT INTO shoe (style) VALUES ('slippers')";
mysqli_query($conn, $sql);
mysqli_close($conn);
?>

Open this page. If it works, it displays a blank screen since there is no HTML content, and nothing was echoed in PHP.

Now go back to your mySQL command line and look at your data to see if the new record is there:

SELECT * FROM shoe;

Or if you prefer, you can browse the data in phpMyAdmin instead.

SELECT Query: Retrieve Data

You can also use PHP to execute the SELECT query from above:

$sql = "SELECT * FROM shoe";
$result = mysqli_query($conn, $sql);

This time we created a $result object. This object contains the retrieved data.

To output the first row of data, we can do this:

$row = mysqli_fetch_assoc($result);
echo $row['size'];
echo $row['color'];
echo $row['style'];

$row is an array, and as you can see, the indexes match the names of the database fields.

If you copy and paste the above block of code twice so it runs twice, you get two rows of data. Each time mysqli_fetch_assoc() is called, the server will point to the next row of data in $result.

To output all of the data rows, you can use a while loop:

while($row = mysqli_fetch_assoc($result)) {
    echo $row['size'];
    echo $row['color'];
    echo $row['style'];
}

You might want to read about PHP while loops. This while loop ends when the $result array runs out of records. Each time the loop repeats, $row is a different array representing a different database record.

This code will mash the data into one line of HTML output with no spaces. You can make it look a little nicer:

while($row = mysqli_fetch_assoc($result)) {
    $size = $row['size'];
    $color = $row['color'];
    $style = $row['style'];
    echo "Size $size, $color $style<br>";
}

This example saves the shoe values as variables and then echoes those variables as part of a string. Since double quotes are used, PHP replaces the variable with its value when it creates the HTML output.