Western CS3319 PHP Workshop

Upon completion of this workshop, you will be able to:

store a .php file in the appropriate location for your virtual machine
view your .php file using a browser
configure your browser so that you can see your php errors
connect from your .php file to a MySQL database
display data on a webpage that came from a table in your MySQL database
insert data into a table in your MySQL database
upload a image file to your virtual machine
display an image file on your virtual machine

This workshop should take about 1 - 1.5 hours

Step 1 - Create a test MySQL database

Log onto your Virtual Machine, remember after you ssh to vm???@cs3319.gaul.csd.uwo.ca (on a mac/linus) or do login as: vm??? (on putty on windows) you still need to ssh to YOUR PERSONAL virtual machine as follows:
ssh rocky@vm???
Get into mysql
mysql -u root -p
Enter the password you gave for your root user in the workshop to set up your Virtual Machine(password is likely cs3319)
Create a sample database:

CREATE DATABASE vetoffice;
USE vetoffice;

Create 2 sample tables (one will hold the names of people who own pets and the other table will hold the pet name and species and associated owner):

CREATE TABLE owner(ownerid CHAR(2), fname VARCHAR(20), lname VARCHAR(20), primary key(ownerid));

CREATE TABLE pet(petid CHAR(2), petname VARCHAR(20), species VARCHAR(20), ownerid CHAR(2), PRIMARY KEY(petid), FOREIGN KEY (ownerid) REFERENCES owner(ownerid) ON DELETE SET NULL);

Add some data to your tables:

INSERT INTO owner VALUES("11","Laura","Reid");
INSERT INTO owner VALUES("22","Marge", "Simpson");
INSERT INTO owner VALUES("33","Peter", "Griffin");
INSERT INTO pet VALUES("54","Waffles","dog","11");
INSERT INTO pet VALUES("56","Chimo","dog","11");
INSERT INTO pet VALUES("57","Santas Little Help","dog","22");
INSERT INTO pet VALUES("58","Snowball","cat","22");
INSERT INTO pet VALUES("59","Brian","dog","33");

Add (i.e. write the SQL to insert a row for yourself to owner table) your first and last name as one of the owners, just make up any fake 2 digit owner id for yourself. Then insert a fake dog (or your real pet) into the pet table and make sure that you make yourself the owner of this new pet.
Do the following commands to make sure the data was added to the tables (make sure some rows show up now):
SELECT * FROM pet;
SELECT * FROM owner;
Look at the data in the tables and make sure you understand how the 2 tables relate and make sure know all the columns, we will use them extensively in this workshop!
Exit from mysql and now you will create the .php files that will read from the database you just created

Step 2 - Create a simple PHP webpage and view it in a browser

Move to the /var/www/html directory
cd /var/www/html
ls
Create a new directory called phpworkshop and move to that directory
mkdir phpworkshop
cd phpworkshop
Create a file called index2.php (I called it index2.php rather than index.php so that i can still see all the files in this directory, if I call it index.php, it will be the first page displayed when going to this directory). I used the editor nano, you can use whatever editor you want.
nano index2.php

Add the following code to the index2.php file:

<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Dr. Western's Vet Clinic</title>
</head>
<body>
<h1>Welcome to the Western Vet Clinic</h1>
<h2>Pets we look after</h2>
<ol>
<li>Dogs</li>
<li>Cats</li>
<li>Birds</li>
</ol>
</body>
</html>

Because your virtual machine is running you should be able to see this file by going to the following URL in a browser:
http://cs3319.gaul.csd.uwo.ca/vm???/phpworkshop/
where ??? is your virtual machine number. Remember to put a / at the very end of the URL or it might not work.
You should see this
Then click on the index2.php files to see the php page you just created for a pet shop.

Step 3 - Connect to your test database with PHP

Now we are going to make our php file connect to our database. In the phpworkshop directory, create another file using an editor like nano called: connectdb.php and put the following code into that file (make sure the database and password are correct for your MySQL): This code will make a connection to the database you just created.

<?php
$dbhost = "localhost";
$dbuser= "root";
$dbpass = "cs3319";
$dbname = "vetoffice";
$connection = mysqli_connect($dbhost, $dbuser,$dbpass,$dbname);
if (mysqli_connect_errno()) {
     die("database connection failed :" .
     mysqli_connect_error() .
     "(" . mysqli_connect_errno() . ")"
         );
    }
