CSY2089

Web Programming
Topic 4
Tom Butler
[Link]@[Link]
 Topic 4

●
Connecting to MySQL with PHP
 Connecting to MySQL From PHP

●
There are three different libraries that can be used in
PHP to connect to MySQL
– mysql
– mysqli (short for MySQL Improved)
– PDO (short for PHP Data Objects)
●
They all essentially do the same job but the code used
is different for each one!
 MySQL
●
The MySQL extension is the oldest way of connecting to MySQL from
PHP and is now very out of date
●
You may come across code examples using this online (e.g. w3Schools)
, however they will not work with recent PHP versions
●
If you see code using the function mysql_connect() to connect to the
database look for a newer example (the date of the article will be a
giveaway, it will probably be from about 2001!)
●
mysql_connect(), mysql_query() and similar functions are no longer
supported by PHP. If you use them they will not work unless you’re
using an old version of PHP. If you use them in the assignment you will
get an F- as I will not be able to run your work!
 MySQLI

●
MySQLI, short for MySQL Improved is an updated
method of connecting from PHP to MySQL
●
It offers several features and security enhancements
that are not available in the basic MySQL extension
●
However, it's not used frequently any more (as of
2010ish) as it's been superseded by PDO
●
It’s more difficult to use than PDO and is less flexible so
there’s little reason to choose it in modern projects
 PDO
●
PDO stands for PHP Data Objects and isn't specific to MySQL
●
It allows you to connect to any kind of database e.g.
– MySQL
– SQLite
– Microsoft SQL Server
– Oracle
●
The commands for PDO are not specific to the database, unlike the mysqli extension
– If you learn how to use PDO to connect to MySQL you can use almost identical code to connect to other databases
●
Using PDO generally means writing less code as well!
●
Most PHP projects and developers choose PDO for these reasons.
●
See also:
– [Link]
– [Link]
– [Link]
 Crash course in OOP

●
PDO uses Object Oriented Programming
●
You need a very basic understanding of objects and
classes to use it
 What is an object?

●
An object is similar to an array:
– You can store more than one value in a single variable
– Each variable is stored under a unique key
●
An object can also contain functions which can access
that data
 Objects

●
Those functions work on data stored inside the object
●
This avoids passing lots of arguments into a function
repeatedly, the information is “encapsulated” inside the
object and does not need to be sent to the function
each time
 Objects

●
PHP includes a class called DateTime which can be used
to represent dates and times
●
This is similar to the date function but has more
functionality
 DateTime
●
When you create an instance of the DateTime class its
date is set to today's date
●
You can display the date using the format function
●
To call a function, you use the arrow operator -> and
the function name, followed by arguments:

<?php

$date = new DateTime();

Output:
echo $date->format('d/m/Y');
26/11/2020
?>
 DateTime

●
Each DateTime instance can be used to represent a
single date and time.
●
The date can be defined when you create the instance
by passing it in as an argument:
●
When a new instance is created, it can be passed
values as arguments. You can use the instance name
like a function
<?php

$date = new DateTime('2012-02-01');

Output:
echo $date->format('d/m/Y');
01/02/2012
?>
 Objects

●
The current date is stored inside the object. When you
print out the date, the date is “remembered” by the
object and all you need to tell it is how to format the
date
●
You can set the date once, and use it multiple times
<?php
Output:
$date = new DateTime('2012-02-01');
01/02/2012
echo $date->format('d/m/Y');
02-01-2012
echo $date->format('m-d-Y');

?>
 Objects

●
To do this in a non-object-oriented way using functions
you would need to provide the data multiple times
●

<?php

Output:
echo format('d/m/Y', '2012-02-01'); 01/02/2012
02-01-2012
echo format('m-d-Y', '2012-02-01');

?>
 Objects

●
As the date is stored inside the object. You can adjust
the date using the modify function
●
Because the date is already stored, you only have to
pass it information about how the date is changing:
● <?php
$date = new DateTime('2018-11-20'); Output:
20/11/2018
echo $date->format('d/m/Y'); 18/11/2018
16/08/2018
$date->modify('-2 Days');

echo $date->format('d/m/Y');

$date->modify('-2 Days');

echo $date->format('d/m/Y');

?>
 Objects

