The LIST. The LIST project is a very simple full-stack project that builds on the previous project, "The TABLE". It assumes a simple table named "list" with four columns and about 10 records. You can download an SQL script that will build the table for you here.
You can open the completed version of the project in your browser here.
If you want to download completed versions of the PHP scripts and image files for The LIST, you can find them here.
The LIST Setup. The first task is to create a directory to hold the project, and it should be located inside the "htdocs" folder inside MAMP or XAMPP. The htdocs directory is where your AMP setup (MAMP/XAMPP) expects to find URLs that run from localhost. I recommend you name it n413_list. Inside this directory, use your text or code editor to create a quick test file named index.html that simply contains "Hello World". If you are working with MAMP, you should be able to point your browser to http://localhost:8888/n413_list and see "Hello World" in you browser. For XAMPP, simply leave off the :8888 and you should see the same result.
Once you have success, make a few changes:
- Change the index.html file extension to .php and reload it in your editor.
- Change "Hello World" to this:
<?php echo 'Hello World (PHP version)'; ?>
- Reload your browser and you should see the PHP version of Hello World displayed there.
If you are new to PHP, you have just written a PHP script. The Apache server detected the ".php" file extension, and instead of sending the entire contents of the file to the browser, it computed the output, looking for the opening and closing PHP tags. Inside those tags, the "echo" command indicated what to send to the browser. If you want to experiment, try changing the file extension back to ".html" and notice the result. You might also try writing some HTML markup outside the PHP tags and notice the result when you run the file as ".php".
- One note here about PHP: If you are new to PHP, please understand that this is NOT a course in PHP (or HTML, CSS, Javascript, SQL, etc.). Beginning PHP is a topic in itself, and although you should be able to pick up enough PHP knowledge to keep up as we go, it is not a substitute for actually learning the fundamentals and syntax for PHP --or the other languages.
So now that you have a simple test working in your AMP setup, let's work out how to draw your list from MySQL to the browser.
The Connection Script. The first task for any PHP project that gets data from MySQL is to create a database connection. Since this is done for each project, we will create the connection in a separate script that can be included in all the scripts we create. Create a new file called n413connect.php. Create the opening PHP tag: "<?php", then create a few empty lines before writing the closing tag: "?>".
A MySQL connection needs four pieces of information:
- A host name (The MySQL host localhost:8889 for MAMP, localhost:3306 for XAMPP)
- A user name (Your user account on the MySQL host -- this will be "root" in our case)
- A user password (MAMP users will use "root", XAMPP users will use empty quotes -- "")
- The database name (The database we created in The TABLE was "ampjam_db")
So the first step in creating the connection script is to store the information items in four variables. Add this to your n413connect script:
<?php
$dbhost = 'localhost:8889'; //XAMPP is 'localhost:3306'
$dbuser = 'root';
$dbpwd = 'root'; //XAMPP password is ''
$dbname = 'ampjam_db';
?>
Next, we add a call to the mysqli library. The mysqli library is a set of functions created to interact with MySQL (or MariaDB). It replaces an older library simply called "mysql", but when then library was improved, "i" was added to the name, and the old library was deprecated (retired). We will use the mysqli_connect() function. The mysqli_connect() function requires the four variables as arguments, and returns the connection as an object. We will store the object in a variable called $link for later use. The script looks like this:
<?php
$dbhost = 'localhost:8889'; //XAMPP is 'localhost:3306'
$dbuser = 'root';
$dbpwd = 'root'; //XAMPP password is ''
$dbname = 'ampjam_db';
$link = mysqli_connect($dbhost, $dbuser, $dbpwd, $dbname);
?>
Last, add an error message so you know if the connection failed. If mysqli_connect() returns false, $link will not be true. In that case, we will kill the PHP script. We will use calls to some mysqli error functions and concatenate (merge) the resulting messages with PHP's string concatenation operator, the period or dot("."). The error message will display in the browser. The full connection script is:
<?php
$dbhost = 'localhost:8889'; //XAMPP is 'localhost:3306'
$dbuser = 'root';
$dbpwd = 'root'; //XAMPP password is ''
$dbname = 'ampjam_db';
$link = mysqli_connect($dbhost, $dbuser, $dbpwd, $dbname);
if (!$link) { die('Connect Error (' . mysqli_connect_errno() . ') '. mysqli_connect_error()); }
?>
Save the n413connect.php file in your htdocs folder. You can run it if you wish:
MAMP:http://localhost:8888/n413_list/n413connect.php
--or--
XAMPP:http://localhost/n413_list/n413connect.php
If you don't see anything in the browser, it worked.
List 1 - Simple List. Let's start by drawing the simplest version of the list table to the browser. Create a file named list_1.php in your htdocs directory. Begin the file with the php tags, and insert an "include" to pull in the connection script:
<?php
include("n413connect.php");
?>
The result will be that the connection script will work just as though it was written in the new script, and the $link variable will be available for the new script to use.
Next, we write an SQL query --that is, a statement that will be sent to MySQL to query the database. We will make a "SELECT" query that will select any data in the database that matches the conditions we put in the query. The query will ask for any "item" fields in the list table's records.
Once we have asked MySQL to "SELECT" an "item", we must specify where the item comes from. For this, we use the command "FROM" and follow it with the name of the table, "list". Our query will simply ask for data from one table, but SQL can retrieve data from multiple tables at once. In that case, we would specify all the tables, separated by commas.
The table's name is "list", but that word could mean something else in SQL language, so we place "list" in backticks to be sure that MySQL understands it is the table name. The query will be stored in a string variable named $sql. It looks like this:
<?php
include("n413connect.php");
$sql = "SELECT item FROM `list`";
?>
The next line sends the query to MySQL. It will use the mysqli_query() function, which needs two arguments: the connection and the query. The connection is the $link variable from the connection script. The result is returned and stored in a variable, which will be called $result:
<?php
include("n413connect.php");
$sql = "SELECT item FROM `list`";
$result = mysqli_query($link, $sql);
?>
The returned result is a type of data structure called a "resource", and is not directly accessible from PHP. You must access it through mysqli functions. We will convert the result to PHP arrays using mysqli_fetch_array(). mysqli_fetch_array() requires two arguments: the query result and a value that indicates whether the array should be numeric, associative (an array that uses strings for index keys), or both. We will ask for both, and the value is represented by a PHP constant, "MYSQLI_BOTH".
The query result is a two-dimensional array, with the table rows being the outer array, and the row columns being the inner array for each row. That means we must step through each row, then access the fields (columns) of the row in a second step. We can step through the rows using a "while" loop. mysqli_fetch_array() utilizes a "pointer" index that always knows the next row to access. The function either returns the array with that row's data, or it returns "false". We can use this as logic for whether to continue asking for rows. The script with the "while" loop looks like this:
<?php
include("n413connect.php");
$sql = "SELECT item FROM `list`";
$result = mysqli_query($link, $sql);
while ($row = mysqli_fetch_array($result, MYSQLI_BOTH)){
echo $row["item"].'<br/>';
}
?>
You can test this now. ( MAMP: http://localhost:8888/n413_list/list_1.php or
XAMPP: http://localhost/n413_list/list_1.php )
List 2 - Image List. Let's add some images to the list. If you don't already have any image files for your database items, find some and enter the file names into your database table records. Then, place the image files in a folder named "images" inside the n413_list directory.
You can download the images being used for this example here.
Copy the list_1.php file into list_2.php. Open list_2.php in your editor and add a new column into the SQL query. The new query should look like this:
$sql = "SELECT item, image FROM `list`";
This will add the "image" field or column into each row in the result. The field names are separated with commas, but be sure the last field is not followed by a comma.
Next, let's use the image column to write an <img> tag to the browser. Change the "echo" command inside the "while" loop to this:
echo '<img src="'.$row["image"].'" style="height:75px;margin-right:20px;" />'.$row["item"].'<br/>';Notice the details of how the single and double quotes are used.
- The overall string for the echo command is in single quotes.
- Attribute values in HTML use double quotes.
- The single-quoted string is closed when PHP variables are inserted and then reopened where the string continues.
- The double quotes for HTML must be inside the single quotes to properly render to the browser.
- The associative array keys inside the square brackets for $row are double-quoted also, but these are outside the single-quoted string.
- The dot operator (".") is used to concatenate (join) strings in PHP.
The complete script should look like this:
<?php
include("n413connect.php");
$sql = "SELECT item, image FROM `list`";
$result = mysqli_query($link, $sql);
while ($row = mysqli_fetch_array($result, MYSQLI_BOTH)){
echo '<img src="'.$row["image"].'" style="height:75px;margin-right:20px;" />'.$row["item"].'<br/>';
}
?>
Test this version now. ( MAMP: http://localhost:8888/n413_list/list_2.php or
XAMPP: http://localhost/n413_list/list_2.php )
List 3 - Web Page. Next, let's change the structure of the PHP script to make it more compatible with developing a web page. Copy list_2.php into a new file named list_3.php. Edit the query to include all the columns from the list table. You can either replace all the column names with an asterisk ("*"), or you can list them all, separated by commas:
<?php
include("n413connect.php");
$sql = "SELECT id, item, description, image FROM `list`";
$result = mysqli_query($link, $sql);
while ($row = mysqli_fetch_array($result, MYSQLI_BOTH)){
echo '<img src="'.$row["image"].'" style="height:75px;margin-right:20px;" />'.$row["item"].'<br/>';
}
?>
Next, we will store the query result in an array for later use. The first step is to create a variable named "$records" and make it an empty array. Then, instead of echo-ing each row directly to the browser, we will push the row onto the $records array inside the "while" loop:
<?php
include("n413connect.php");
$sql = "SELECT id, item, description, image FROM `list`";
$result = mysqli_query($link, $sql);
$records = array();
while ($row = mysqli_fetch_array($result, MYSQLI_BOTH)){
$records[] = $row;
}
?>
In PHP, the "square bracket" operator pushes data onto an array. Each row ($row) is itself an array with an element corresponding to each column from the list table.
Storing the result data in $records will allow us to write a web page in the usual way, and "drop in" the PHP data, instead of needing to write HTML around the script that queries the database.
An effective way to drop in the contents of an array to the HTML is to use the "foreach" loop, which will iterate through the array and assign each array element to a variable:
foreach ($records as $record){... This allows us to write any number of HTML passages to the browser by writing the HTML template once and drawing it many times using the loop. We will use CSS formatted img and div elements, but you could render large tables or other complex HTML structures this way:
<?php
include("n413connect.php");
$sql = "SELECT id, item, description, image FROM `list`";
$result = mysqli_query($link, $sql);
$records = array();
while ($row = mysqli_fetch_array($result, MYSQLI_BOTH)){
$records[] = $row;
}
?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=yes">
<title>Full Stack Amp Jam List Project</title>
<style>
body { font-family:Arial; }
img { display:inline-block;vertical-align:top;height:75px;margin-left:50px;margin-right:20px;margin-bottom:10px; }
.desc { display:inline-block;width:60%;font-family:Arial;margin-bottom:10px; }
</style>
</head>
<body>
<h2>Full Stack Amp Jam List Project</h2>
<?php
foreach ($records as $record){
echo '
<div>
<img src="images/'.$record["image"].'" />
<div class="desc"> <b>'.$record["item"].'</b> '.$record["description"].'</div>
</div>';
}
?>
</body>
</html>
This style of coding allows a greater separation between data and presentation, and allows tasks like styling and other formatting to be done in the usual way. The PHP tags are in two separate sections of the code, but the variables in each section are available to the other. Make your own version of this script to draw your list in the browser. Do some styling or make a CSS style sheet to format the list however you would like.
List 4 - Interaction. Copy your list_3.php file into a new file named list_4.php. With this file, we will write an "onclick" handler as we draw each passage of HTML in the "foreach" loop. We will have the onclick handler call a Javascript function and pass it arguments with information needed to do something.
In our case, we will simply pass the "id" of the record and the "item" text, then use that information to display an alert. But later, we can do a lot more. For example, with the "id" value, we could make edits to the text and pass it back to the server using AJAX calls. The record I.D. would make it possible to update the record in the database.
Let's start with the Javascript function, enclosed in <script> tags:
<script>
function show_alert(id,item){
alert("You have clicked Item "+id+", "+item+".");
}
</script>
Insert this at the end of the script, between the </body> and </html> tags. Notice that since this code goes outside the PHP tags, it is ordinary Javascript, using the Javascript plus ("+") operator for string concatenation.
Next, edit the line of HTML in the "echo" statement found in the "foreach" loop to include an "onclick" handler that calls the Javascript function:
echo '
<div>
<img src="images/'.$record["image"].'" />
<div class="desc" onclick="show_alert();"> <b>'.$record["item"].'</b> '.$record["description"].'</div>
</div>';
For the sake of simplicity, the code above includes the Javascript function with no arguments. Let's look at the code when we add in the "id" argument:
echo '
<div>
<img src="images/'.$record["image"].'" />
<div class="desc" onclick="show_alert('.$record["id"].');"> <b>'.$record["item"].'</b> '.$record["description"].'</div>
</div>';
This time, we have Javascript being written as a string inside PHP. The dot operator (".") is being used for string concatenation when the PHP variable is inserted.
Also note that the string we are writing for the "echo" command is formatted with single quotes, while the Javascript inside the onclick attribute is formatted in double quotes. The "id" argument is an integer, and we don't need quotes around an integer argument in Javascript. However, the next argument we must add is "item", which is a string, and must be quoted. Since we already have single quotes and double qoutes being used, we will need to "escape" the single quotes required for Javascript with a backslash character ("\"). This will render the Javascript properly to the browser, but the syntax becomes quite confusing here. Study it carefully:
echo '
<div>
<img src="images/'.$record["image"].'" />
<div class="desc" onclick="show_alert('.$record["id"].', \''.$record["item"].'\');"> <b>'.$record["item"].'</b> '.$record["description"].'</div>
</div>';
Remember, that the single quote adjacent to the dot operator is for the "echo" string. The other single quote is for Javascript, and requires the escape backslash.
The last change is to add a cursor style for a pointer. In this example, make the change in the ".desc" class in the <style> section. The working version of the code looks like this:
(list_4.php)
<?php
include("n413connect.php");
$sql = "SELECT id, item, description, image FROM `list`";
$result = mysqli_query($link, $sql);
$records = array();
while ($row = mysqli_fetch_array($result, MYSQLI_BOTH)){
$records[] = $row;
}
?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=yes">
<title>Full Stack Amp Jam List Project</title>
<style>
body { font-family:Arial; }
img { display:inline-block;vertical-align:top;height:75px;margin-left:50px;margin-right:20px;margin-bottom:10px; }
.desc { display:inline-block;width:60%;font-family:Arial;margin-bottom:10px;cursor:pointer;}
</style>
</head>
<body>
<h2>Full Stack Amp Jam List Project</h2>
<?php
foreach ($records as $record){
echo '
<div>
<img src="images/'.$record["image"].'" />
<div class="desc" onclick="show_alert('.$record["id"].', \''.$record["item"].'\');"> <b>'.$record["item"].'</b> '.$record["description"].'</div>
</div>';
}
?>
</body>
<script>
function show_alert(id,item){
alert("You have clicked Item "+id+", "+item+".");
}
</script>
</html>
Give it a test! If the interactivity doesn't work, Use your browser's developer tools to find out where the trouble is. Console messages will usually have clues, and looking at the browser source code will show you how the Javascript actually rendered to the browser. You may have problems with the escaped quotes, so give those a careful look.
Completed Project. You can open the completed version of the project in your browser here.
Here are completed versions of the scripts used for this project:
(n413connect.php)<?php $dbhost = 'localhost:8889'; //XAMPP is 'localhost:3306' $dbuser = 'root'; $dbpwd = 'root'; //XAMPP password is '' $dbname = 'ampjam_db'; $link = mysqli_connect($dbhost, $dbuser, $dbpwd, $dbname); if (!$link) { die('Connect Error (' . mysqli_connect_errno() . ') '. mysqli_connect_error()); } ?>
(list_1.php)<?php include("n413connect.php"); $sql = "SELECT item FROM `list`"; $result = mysqli_query($link, $sql); while ($row = mysqli_fetch_array($result, MYSQLI_BOTH)){ echo $row["item"].'<br/>'; } ?>
(list_2.php)<?php include("n413connect.php"); $sql = "SELECT item, image FROM `list`"; $result = mysqli_query($link, $sql); while ($row = mysqli_fetch_array($result, MYSQLI_BOTH)){ echo '<img src="'.$row["image"].'" style="height:75px;margin-right:20px;" />'.$row["item"].'<br/>'; } ?>
(list_3.php)<?php include("n413connect.php"); $sql = "SELECT id, item, description, image FROM `list`"; $result = mysqli_query($link, $sql); $records = array(); while ($row = mysqli_fetch_array($result, MYSQLI_BOTH)){ $records[] = $row; } ?> <!DOCTYPE html> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /> <meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=yes"> <title>Full Stack Amp Jam List Project</title> <style> body { font-family:Arial; } img { display:inline-block;vertical-align:top;height:75px;margin-left:50px;margin-right:20px;margin-bottom:10px; } .desc { display:inline-block;width:60%;font-family:Arial;margin-bottom:10px; } </style> </head> <body> <h2>Full Stack Amp Jam List Project</h2> <?php foreach ($records as $record){ echo ' <div> <img src="images/'.$record["image"].'" /> <div class="desc"> <b>'.$record["item"].'</b> '.$record["description"].'</div> </div>'; } ?> </body> </html>
(list_4.php)<?php include("n413connect.php"); $sql = "SELECT id, item, description, image FROM `list`"; $result = mysqli_query($link, $sql); $records = array(); while ($row = mysqli_fetch_array($result, MYSQLI_BOTH)){ $records[] = $row; } ?> <!DOCTYPE html> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /> <meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=yes"> <title>Full Stack Amp Jam List Project</title> <style> body { font-family:Arial; } img { display:inline-block;vertical-align:top;height:75px;margin-left:50px;margin-right:20px;margin-bottom:10px; } .desc { display:inline-block;width:60%;font-family:Arial;margin-bottom:10px;cursor:pointer;} </style> </head> <body> <h2>Full Stack Amp Jam List Project</h2> <?php foreach ($records as $record){ echo ' <div> <img src="images/'.$record["image"].'" /> <div class="desc" onclick="show_alert('.$record["id"].', \''.$record["item"].'\');"> <b>'.$record["item"].'</b> '.$record["description"].'</div> </div>'; } ?> </body> <script> function show_alert(id,item){ alert("You have clicked Item "+id+", "+item+"."); } </script> </html>
If you want to download completed versions of the PHP scripts and image files, you can find them here.
If you are ready, lets proceed to building The FORM!