Transcript This is actual PHP+MySQL Integration

COM621 – Lecture 5
MySQL and PHP Integration
PHP - MySQL
• Commercial Sites:
– Apache
– PHP
– MySQL
– XAMP (WAMP – MAMP)
• Required Tools
– Text Editor or a Program like Dreamweaver
Installing XAMP
• If you are working from your laptop, it is time to
download and install XAMP:
– Windows: Download and Install WAMP Server
– MAC: Download and Install MAMP
– Leave all the settings as default except the browser
(change to your preferred browser: I recommend
Chrome, Firefox or Safari)
– Once Installed, your files for this module need to be
stored in the following folder (also applies to the lab
machines: c:/wamp/www
– Task: Create a folder with your name inside this folder
Stopping IIS
• Next step (only if
you are working in
the lab) – you
need to stop IIS –
go to:
– CONTROL PANEL

ADMINISTRATIVE
TOOLS  Internet
Information
Service (IIS)
Manager 
Stopping IIS
– At the top
right hand
side of the
screen click
STOP
Configuring XAMP
• Now is time to Start WAMP Server:
– On the desktop click start wampserver; a red “w”
icon should appear in your taskbar, it should go
from red, to orange to green – once it is green we
are ready to go
Starting WAMP
Start WampServer
TaskBar – Red “W”
Starting WAMP
• Right click on the icon and
select: localhost (this should
open your browser with the
WAMP welcome page)
WAMP in Browser
Connecting to databases
• One of the most common tasks when working
with dynamic webpages is connecting to a
database which holds the content of the page
• PHP has several libraries that allows for
communication with many different databases
• Some of these libraries have variations in the
commands
• We will be using the mysql library
• Most functions start with mysql_ and then the
name of the function
10
Opening a connection
• You will need:
– The address to the database server
– A username with privileges to access the table you
require
– The username associated password
• Optionally:
– The name of the database you are connecting
11
Connection Sequence
• mysql_connect(host, username, password):
returns a link to the host using username and
password authentication.
• Usage:
– $conn=mysql_connect(‘localhost’, ‘myusername’,’MyP455w0rd’)
• mysql_select_db(database,link): selects a database
from the connection in link
• Usage:
– mysql_select_db(‘db_name’,$conn);
12
Executing Queries
• mysql_query(query,link) is used to run a query on
the database server. Required the link and the query
string. Returns a result object.
• Usage:
– $result= mysql_query(“SELECT * from directory”, $conn);
• Queries can be constructed as strings and then the
string variable can be used on the mysql_query
command:
– $query="select * from directory";
– $result = mysql_query($query,$link) or die("could not
execute:".mysql_error());
13
Working with the results
from the query
• Result sets are objects. They point to places in
memory where the query returned values exist
• Result set are NOT the individual rows from the query
• You can think of a result set as the TABLE that holds the
results
• You need to read the rows from that table individually
– $row=mysql_fetch_array($result);
• Using this function, the array returned can be either
numerically indexed or associative!
• If there are no more rows, the function returns FALSE
14
Keeping it tidy
• Once you have completed your work with the
database, there are two things you should do:
– Free the results
– Close the connection to the server.
• Freeing the results can be optional: On closing the
connection, the results are automatically freed
• If you are planning to run further queries on the same
connection, it is good practice to free the previous
result set.
– mysql_free_result($result);
• To close the connection you use
– mysql_close($conn);
15
Working with databases –
key steps
• Design the DB – Create the DB on PHPMyAdmin
or MySQL command
• Design and Create the HTML
• Create a connection
• Select the table
• Run the query
• Verify Insert/Fetch the rows/Do other SQL
associated tasks
• Display Results as needed
• Close the connection
16
MySQL – Data Manipulation
Language
SQL Data Manipulation
Language (DML)
• SQL is a non procedural language providing
syntax for extracting data, including a syntax
to update, insert, and delete records.
• The Query and Update commands together
form the Data Manipulation (DML) part of SQL
– SELECT
– UPDATE
– DELETE
– INSERT INTO
SQL-DML
SELECT
• SELECT: This command is mandatory when
performing a query; it is used to retrieve data
from a table based on some criteria
• It specifies a coma separated list of fields to be
retrieved, and the FROM clause specifies the
table(s) to be accessed.
• The results are stored in a result table known as
the result-set.
• The * symbol can be used to represent all of the
fields
SQL-DML
SELECT
Format:
SELECT column_name(s) FROM table_name
Example:
SELECT LastName, FirstName, Address FROM Students;
To select specified columns, the SELECT command
is followed by a comma separated list of fields to
be selected from the table
SQL-DML
SELECT
• SELECT DISTINC: this keyword is used to return
only distinct (unique) values from the table.
• If there are multiple values of a specified field,
the distinct result-set will display only one.
Format:
SELECT DISTINCT column_name(s) FROM table_name
Example:
SELECT DISTINCT ShipName FROM Orders;
SQL-DML
SELECT
• LIMIT(X): this keyword specifies the number of
rows to be returned from the beginning of the
result-set. X specifies the rows returned
Format:
SELECT column_name(s) FROM table_name LIMIT X;
Example:
SELECT ShipName FROM Orders LIMIT 10;
SQL-DML
SELECT
• WHERE Clause: It is used to select a field when
a certain criteria set of conditions are desired
• The WHERE Clause is optional
• To create the conditions (called selection
criteria) SQL provides a set of operators to
further qualify what criteria should be
specified
SQL-DML
Where Operators
Operator
Description
Example
=
Equal to
WHERE country = ‘ireland’
<>, !=
Not equal to
WHERE country != ‘USA’
>
Greater than
WHERE salary > 28000
<
Less than
WHERE age < 35
>=, <=
Greater/Less Than or Equal
WHERE cost >=1200
IS [NOT] NULL
Is NULL (vo value) or Not NULL
WHERE birth = NULL
BETWEEN
Between an inclusive range
WHERE last_name BETWEEN ‘Doherty’ AND ‘McDAID’
LIKE
Search for a value like a pattern
WHERE name LIKE ‘D%’
NOT LIKE
Search for a value not like a pattern
WHERE country NOT LIKE ‘Sw%’
! , NOT
Logical not for negation
WHERE age ! 10;
||, OR
Logical OR
WHERE order_number > 10 || part_number = 80
&&, AND
Logical AND
WHERE age>12 && age < 21
XOR
Exclusive OR
WHERE status XOR
SQL-DML
• Using Quotes: Quotes are always an issue in
programming languages. (single quotes?,
double quotes?, when?)
• SQL uses single quotes around text values
(MySQL also accepts double quotes)
• Numeric Values should not be enclosed in
quotes.
SQL-DML
• Comparing Strings: When comparing strings
using =, the string must be exactly as typed for
the condition to be true – this include length
and type of characters.
• NULL: Null means that there is not a value in
the field, or it is unknown, but does not mean
a value of zero.
DML-SQL
• LIKE – NOT LIKE: The pattern matching operator
can be used as a condition in the WHERE clause,
allowing the selection of rows that are ‘like’ or
match a pattern
• A percent sign (%) can be used as a wildcard to
match any possible character that might appear
before and/or after the character(s) specified.
• A _ is used to match a single character.
• The LIKE/NOT LIKE condition can be used in any
valid SQL statement, including SELECT, INSERT,
UPDATE or DELETE.
DML-SQL
• Examples of the wildcard % uses:
• SELECT CompanyName, Country FROM Customers WHERE
country LIKE ‘SW%’;
– Returns all the customers and countries in which the country starts
with “Sw” i.e. Sweden, Switzerland
• SELECT City, Country FROM suppliers WHERE City LIKE ‘%o’;
– Returns all cities and countries where the % matches any city that
ends with a letter o.
• SELECT CompanyName FROM customers WHERE
CompanyName LIKE ‘%Super%’
– Returns all company names where the % matches any company name
that contains the pattern “Super”
DML-SQL
• Examples of the wildcard _ uses:
• SELECT Extension, Firstname FROM Employees WHERE
extension LIKE ‘4_ _’;
– Returns all extensions and first names where the exetension has three
characters and the first character is a 4.
DML-SQL
• ORDER BY: Used to sort the output of a query
in either ascending (ASC, the default) or
descending (DESC) order where the values
being sorted are either strings or numbers
Format:
SELECT column_name(s) FROM table_name [WHERE condition] ORDER BY
column [ASC, DESC]
Example:
SELECT Company,Ordernumber FROM Orders ORDER BY Company;
SQL-DML
INSERT
• The INSERT statement is used ot insert new
rows into a table.
• After the VALUES keyword, a commaseparated list of column names follows
Format:
INSERT INTO table_name VALUES (value1, value2, … )
INSERT INTO table_name (column1, column2,…) VALUES (value1, value2, … )
Example:
INSERT INTO Shippers (CompanyName, Phone) VALUES (‘FEDEX’,’416-5551221’);
SQL-DML
INSERT
• Usually, the tables have a primary key column
that is usually set to auto-increment; when
this is the case, the id of the table is created
by the database engine automatically
• Letting the database increment the PRIMARY
KEY ensures that the value is always unique.
SQL-DML
UPDATE
• The UPDATE statement is used to modify data in a
table.
• The UPDATE command is followed by the name
of the table where the data will be changed,
followed by the SET statement to inidcate what
field will be changed, and then the new value
that will be assigned to the field
• The WHERE clause further qualifies what data is
to be modified, thereby limiting the scope of the
UPDATE
SQL-DML
UPDATE
Format:
UPDATE table_name
SET column_name = new value
WHERE column_name = some_value;
Example:
UPDATE orders SET ShipCountry=‘Spain’ WHERE CustomerId = ‘whitc’;
SQL-DML
DELETE
• The DELETE statement is used to delete rows in a
table and returns the number of rows that were
deleted.
• DELETE uses the FROM clause to specify the
name of the table that contains the data you
want to delete
• The WHERE clause specifies the criteria to
identify what data should be removed.
BE CAREFUL: Without the WHERE clause ALL ROWS
are DELETED
SQL-DML
DELETE
• If the ORDER BY clause is specified, the rows
are deleted in the order that is specified.
• The LIMIT clause places a limit on the number
of rows that can be deleted.
Format:
DELETE FROM table_name
WHERE column_name = some_value;
Example:
DELETE FROM orders WHERE ShipCountry = ‘Greenland’;
PHPMyAdmin Tutorial
LIVE
Database Design
and Creation
Create database ‘hospital’, add a table ‘discharge’
with appropriate fields – patient id, name,
appointment type, admission ward, xray
examination, mri scan, cat scan, eye test, hearing
test, and date (to capture todays date)
hospital
discharge(patid,name,appointment,ward,xray,mri,c
scan,eye,hearing,date)
Data details
Field
Type
Comment
patid
int(11) NN AI PK
Primary Key Index
name
varchar(45)
Patient Full Name
appointment
varchar(11)
type of appointment:
GP, Specialist or Surgery
ward
char(2)
Admitted to ward? No, or ward A,B or C
xray
char(3)
x-rays taken? YES,NO
mri
char(3)
mri carried out? YES,NO
cscan
char(3)
CAT scan carried out? YES,NO
eye
char(3)
eye test carried out? YES,NO
hearing
char(3)
hearing test carried out? YES,NO
date
date
date of discharge: format YYYY-MM-DD as text
• Go ahead and create the database and table
using PHPMyAdmin or MySQL command
Prompt
Data capture form
link to php script file
use get to check form is working correct
post could also be used, user choice
<form action="process.php" method="get" name="discharge">
table name
Patient Name: <input type="text" name="name" /><br />
<hr />
Appointment type<br />
<select name="appointment">
<option value="GP">General Practicioner</option><br />
<option value="Specialist">Specialist</option><br />
<option value="Surgery">Surgery</option><br />
</select>
3 options for app type
Radio button
Admission to Wards:<br />
<label>
<input type="radio" name="ward" value="No" />
No Ward</label>
<br />
<label>
<input type="radio" name="ward" value="A" />
Ward A</label>
<br />
<label>
<input type="radio" name="ward" value="B" />
Ward B</label>
<br />
<label>
<input type="radio" name="ward" value="C" />
Ward C</label>
<br />
<hr />
4 options via radio
buttons, only one can
be selected on form
Checkboxes
Treatments Undertaken:<br />
<input type="checkbox" name="xray" value="yes" />X-Ray Treatment<br />
<input type="checkbox" name="mri" value="yes" />MRI Scan<br />
<input type="checkbox" name="cscan" value="yes" />CAT Scan<br />
<input type="checkbox" name="eye" value="yes" />Eye Test<br />
<input type="checkbox" name="hearing" value="yes" />Hearing Test<br />
<hr />
5 options, any can be selected on form
Submit/reset button
<input type="submit" value="Submit Form" />
<input type="reset" value="Reset Form" />
</form>
PHP-process.php
Open a new file, save as process.php, keep on same directory
as html file
<?php
$id = $_GET['patid'];
$name = $_GET['name'];
$app = $_GET['appointment'];
$ward = $_GET['ward'];
$string
single option data passing
$_POST option also viable, keep consistent with
previous use
Checkbox data
selection
checkboxes allow multiple options
if not checked, it will not pass
the parameter (not set)
if (isset($_GET['xray'])) {
$xray = $_GET['xray'];
} else {
$xray = "No";
}
if passed the default is set to ‘yes’
so if not passed, we need to assign
a value to the respective variable that
will go into the database
Note: This code needs to be repeated for every checkbox variable passed as a parameter
the variables used are:
$mri, $cscan, $eye, $hearing
print "Name:".$name."<br />";
print "Appointment:".$app."<br />";
print "Ward:".$ward."<br />";
print "X-Ray:".$xray."<br />";
print "MRI:".$mri."<br />";
print "CSCAN:".$cscan."<br />";
print "EYE:".$eye."<br />";
print "HEARING:".$hearing."<br />";
print used to check fields are being
passed and captured in the page
Database connection
$link = mysql_connect('localhost','student','student') or die(mysql_error());
mysql_select_db('hospital',$link);
Current date selection
This script demonstrates how the current date
can be obtained from the MySQL server using
the appropriate DATE query
$querydate = "SELECT CURDATE() as Today";
$result1 = mysql_query($querydate,$link) or die(mysql_error());
$row = mysql_fetch_assoc($result1);
$today = $row['Today'];
The parameter being queried (date)
does not have an index in the array,
so we need to use the alias “AS” to assign
an index that we can use to retrieve from
the result array
INSERT data
Query to insert all data from html form + date obtained from
previous query
$query = "INSERT INTO discharge VALUES
(NULL,'$name','$app','$ward','$xray','$mri','$cscan','$eye','$hearing','$today')";
$result = mysql_query($query,$link) or die(mysql_error());
$affected = mysql_affected_rows($link);
if ($affected >0) {echo "success";} else {echo "fail";}
detects affected rows in the query
in this case 1 row was INSERTED so
$affected=1 if the insert was successful
generates my-sql error causes
Selection to display all
This is another query to retrieve all the data
from the table discharge
$query3 = "SELECT * FROM discharge";
$result3 = mysql_query($query3,$link) or die(mysql_error());
$row3 = mysql_fetch_assoc($result3);
?>
Table design for results
<table width="200" border="1">
<tr>
<th scope="col">Patient ID</th>
<th scope="col">Patient Name</th>
<th scope="col">Appointment Type</th>
<th scope="col">Admitted to Ward</th>
<th scope="col">X-Ray</th>
<th scope="col">MRI</th>
<th scope="col">Cat Scan</th>
<th scope="col">Eye Test</th>
<th scope="col">Hearing Test</th>
<th scope="col">Date Discharged</th>
</tr>
Results
$row is an associative array containing the results,
the index of the array are the fields in the database
<tr>
<td><?php echo $row3['patid']; ?></td>
<td><?php echo $row3['name']; ?></td>
<td><?php echo $row3['appointment']; ?></td>
<td><?php echo $row3['ward']; ?></td>
<td><?php echo $row3['xray']; ?></td>
<td><?php echo $row3['mri']; ?></td>
<td><?php echo $row3['cscan']; ?></td>
<td><?php echo $row3['eye']; ?></td>
<td><?php echo $row3['hearing']; ?></td>
<td><?php echo $row3['date']; ?></td>
</tr>
</table>
do …. while loop
<?php do { ?>
<tr>
<td><?php echo $row3['patid']; ?></td>
<td><?php echo $row3['name']; ?></td>
<td><?php echo $row3['appointment']; ?></td>
<td><?php echo $row3['ward']; ?></td>
<td><?php echo $row3['xray']; ?></td>
<td><?php echo $row3['mri']; ?></td>
<td><?php echo $row3['cscan']; ?></td>
<td><?php echo $row3['eye']; ?></td>
<td><?php echo $row3['hearing']; ?></td>
<td><?php echo $row3['date']; ?></td>
</tr>
<?php } while ($row3=mysql_fetch_assoc($result3)); ?>
</table>
DO-WHILE Loop
needed to display
all the results of the database
Close connection
<?php
mysql_close($link);
?>
Deleting Records
• HTML Button to call delete statements:
<form action="delete.php" method="get">
<label>Patient ID:<input type="text" name="patid" /></label>
<input type="submit" value="Delete Record" />
</form>
• Query to Delete Data from the Database:
<?php
$patid=$_REQUEST['patid'];
$query = "DELETE from discharge WHERE patid=$patid";
$result = mysql_query($query,$link) or die(mysql_error());
$affected = mysql_affected_rows($link);
Obtaining patid from
form
if ($affected >0) {echo "success";} else {echo "fail";}
User Feedback
?>
Remember to add the connection lines
using $link to connect to the db
Places where
things can go wrong
• Creating a connection
– Server unreachable/offline
– Wrong username/password combination
• Selecting the table
– Table does not exist
– User without privileges for that table
• Running the query
– Syntax errors
– Empty result set
• Fetching results
– Misusing the array
57