●
Objects are use to represent a real-world concept (e.g. a
date)
●
This can be one or more related values (e.g.
day/month/year to represent a date)
●
As well as some behaviour for modifying or retrieving
those values
– e.g. subtracting or adding to the date
– Or formatting the date using a specific format
 PDO

●
Connecting to MySQL from PDO needs the same
information as connecting from MySQL workbench:
– A server address
– A username
– A password
●
However it also needs a schema name
 PDO
●
To connect to the database you can use the following
code:
$server = 'mysql';
$username = 'student';
$password = 'student';

//The name of the schema we created earlier in MySQL workbench

//If this schema does not exist you will get an error!
$schema = 'csy2089';

$pdo = new PDO('mysql:dbname=' . $schema . ';host=' . $server, $username, $password);

●
 PDO

●
PDO is an object like `DateTime` the object and has
several functions available that you can call
●
One of them is query which is used to query the
database
●
Query takes a string and returns an array of results
 PDO
●
To run the query SELECT * FRO M person that lists all the
people in the database use the query function on the
$pdo object
●
$server = 'mysql';
$username = 'student';
$password = 'student';

//The name of the schema we created earlier in MySQL workbench

//If this schema does not exist you will get an error!
$schema = 'csy2028';

$pdo = new PDO('mysql:dbname=' . $schema . ';host=' . $server, $username, $password);

$results = $pdo->query('SELECT * FROM person');

●
This will store all the records in the $results variable
 PDO

●
You can loop through each result using a foreach loop:
$results = $pdo->query('SELECT * FROM person');

foreach ($results as $row) {

}

●
The $row variable is an array which represents the
record
●
The keys are the field names and the values are the
field values
 PDO

●
To print out each person's first name you can read the
firstname key from the $row array:
●
The keys in the array are created from the column
names in the database table
$results = $pdo->query('SELECT * FROM person');

foreach ($results as $row) {

echo '<p>' . $row['firstname'] . '</p>';
}

Output:
Dave

John

Jo

Sue
 PDO

●
Each coulmn from the table is represented in the $row
array:
– firstname
– surname
– email
– birthday
 PDO
$results = $pdo->query('SELECT * FROM person');

foreach ($results as $row) {

echo '<p>' . $row['firstname'] . ' ' . $row['surname'] . ' was born on ' . $row['birthday'] . '</p>';
}

Output:
Dave Smith was born on 1993-12-23

John Smith was born on 1991-02-25

Jo Richards was born on 1989-03-03

Sue Evans was born on 1984-09-04

 PDO

●
PDO queries support anything supported by the
database e.g. WHERE clauses:
$results = $pdo->query('SELECT * FROM person WHERE surname = "Smith"');

foreach ($results as $row) {

echo '<p>' . $row['firstname'] . ' ' . $row['surname'] . ' was born on ' . $row['birthday'] . '</p>';
}

Output:
Dave Smith was born on 1993-12-23 [Link]

John Smith was born on 1991-02-25 [Link]

 PDO

●
Like any string, you can add variables to a query:
$surname = 'Smith';

$results = $pdo->query('SELECT * FROM person WHERE surname = "' . $surname . '"');

foreach ($results as $row) {

echo '<p>' . $row['firstname'] . ' ' . $row['surname'] . ' was born on ' . $row['birthday'] . '</p>';
}

●
Hint: This is useful when you want to use search criteria
from $_GET or $_POST!

Warning: THIS CODE IS INSECURE AND FOR DEMONSTRATION ONLY

 Insecure!

●
This code is very insecure
$results = $pdo->query('SELECT * FROM person WHERE surname="' . $_POST['surname'] . '"');

●
If “Smith” is entered in the surname box then the query
that is executed is:
SELECT * FROM person WHERE surname="Smith”

●
This works, but the user can type anything into the box!
 MySQL

●
This can be a problem as users cannot be trusted
●
What if they entered abc”123 (with the quote) into the
box?
●
The query that would be executed is:
SELECT * FROM person WHERE surname="abc"123"

●
Which is invalid and will cause an error
 MySQL

●
MySQL Also allows you to run more than one query at
once by separating them by a semicolon ( ; )

$pdo->query('SELECT * FROM person WHERE surname="Smith";SELECT * FROM person');

●
This would actually issue both queries
 MySQL
$results = $pdo->query('SELECT * FROM person WHERE surname="' . $_POST['surname'] . '"');
●

