Transcript Lec6 PHP and MySQL

PHP+MySQL Integration
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
2
Working with databases –key steps
1. Design the DB
– Create the DB on PHPMyAdmin or MySQL
command
2. Design and create the HTML
3. Create a connection and select the database
4. Run the query
– Verify Insert/Fetch the rows/Do other SQL
associated tasks
5. Display Results as needed
6. Close the connection
3
1. 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, cscan, 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
2. Design and Create the HTML
In order to interact with the database we need a
data capture form, and a script that will
process the form.
Create an HTML document in the folder you
created in C:\wamp\www, and save it as
hospital.html
hospital.html
<form action="process.php" method="get" name="discharge">
Patient name: <input name="name" type="text" /><br/>
<hr/>
Appointment type: <select name="appointment">
<option value="General Practitioner">GP</option>
<option value="Specialist">Specialist</option>
<option value="Surgery">Surgery</option>
</select>
<hr/>
Admission to these Wards:<br/>
<label> <input type="radio" name="ward" value="No" id="ward_0" /> No Ward</label> <br />
<label> <input type="radio" name="ward" value="A" id="ward_1" /> Ward A</label> <br />
<label> <input type="radio" name="ward" value="B" id="ward_2" /> Ward B</label> <br />
<label> <input type="radio" name="ward" value="C" id="ward_3" /> Ward C</label> <br />
<hr/>
Treatments Undertaken:<br/>
<input name="xray" type="checkbox" value="yes" />X-Ray Treatment<br/>
<input name="mri" type="checkbox" value="yes" />MRI Scan<br/>
<input name="cscan" type="checkbox" value="yes" />CAT Scan<br/>
<input name="eye" type="checkbox" value="yes" />Eye Test<br/>
<input name="hearing" type="checkbox" value="yes" />Hearing Test<br/>
<hr/>
<input name="" type="submit" value="Submit Form" />
<input name="" type="reset" value="Reset Form" />
</form>
Form
link to php script file
use get to check form is working correctly
post could also be used, user choice
<form action="process.php" method="get" name="discharge">
form name
Drop down list
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 appointment type
Radio buttons
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 buttons
<input type="submit" value="Submit Form" />
<input type="reset" value="Reset Form" />
</form>
process.php
Open a new file, save as process.php, keep on same directory
as html file
<?php
$name = $_GET['name'];
$app = $_GET['appointment'];
$ward = $_GET['ward'];
single option data passing
$_POST option also viable, keep consistent with
previous use
$variable
Arguments are passed as below:
http://localhost/process.php?name=Joe+Bloggs&appointment=General+Practitioner&ward=A
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
Check data capture
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
3. Database 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 to
16
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);
17
Opening a 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
4. Run the query
mysql_query(link, query) is used to run a query on the
database server. It requires the link and the query
string and returns a result object.
Usage:
$result= mysql_query(“SELECT * from directory”, $conn);
Queries can be constructed as strings. The string
variable can then be used on the mysql_query
command.
Usage:
$query="select * from directory";
$result = mysql_query($query,$link) or die("could not
execute:".mysql_error());
Insert query
Query to insert all data from html form + date obtained from
previous query
Specify discharge table
$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 mysql error causes
Select query
This query will 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);
?>
5. Display results
•Result sets are objects. They point to places in memory
where the values returned from the query exist
•Result sets 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
Table headings
<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 is the field 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
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);
27
6. Close connection
<?php
mysql_close($link);
?>
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
29