?>

Now add the code to your index2.php to call this code (add the red code to your index2.php file). I have purposely misspelled connectdb.php so that we can make sure that php errors are displaying

<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Dr. Western's Vet Clinic</title>
</head>
<body>
<?php
include 'connectdbb.php';
?>
<h1>Welcome to the Western Vet Clinic</h1>
<h2>Pets we look after</h2>
<ol>
<li>Dogs</li>
<li>Cats</li>
<li>Birds</li>
</ol>
</body>
</html>

Now refresh your webpage, you should see something like this (this way we can see syntax errors if you set up your vritual machine according to the instructions given):
NOTE: if you are NOT seeing the errors, then probably you either:

Spelled the connectdb.php correctly in your index2.php file (remember, i said to spell it INCORRECTLY to make sure we are catching errors)
OR you reinstalled php since you set up your virtual machine. ONLY DO THIS IF YOU DO NOT SEE ERRORS AND YOU ARE SURE YOU MISSPELLED connectdb.php! If so, just go to page 10 in the virtual machine setup workshop and redo all of step 4.7.a to 4.7.h (do all the parts of 4.7 where you edit the php.ini file) in order to see the errors.

Now go back and edit index2.php and change the line that currently says:
include 'connectdbb.php';
to
include 'connectdb.php';
then refresh your index2.php page again and make sure it now has no errors.

Step 4 - Display data from your test database in a webpage

Now let's use the "SELECT * FROM pet" SQL command to see our data. The first time we will do it so that it does not look very pretty in the webpage. Create a new file in the phpworkshop directory called getdata.php and put the following code in it:

<?php
$query = "SELECT * FROM pet";
$result = mysqli_query($connection,$query);
if (!$result) {
    die("databases query failed.");
}
while ($row = mysqli_fetch_assoc($result)) {
     var_dump($row);
     echo $row;
}
mysqli_free_result($result);
?>

Go back into index2.php and add the code in red:

<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Dr. Western's Vet Clinic</title>
</head>
<body>
<?php
include 'connectdb.php';
?>
<h1>Welcome to the Western Vet Clinic</h1>
<h2>Pets we look after</h2>
<?php
include 'getdata.php';
?>
<ol>
<li>Dogs</li>
<li>Cats</li>
<li>Birds</li>
</ol>
</body>
</html>

Reload your index2.php page in your browser again. Notice that now you see all the data about each pet but it doesn't look pretty!
Let's try this again but make it look a bit neater. Suppose we only want to see species from the pet table.

Go back and edit getdata.php and change it as follows (I will explain each step as we do it)::