●
Given the following query, what would happen in the
user typed
– “; DELETE FROM person ; SELECT “
●
Into the surname box?

SELECT * FROM person WHERE surname=""; DELETE FROM person; SELECT ""
 SQL Injection

●
This is known as SQL Injection
●
This is very dangerous and leaving your database
exposed to these exploits can be dangerous:
– People can get unauthorised access to data
– Change things without you knowing
– Delete records
 SQL Injection

●
There are two ways to prevent SQL injection
1) escaping quotes
– 2) Prepared statements
 SQL Injection

●
To prevent SQL injection you can escape special
characters with a slash. For example:
$pdo->query('SELECT * FROM person WHERE surname="abc\"123";');

●
Would actually search the database for the string
including the quote:
– abc”123
 Escaping Quotes

●
PDO provides a function called quote. This does two
jobs:
– Puts quotes around the string
– Escapes any special characters
echo $pdo->quote('Smith'); //Prints “Smith” (with quotes)
echo $pdo->quote('abc"123'); //Prints “acb\”123” (with quotes)

●
This can be used to prevent SQL injection:
$results = $pdo->query('SELECT * FROM person WHERE surname=' . $pdo->quote($_POST['surname']));
 Prepared statements

●
A second method of solving this problem is Prepared
Statements
●
These are a special type of query that is compiled
before it is executed
●
Instead of writing a query by building a string using
concatenation, you write the query with placeholders in
the string which will be replaced when the query is
executed
 Prepared Statements

●
This is done using the PDO prepare function
●
When a query is prepared it is only stored ready to be
executed
●
You must also call the execute function to send the
query to the database
 Prepared Statements

●
When you write a query to be prepared you don't need
to include quotes or the value you want to use in the
query
●
Instead you use a placeholder
●
This placeholder is a name of your choice prefixed with
a colon ( : )
●
e.g.

$pdo->prepare('SELECT * FROM person WHERE surname = :name');

 Prepared statements
$pdo->prepare('SELECT * FROM person WHERE surname = :name');

●
The prepare function returns a PDOStatement Object
●
This object is a prepared query which you can later
execute, you must store the object in a variable to
execute the query:
$stmt = $pdo->prepare('SELECT * FROM person WHERE surname = :name');

●
By convention this variable is called $stmt
 Prepared Statements

●
Once you have a $stmt object you can execute the
query
●
When you execute the query using the execute function
you must provide it an array of replacements for the
placeholders you put in the query
$stmt = $pdo->prepare('SELECT * FROM person WHERE surname = :name');

●
This query has a placeholder called “name”
 Prepared Statements
●
To provide the replacements for the placeholder you must
create an array with the placeholder names as the keys and
the values you wish to use in the query as the values:
$stmt = $pdo->prepare('SELECT * FROM person WHERE surname = :name');

$values = [
'name' => $_POST['surname']
];

$stmt->execute($values);
●
This will send the query to the database
●
However, unlike the query function, execute does not return
the records that matched the query
 Prepared Statements

●
Once you have executed the query, you can use a
foreach() loop to loop over all the records
$stmt = $pdo->prepare('SELECT * FROM person WHERE surname = :name');

$values = [
'name' => $_POST['surname']
];

$stmt->execute($values);

foreach ($stmt as $row) {

echo '<p>' . $row['firstname'] . '</p>';
}
 Prepared Statements

●
Prepared statements are slightly more code but they
are:
– More secure as they're immune to SQL injection
– Often more readable because you don't need to use string
concatenation
– Faster to execute
Inserting records
 Adding records
●
INSERT queries can be run to add records
INSERT INTO person ( firstname, surname, email, birthday)
VALUES (“John”, Smith”, “john@[Link]”, “1992-04-05”)

●
When writing an INSERT query you will need to provide three
parts:
– The name of the table
– A comma separated list of column names you are writing to (often
all the columns in the table)
– A comma separated list of values to write to those columns
 Prepared Statements
●
To write to a table using a prepared statement you can
use the following:

$stmt = $pdo->prepare('INSERT INTO person (email, firstname, surname, birthday)

VALUES (:email, :firstname, :surname, :birthday)
');

$values = [
'firstname' => 'John',
'surname' =>'Smith',
'email' => 'john@[Link]',
'birthday' => '1992-04-05'
];
$stmt->execute($values);
 Prepared statements and forms

●
The values can come from forms:

$stmt = $pdo->prepare('INSERT INTO person (email, firstname, surname, birthday)

VALUES (:email, :firstname, :surname, :birthday)
');

$values = [
'firstname' => $_POST['firstname'],
'surname' => $_POST['surname'],
'email' => $_POST['email'],
'birthday' => $_POST['birthday']
];
$stmt->execute($values);
 Form to database

●
It is very useful to be able to take data from a HTML
form and display write it to a database
●
This can all be done in one file using an if statement
and the following page structure
●
It is good practice to do this in one file as it makes edit
forms a lot easier (see next week)
// If the submit button was pressed
if (isset($_POST['submit'])) {
// process the form
}
else {
// display the form
}
// If the submit button was pressed
if (isset($_POST['submit'])) {
$stmt = $pdo->prepare('INSERT INTO person (email, firstname, surname)
VALUES (:email, :firstname, :surname)
');

$values = [
'firstname' => $_POST['firstname'],
'surname' => $_POST['surname'],
'email' => $_POST['email']
];
$stmt->execute($values);
}
else {
?>
<form action="[Link]" method="POST">
<label>First name:</label>
<input type="text" name="firstname" />

<label>Surname:</label>
<input type="text" name="surname" />

<label>Email:</label>
<input type="text" name="email" />

<input type="submit" name="submit" value=”Submit” />

</form>
<?php
}
 Exercise 1
●
1) Get PHP to list the details of all the people in the database in a <ul> element
– The list should be in the format:
●
[Firstname] [Surname] was born on [birthday] and their email address is [email]
●
2) Add a “Search” form with a user input and allow searching of the user by their name
– Hint: Add the search form to the list from part (1)!
●
3) Add a drop down for “field” that allows selection of which database field to search e.g.
“Surname”, “First name”, “Email”
●
4) Add a registration form that allows the user to add a record to the database table using
the website. You will need to use an INSERT query and a HTML form
– Hint: Use 3 select boxes to handle the date
●
Hint: You can verify that your records have been inserted correctly by viewing the table in
MySQL Workbench
 UPDATE queries
●
UPDATE queries look different from the other queries
●
They require several pieces of information and are a bit
like a cross between an INSERT and a SELECT
●
You have to find one or more existing records with a
WHERE clause
●
And provide the new data for the record

UPDATE person SET

firstname=”Johnathan”,
surname=”Smith”
WHERE email=”john@[Link]”
 Easy mistake

●
What is subtly wrong with this code?

UPDATE person SET

firstname=”Johnathan”,
surname=”Smith”,
WHERE email=”john@[Link]”
 Easy mistake
●
What is subtly wrong with this code?
UPDATE person SET
firstname=”Johnathan”,
surname=”Smith”,
WHERE email=”john@[Link]”

●
MySQL sees a comma as a separator between column
names. In this example it will see “WHERE” as the name
of the next column to write data to!
●
Correct code:
UPDATE person SET
firstname=”Johnathan”,
surname=”Smith”
WHERE email=”john@[Link]”
 Edit forms

●
Unlike forms which add records and run an INSERT
query on submit, edit forms require some extra
information
– 1) When the corresponding update query is run, some
information about what record is being edited is required, in
addition to the data which is being stored
– 2) The edit form should be pre-filled with the existing data

UPDATE person SET

firstname=”Johnathan”,
surname=”Smith”
WHERE email=”john@[Link]”
 Pre-filling the form

●
To fill an edit form with its contents you need to select
the existing record from the database
●
This is best done with a get variable:
– [Link]?email=john@[Link]
The edit page can then query for the record set in the
email variable
$stmt = $pdo->prepare('SELECT * FROM person WHERE email = :email');

$values = [
'email' => $_GET['email']
];
$stmt->execute($values);

//Fetch the first record from the query

$user = $stmt->fetch();

//Display the form but write the value to each input

?>
<form action="[Link]" method="POST">
<label>First name:</label>
<input type="text" name="firstname" value="<?php echo $user['firstname']; ?>" />

<label>Surname:</label>
<input type="text" name="surname" value="<?php echo $user['surname']; ?>" />

<label>Email:</label>
<input type="text" name="email" value="<?php echo $user['email']; ?>" />