The var_dump command on the 8th line of this file is showing us all everything in the variable holding the data. So $row is an array of our columns, it is holding 4 items, because the pet table has 4 columns and it shows us each row's 4 column values. It is inside a While loop and so it gets each row in the table. You can use var_dump when you want to know quickly what is going on. But now that we know what it does, let's get rid of it. We want to display all the species column out that we created in an html list. So before the while loop we will start the list with the <ol> html tag. We will echo it out to the screen as follows: echo "<ol>";
Then we will start the while loop and keep looping, fetching each row from the table, until we run out of rows.
1. Inside the while loop, we will output the list item html tag(<li>) and then the species column from the pet table for each row (you put the column name from your SQL query between the [ array brackets ] and in quotes. Then we will concatenate (in php a dot . means concatenate) on the close list item html tag (</li>) as follows:
  while ($row = mysqli_fetch_assoc($result)) {
  echo "<li>";
  echo $row["species"] . "</li>";
  }
After the loop is over, we will finish the list by outputting the closing list html tag as follows:
echo "</ol>";

Your code should now look like this:

<?php
$query = "SELECT * FROM pet";
$result = mysqli_query($connection,$query);
if (!$result) {
     die("databases query failed.");
}
echo "<ol>";
while ($row = mysqli_fetch_assoc($result)) {
    echo "<li>";
    echo $row["species"] . "</li>";
}
mysqli_free_result($result);
echo "</ol>";
?>

Save the changes to getdata.php
Go back to your browser and refresh your index2.php and you will now see all the pets. Try to figure out how to show each type of pet only ONCE. HINTS: you should only have to change one line of the code and just use your SQL knowledge.
Now, figure out how to change the code so that it displays a list of each pet owner's first name, last name and his/her pet's name on the webpage, you should see something like this (HINT: you will need to refresh your memory of the pet and owner table structures and fix your SQL statement, then fix what you echo in the while loop):

Now let's make things a bit more fancy.

First of all, let's remove the list of animals in the index2.php and replace it with a button to get an owners pet names, so index2.php should now look like this:

Save index2.php. Notice that the above code is creating a form (in bold red above). When the user hits the submit button, it will serve up the new webpage called getpets.php. Before the user has a chance to hit the button though, it gets all the data from the owner table (using the existing getdata.php code we are going to fix) and then it closes the database connect, we will open that connection again when we go to get the pets from the pets table using the new file getpets.php that we are about to create.

Now let's put all the owner names and owners ownerid into radio buttons (so we will get each row, create a radio button, add in the owner's ownerid as the value for the radio button and then display the owner's first and last name): Modify the getdata.php file so that it looks like this now:

<?php
   $query = "SELECT * FROM owner";
   $result = mysqli_query($connection,$query);
   if (!$result) {
        die("databases query failed.");
    }
   echo "Who are you looking up? </br>";
   while ($row = mysqli_fetch_assoc($result)) {
        echo '<input type="radio" name="petowners" value="';
        echo $row["ownerid"];
        echo '">' . $row["fname"] . " " . $row["lname"] . "<br>";
   }
   mysqli_free_result($result);
?>

Save getdata.php and reload index2.php in the browser again to make sure you are getting every pet owner displayed each as a radio button. Press the button, notice that because we have not yet created the getpets.php file, the button doesn't know what to do.

Now let's make a new webpage that will display the pets names depending on which owner was selected. Create a new file called getpets.php. In this file we will need to:

Set up the title and header and initial html stuff
Open the connection to the database again (we can reuse connectdb.php file)
Get the value for the radio button (the radio button group was named petowners on the previous page (the getdata.php page)) that the user selected. The form will pass over the selected radio button to the new page using a special variable called $POST and the name of the group of radio buttons. So $POST["putthenameoftheradiobuttongrouphere"] will hold the owner id of the owner that the user selected from the list of radio buttons and we are going to put that value into a variable we call $whichOwner.
Create a query to get all the pets of this owner
Display the pet's names neatly in an ordered list
Close the connection to the database.

Here is the code you will need for that file(getpets.php)

<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Dr. Western's Vet Clinic-Your Pets</title>
</head>
<body>
<?php
include 'connectdb.php';
?>
<h1>Here are your pets:</h1>
<ol>
<?php
   $whichOwner= $_POST["petowners"];
   $query = 'SELECT * FROM owner, pet WHERE pet.ownerid=owner.ownerid AND pet.ownerid="' . $whichOwner . '"';
   $result=mysqli_query($connection,$query);
    if (!$result) {
         die("database query2 failed.");
     }
    while ($row=mysqli_fetch_assoc($result)) {
        echo '<li>';
        echo $row["petname"];
     }
     mysqli_free_result($result);
?>
</ol>
<?php
   mysqli_close($connection);
?>
</body>
</html>

Now go back to your browser and try reloading index2.php and click on one of the owners radio button and then press the button to see if you get all the pets for that owner! Press the back button and try to get all the pets of a different pet owner. Press the back button again so that you are on the index2.php page and then try pressing right click on your mouse and then selecting view page source to see the underlying html that you just created/generated. Look at the code above for index2.php and make sure you understand how the value="11" got filled in for Laura Reid or the value="22" got filled in for Marge Simpson (notice the name of the radiobuttons and notice the values for each radio button and then look into your getdata.php code again and find where ownerid is added to the radio button and look at the name for each radiobutton in getdata.php and look at the $POST variable). If this is starting to make sense to you, you are well on your way to understanding how to do assignment 3!

Step 5 - Insert Data Into A Table in A MySQL Database

First of all we need to modify index2.php so that it allows us to enter the new data if a family buys a new pet. Change your index2.php file so that it looks like this (add the red lines in the correct location):

<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Dr. Western's Vet Clinic</title>
</head>
<body>
<?php
include 'connectdb.php';
?>
<h1>Welcome to the Western Vet Clinic</h1>
<h2>Pets we look after</h2>
<form action="getpets.php" method="post">
<?php
include 'getdata.php';
?>
<input type="submit" value="Get Pet Names">
</form>
<p>
<hr>
<p>
<h2> ADD A NEW PET:</h2>
<form action="addnewpet.php" method="post">
New Pet's Name: <input type="text" name="petname"><br>
New Pet's Species: <br>
<input type="radio" name="species" value="dog">Dog<br>
<input type="radio" name="species" value="cat">Cat<br>
<input type="radio" name="species" value="bird">Bird<br>
For which customer: <br>
<?php
include 'getdata.php';
?>
<input type="submit" value="Add New Pet">
</form>
<?php
mysqli_close($connection);
?>
</body>
</html>

Now we need to create a php file that will add the new pet. Notice in the red code above that, when the submit button is pressed, because we now have: form action="addnewpet.php", our page is going to open a file called addnewpet.php. So we need to create that file. Thus, make a new file called addnewpet.php and put the code below in it. This file will insert the data into the pet table.

<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Dr. Western's Vet Clinic-Your Pets</title>
</head>
<body>
<?php
   include 'connectdb.php';
?>
<h1>Here are your pets:</h1>
<ol>
<?php
   $whichOwner= $_POST["petowners"];
   $petName = $_POST["petname"];
   $species =$_POST["species"];
   $query1= 'SELECT max(petid) AS maxid FROM pet';
   $result=mysqli_query($connection,$query1);
   if (!$result) {
          die("database max query failed.");
   }
   $row=mysqli_fetch_assoc($result);
   $newkey = intval($row["maxid"]) + 1;
   $petid = (string)$newkey;
   $query = 'INSERT INTO pet values("' . $petid . '","' . $petName . '","' . $species . '","' . $whichOwner . '")';
   if (!mysqli_query($connection, $query)) {
        die("Error: insert failed" . mysqli_error($connection));
    }
   echo "Your pet was added!";
   mysqli_close($connection);
?>
</ol>
</body>
</html>

Now go into the browser, reload your index2.php file and add a new pet for one of the people at the bottom of the webpage (fill in the fields below the horizontal line), then go back to the first page and look up the pets for that person, (ie select their name in the first set of radio buttons above the horizontal line and hit submit) to see if it was added!

Step 6 - Upload an image file from your desktop to your Virtual Machine using PHP

Now we are going to add a picture of some of our pets. First we have to add a column to hold the picture file name in the pet table in our database. Go back into mysql and connect to the database:
mysql -u root -p
USE vetoffice;
Now add a spot to hold the pet's image to the pet table.
ALTER TABLE pet ADD petpicture VARCHAR(150);
Now add some data to that new column:
UPDATE pet SET petpicture="http://www.csd.uwo.ca/~lreid/cs3319/workshops/php/images/brian.jpg" WHERE petid="59";
UPDATE pet SET petpicture="snowball.jpg" WHERE petid="58";
Use the SQL select command to make sure it was changed.
Exit mysql
We want to allow someone to run our webpage and from our webpage, press a button to choose a file on their own local machine and upload it to our virtual machine. We want to upload these files to a subfolder of where our php code is located. IT MUST BE A SUBFOLDER NOT THE SAME FOLDER as where our php code is located because we are going to lower the permissions on the subfolder in order for the file to be allowed to upload BUT if we lowered the permission on our folder where our php code is stored, then other people could read our php code (we just want people to use our webpages and not copy the code we worked hard to write!). So we are going to make a subfolder and lower the permissions ONLY on the subfolder as follows
cd /var/www/html/phpworkshop
mkdir uploadarea
chmod 1777 uploadarea

Now we are going to change the form where we add a pet so that we can upload a file containing a picture our pet. Go into the index2.php file and add the red parts below:

...
<h2> ADD A NEW PET:</h2>
<form action="addnewpet.php" method="post" enctype="multipart/form-data" >
New Pet's Name: <input type="text" name="petname"><br>
New Pet's Species: <br>
<input type="radio" name="species" value="dog">Dog<br>
<input type="radio" name="species" value="cat">Cat<br>
<input type="radio" name="species" value="bird">Bird<br>
<input type="file" name="file" id="file"><br>
For which customer: <br>
<?php
include 'getdata.php';
?>
<input type="submit" value="Add New Pet">
</form>
...

Reload the index2.php in the browser and press on the Choose File button and then hit the Cancel button. Notice that the second red line above is an html tag (<input type="file" ..) that uploads files and searches the directory. This means that this html tag does all the hard work of coding the ability to search our computer for a file and we don't have to do this, HURRAY!

Now we have to add the code that will:

Check to make sure we uploading a valid image type (.jpg, .gif, etc...)
Check to make sure the image isn't too big (in bytes)
Check to make sure the file uploaded
Make sure the file name doesn't already exist in our uploadarea subfolder
Move up the file and store it in the uploadarea subfolder

Create a variable called $petpic to hold the location and name of the current file
Here is the code to do those 6 things, put this code in a file called upload_file.php:

<?php
$allowedExts = array("gif", "jpeg", "jpg", "png");
  $temp = explode(".", $_FILES["file"]["name"]);
  $extension = end($temp);
  $extension = strtolower($extension);
  $uploadholder = dirname(__FILE__) . "/uploadarea";
  if ((($_FILES["file"]["type"] == "image/gif")
      || ($_FILES["file"]["type"] == "image/jpeg")
      || ($_FILES["file"]["type"] == "image/jpg")
      || ($_FILES["file"]["type"] == "image/pjpeg")
      || ($_FILES["file"]["type"] == "image/x-png")
      || ($_FILES["file"]["type"] == "image/png"))
      && ($_FILES["file"]["size"] < 500000)
      && in_array($extension, $allowedExts)) {
            if ($_FILES["file"]["error"] > 0) {
                        echo "Return Code: " . $_FILES["file"]["error"] . "<br>";
            } else {
                        if (file_exists("uploadarea/" . $_FILES["file"]["name"])) {
                                    echo '<p><hr>';
                                    echo $_FILES["file"]["name"] . " already exists. ";
                                    echo '<p><hr>';
                                    $petpic = "NULL";
                        } else {
                                    move_uploaded_file($_FILES["file"]["tmp_name"],"uploadarea/" . $_FILES["file"]["name"]);
                                    $petpic = "uploadarea/" . $_FILES["file"]["name"];
                        } // end of else
            } // end of else
     } else {
            echo "Invalid file";
    } //end of else
?>

Now go into the addnewpet.php and change the end of the line:
$query = 'INSERT INTO pet ....
to be (make sure it looks exactly the same as this):
$query = 'INSERT INTO pet values("' . $petid . '","' . $petName . '","' . $species . '","' . $whichOwner . '","' . $petpic . '")';
In the same file, right after the <body> tag, where you have:
<body>
<?php
include 'connectdb.php';
?>
change it to:
<body>
<?php
include 'upload_file.php';
include 'connectdb.php';
?>
Now run your program in the browser and add a new pet but upload a picture for the new pet from your computer. If you want, you can go into your mysql database and see if the picture was inserted correctly.

Step 7 - Display the image in a webpage using PHP

Now we are going to add to the picture to beside the pet. Edit the file getpets.php and add the following line to put in an <img> image tag in the while loop, just after outputting the pet's name:
echo '<img src="' . $row["petpicture"] . '" height="60" width="60">';
Now go into the browser and pick one of the owners who have a pet with an associated picture and click on the get pets button. You should now see the image next to the pets name!

Step 8 - Some extra PHP info

Here are some other variables you might find useful. Create a new file called index3.php and put the following code in it

<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>PHP cs3319 workshop</title>
</head>
<body>
<h1>Some extra PHP stuff</h1>
Here are some useful variables:
<ul>
<?php
echo '<li>' . $_SERVER['SERVER_NAME'] ;
echo '<li>' . $_SERVER['REMOTE_ADDR'] ;
echo '<li>' . $_SERVER['DOCUMENT_ROOT'] ;
echo '<li>' . $_SERVER['SCRIPT_FILENAME'] ;
echo '<li>' . $_SERVER['PHP_SELF'] ;
?>
</ul>
<?php
phpinfo();
?>
</body>
</html>

Open index3.php in a browser and see what you get. Some of this extra information might be useful for you!

Step 9 - WHOO HOO - Hand it in now!

You have now created a little web application that shows the name of pet owners and their pets and adds new pets to an owner and displays the pet's pictures. Well done!

Make sure that you now go to BrightSpace>Assignments>Writing Some PHP Code and hand in a link to your Pet Application. You will get 1% for handing in this link. It should look like this: http://cs3319.gaul.csd.uwo.ca/vm???/phpworkshop/ where ??? is your virtual machine number. Remember to put a / at the very end of the URL or it might not work.