<input type="submit" name="submit" value=”Submit” />

</form>
<?php
 Passing back the record being edited

●
The update query which runs must be contain the new
values and the old primary key
●
When this form is submitted it will send the values
– Firstname
– Surname <form action="[Link]" method="POST">
<label>First name:</label>
<input type="text" name="firstname" value="<?php echo $user['firstname']; ?>" />
– Email
<label>Surname:</label>
<input type="text" name="surname" value="<?php echo $user['surname']; ?>" />

<label>Email:</label>
<input type="text" name="email" value="<?php echo $user['email']; ?>" />

<input type="submit" name="submit" value=”Submit” />

</form>
 Passing back the record being edited
●
And needs to run a query something like:
UPDATE person SET
firstname=”Johnathan”,
surname=”Smith”,
email=”john@[Link]”
WHERE email=”john@[Link]”

●
This will work if John’s email is john@[Link]
●
But what if John changes his email from john@[Link] to
john@[Link] ?
●
The query will need to know both the old email address and
the new one to correctly update the record
 Sending two emails

●
The form can be amended to send back the current
email address along with the new one:
<form action="[Link]" method="POST">
<label>First name:</label>
<input type="text" name="firstname" value="<?php echo $user['firstname']; ?>" />

<label>Surname:</label>
<input type="text" name="surname" value="<?php echo $user['surname']; ?>" />

<label>Current email:</label>
<input type="text" name="oldemail" value="<?php echo $user['email']; ?>" />

<label>New Email:</label>
<input type="text" name="email" value="<?php echo $user['email']; ?>" />

<input type="submit" name="submit" value=”Submit” />

</form>
 Using the old email

●
The query can then be amended to use the oldemail
value for the WHERE clause:

UPDATE person SET

firstname=”Johnathan”,
surname=”Smith”,
email=”---new email---”
WHERE email=”--old email--”
 Hidden inputs

●
Instead of including two email addresses on the form for
the user to see, you can use a hidden input
●
A hidden input is sent as part of the form
●
But the user cannot see it or amend the contents:
<form action="[Link]" method="POST">
<label>First name:</label>
<input type="text" name="firstname" value="<?php echo $user['firstname']; ?>" />

<label>Surname:</label>
<input type="text" name="surname" value="<?php echo $user['surname']; ?>" />

<input type="hidden" name="oldemail" value="<?php echo $user['email']; ?>" />

<label>Email:</label>
<input type="text" name="email" value="<?php echo $user['email']; ?>" />

<input type="submit" name="submit" value=”Submit” />

</form>
 PDO Shortcut

●
When creating prepared statements you have to create
the $values array
●
This often is a direct copy of the POST array!

$stmt = $pdo->prepare('INSERT INTO person (email, firstname, surname)

VALUES (:email, :firstname, :surname)
');

$values = [
'firstname' => $_POST['firstname'],
'surname' => $_POST['surname'],
'email' => $_POST['email']
];
$stmt->execute($values);
 PDO Shortcut
●
Rather than manually creating a $values array, you can just
use the existing POST array:

$stmt = $pdo->prepare('INSERT INTO person (email, firstname, surname)

VALUES (:email, :firstname, :surname)
');

$stmt->execute($_POST);

●
The $_POST array already contains the keys
– email
– firstname
– surname
 <form action="[Link]" method="POST">
<label>First name:</label>
<input type="text" name="firstname" />

<label>Surname:</label>
<input type="text" name="surname" />

<label>Email:</label>
<input type="text" name="email" />

<input type="submit" name="submit" value=”Submit” />

</form>

●
This will cause an error because the submit button will
be sent as part of the form
●
$_POST[‘submit’] is set and there is no placeholder for
it:
$stmt = $pdo->prepare('INSERT INTO person (email, firstname, surname)
VALUES (:email, :firstname, :surname)
');
$stmt->execute($_POST);
 <form action="[Link]" method="POST">
<label>First name:</label>
<input type="text" name="firstname" />

<label>Surname:</label>
<input type="text" name="surname" />

<label>Email:</label>
<input type="text" name="email" />

<input type="submit" name="submit" value=”Submit” />

</form>

●
To remedy this you need to remove the submit button
from $_POST using unset:

$stmt = $pdo->prepare('INSERT INTO person (email, firstname, surname)

VALUES (:email, :firstname, :surname)
');
unset($_POST['submit']);
$stmt->execute($_POST);
 Prepared Statements
$stmt = $pdo->prepare('INSERT INTO person (email, firstname, surname, birthday)
● VALUES (:email, :firstname, :surname, :birthday)
');
unset($_POST['submit']);
$stmt->execute($_POST);

●
Is both less code and easier to read than
$pdo->query('INSERT INTO person (email, firstname, surname, birthday)
VALUES ("' . $_POST['email'] . '", "' . $_POST['firstname'] .
'", "' . $_POST['surname'] . '", "' . $_POST['birthday'] . '")
');
 Exercise 2
●
1) Add an edit form for editing users
– Hint: Use a GET variable to supply the email address of the user being edited
●
2) On the list of users add a link to edit the user
– Hint: Pass a GET variable as part of the link
●
3) Add a delete button next to each user to allow deleting the user.

●
You should now have pages for listing, editing, adding and deleting
users
●
Remember: You will need code to display the form and some code to
process the form. See last week for a recap!
 MySQL – Auto Increment

●
Every table in a database should have a primary key
●
This is a piece of data that is unique to each record
●
For the person table last week we used the email
address field as the primary key because each person
has a unique email address
 Primary Keys

●
Sometimes there is not an obvious field for a primary
key
●
When this happens you can use a number to give each
record its own ID.
●
This column is usually called id and will store sequential
numbers for each record added
●
The first record added will be given the ID 1, the second
2 and so on
 Auto Increment

●
In a lot of databases (such as oracle) you have to
manually calculate the next available ID in the table
●
MySQL provides a feature called Auto Increment that
does this for you
●
An auto increment column can be added to any table
●
It must be the primary key
 Example table

●
Consider a table that stores messages posted by a
specific user that stores the following information:
●
Who posted the message (firstname, surname and
email address)
●
The time the message was posted
●
The text of the message
 Messages table
●
This table will need the following fields:
– First name
– Surname
– Email address
– Date of message
– Message text
●
None of these are good candidate for a primary key:
– Multiple people could share the same name
– The same person could post two messages (so their email cannot be used)
– Multiple people could post messages at the same date/time
– Multiple people could post the same message
 Auto increment

●
Because no field is unique there is no obvious choice
for the primary key
●
In this case it would be better to use a sequential
number for each record
●
This can be done using MySQL's auto_increment feature
●
To create an auto increment column create and INT
column and tick the PK and AI boxes in MySQL
Workbench
Auto increment
 Adding records
●
Some records can now be added
●
When issuing an INSERT query and using auto
increment, if you do not supply a value for the auto
increment column, a number is generated
automatically:
$stmt = $pdo->prepare('INSERT INTO message (email, firstname, surname, date, message)
VALUES (:email, :firstname, :surname, :date, :message)
');

$date = date('Y-m-d H:i:s');

$values = [
'firstname' => 'John',
'surname' => 'smith',
'email' => 'john@[Link]',
'date' => $date,
'message' => 'I posted a message!'
];

$stmt->execute($values);
 Auto increment
●
Each time you add a record it adds to the list.
●
$stmt = $pdo->prepare('INSERT INTO message (email, firstname, surname, date, message)
VALUES (:email, :firstname, :surname, :date, :message)
');

$date = date('Y-m-d H:i:s');

$values = [
'firstname' => 'Sue',
'surname' => 'Evans',
'email' => 'sue@[Link]',
'date' => $date,
'message' => 'I posted a message!'
];

$stmt->execute($values);

$values = [
'firstname' => 'John',
'surname' => 'Smith',
'email' => 'john@[Link]',
'date' => $date,
'message' => 'I posted another message!'
];

$stmt->execute($values);
 Avoiding duplication

●
This causes duplication. Each time someone posts a
message their information is stored repeatedly
●
If you want to update John's email address you have to
update all the records that contain it!
●
A better way to handle this is septate tables for each
set of information:
– A table for people
– A table for messages
 Avoiding duplication
●
To achieve this you will need some way of linking
between each table so you know who posted which
message
●
This is done using a foreign key
●
A foreign key is a field in the table that stores the value
of a records primary key from another table
●
Note: We’re not using actual foreign keys:
– See: [Link]
[Link]
 Avoiding duplication

●
Firstly, the person table needs to store information
about who posted the message:
– ID (Primary Key, Auto Increment)
– firstname
– Surname
– Email
 Avoiding duplication

●
The message table can then store the information
specific to messages:
– ID (Primary key, auto increment)
– The message text
– The time it was posted
●
As well as:
– UserId (The ID of the user who posted the message)
 Removing duplication

●
The people's information can be added to the person
table
●
And the message information can stay in the message
table with a reference to the relevant user ID in the
person table
Person table
Message table
 Person table

Message table
 Using multiple tables in PHP

●
To use the information in PHP you need two queries, one
for each table
– One to get all of the messages
– One to query the person table to get the information about
the person who posted each message:
 Getting a list of messages

$messageQuery = $pdo->prepare('SELECT * from message');

$messageQuery->execute();
echo '<ul>';
foreach ($messageQuery as $message) {

echo '<li>' .
' <strong>' . $message['message'] . '</strong>' .
' on ' . $message['date']
. '</li>';
}

echo '</ul>';
 Connect to database
$pdo = new PDO('mysql:dbname=csy2028;host:[Link]', 'student', 'student');
Prepare a query for each table
$messageQuery = $pdo->prepare('SELECT * from message');
$userQuery = $pdo->prepare('SELECT * FROM person WHERE id = :id');
Execute the query that returns
all the messages
$messageQuery->execute();
echo '<ul>';
foreach ($messageQuery as $message) { Loop through each record
$values = [ in the message table
'id' => $message['userId']
];
Build the values to search for
$userQuery->execute($values); a user by their id
$user = $userQuery->fetch();
Execute the user query and
echo '<li>' . search for the user with the ID
$user['firstname'] . ' ' . $user['surname'] . from the message table
' posted the message <strong>' . $message['message'] . '</strong>' .
' on ' . $message['date']
. '</li>'; Fetch the information from the
} person table that was retrieved
by the query
echo '</ul>';

Generate HTML using both

the $user record and the
$message record
 Using a query to populate a select

$pdo = new PDO('mysql:dbname=csy2028;host:[Link]', 'student', 'student');

$stmt = $pdo->prepare('SELECT * FROM person');

$stmt->execute();

echo '<select name="person">';

foreach ($stmt as $person) {

echo '<option value="' . $person['id'] . '">' . $person['firstname'] .'</option>';

}

echo '</select>';
 Linking to records

●
A very useful feature is creating a link which goes to a
set of search results. For example, making it so each
user can be clicked on to display their messages

$stmt = $pdo->prepare('SELECT * FROM person');

$stmt->execute();

echo '<ul>';
foreach ($stmt as $person) {

echo '<li><a href=”[Link]?id=' . $person['id'] . '">' . $person['firstname'] .'</a></li>';

}

echo '</ul>';
 Linked to [Link]?id=1

Linked to [Link]?id=2

●
John Linked to [Link]?id=3
●
Sue
●
Dave
●
Sue Linked to [Link]?id=4
 The view messages page

●
[Link] can then use that $_GET variable

$stmt = $pdo->prepare('SELECT * FROM messages WHERE userId = :userId');

$values = [
'userId' => $_GET['id']
];
$stmt->execute($values);

echo '<ul>';
foreach ($stmt as $message) {

echo '<li>' . $message['text'] .'</li>';

}

echo '</ul>';
 Exercise 3
● 1) Create the following tables to store games and the platform they are released on (e.g. PC, Xbox, Playstation)
– Table: game
● id (int, auto-increment)
● name (varchar)
● PlatformID (varchar)
– Table: platform
● Id
● name

2) Create a form for adding platforms to the database

● 3) Create a form for adding games to the database. Use a <select> to display a list of platforms from the database. When adding a
game, select the platform from the list.
● 4) Add at least two platforms and at least 4 games
● 5) Create a page that lists all the available platforms in a list. Each platform should be a link and when clicked on should take you to a
page that displays all the games associated with the given platform. E.g. Clicking “Xbox” should take you to a page that lists all the
Xbox games in the system
– Hint: You do not need separate pages for each platform, try:
● [Link]?platformId=1 to list all Xbox games,
● [Link]?platformId=2 to list all PC games, etc
● 6) Make it so you can click on a game to edit it. The original name should appear in a text box in an edit form!