Transcript Wk7 - Ken Cosh

269200
Web Programming Language
Week 7
Dr. Ken Cosh
PHP and storage
Storing Data
• Last week we built a calendar and displayed an appointment
•
Sadly once you closed your browser, the information was lost
• This week we will look at storing data permanently
•
•
Files
Databases
Files
• We can create and store data in a file using fopen();
•
<?php
$file = fopen("test.txt","r”);
//some code to be executed
fclose($file);
?>
Fopen Modes
Writing to a File
• Using ‘fwrite();’
•
<?php
$file = fopen("test.txt","w");
echo fwrite($file,"Hello World. Testing!");
fclose($file);
?>
Reading from a file
• Using feof() and fgets()
•
<?php
$file = fopen(“test.txt", "r") or exit("Unable to open file!");
while(!feof($file))
{
echo fgets($file). "<br>";
}
fclose($file);
?>
Problems with Files?
• Large file sizes are difficult and slow to work with
• Searching for records within a flat file is not easy
• Concurrent access to files is a problem (although you can lock files)
• Inserting or deleting records from the middle of the file is difficult
•
(random access vs. sequential access)
Access levels to the data is limited (ie. who can delete, add and change
the data)
Databases (MySQL)
• MySQL is a database server
• MySQL is ideal for both small and large applications
• MySQL supports standard SQL
• MySQL compiles on a number of platforms
• MySQL is free to download and use
• The data in MySQL is stored in database objects called tables.
• A table is a collection of related data entries and it consists of columns and
rows.
Check out PHPMYADMIN
• http://localhost/phpmyadmin/
MySQLi Extension
• There are three main API options when considering connecting to a MySQL
database server:
•
•
•
PHP's MySQL Extension
PHP's mysqli Extension
PHP Data Objects (PDO)
Making a connection
•
Procedural Style
$link = mysqli_connect("myhost","myuser","mypassw","mydb") or die("Error " . mysqli_error($link));
//Some Code
mysqli_close($link);
•
OO Style
$mysqli = new mysqli("localhost", “username", "password", “databasename");
// Some Code
$mysqli->close();
Create a Database
$query = “CREATE DATABASE my_db”;
•
•
Procedural Style
if (mysqli_query($link, $query) === TRUE) {
printf("Table myCity successfully created.\n");
}
OO Style
if ($mysqli->query($query) === TRUE) {
printf("Table myCity successfully created.\n");
}
But why not just use phpmyadmin?
Create a Table
• $sql = “CREATE TABLE Persons
(
FirstName varchar(15),
LastName varchar(15),
Age int
)”;
But why not just use phpmyadmin?
Primary Keys & Auto Increment
• $sql = “CREATE TABLE Persons
(
personID int NOT NULL AUTO_INCREMENT,
PRIMARY KEY(personID),
FirstName varchar(15),
LastName varchar(15),
Age int
)”;
Insert
• $query = “INSERT INTO Persons (FirstName, LastName, Age)
VALUES ('Peter', 'Griffin',35)”;
$query = “INSERT INTO Persons (FirstName, LastName, Age)
VALUES ('Glenn', 'Quagmire',33)”;
Select (Procedural)
if ($result = mysqli_query($link, "SELECT Name FROM City LIMIT 10")) {
printf("Select returned %d rows.\n", mysqli_num_rows($result));
mysqli_free_result($result);
}
Select (OO)
• if ($result = $mysqli->query("SELECT Name FROM City LIMIT 10")) {
printf("Select returned %d rows.\n", $result->num_rows);
$result->close();
}
Select Result – Associative Array
<?php
//Open a new connection to the MySQL server
$mysqli = new mysqli('host','username','password','database_name');
//Output any connection error
if ($mysqli->connect_error) {
die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error);
}
//MySqli Select Query
$results = $mysqli->query("SELECT id, product_code, product_desc, price FROM
products");
print '<table border="1">';
while($row = $results->fetch_assoc()) {
print '<tr>';
print '<td>'.$row["id"].'</td>';
print '<td>'.$row["product_code"].'</td>';
print '<td>'.$row["product_name"].'</td>';
print '<td>'.$row["product_desc"].'</td>';
print '<td>'.$row["price"].'</td>';
print '</tr>';
}
print '</table>';
// Frees the memory associated with a result
$results->free();
// close connection
$mysqli->close();
?>
Select Result – Array
<?php
//Open a new connection to the MySQL server
$mysqli = new mysqli('host','username','password','database_name');
//Output any connection error
if ($mysqli->connect_error) {
die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error);
}
//MySqli Select Query
$results = $mysqli->query("SELECT id, product_code, product_desc, price FROM
products");
print '<table border="1"';
while($row = $results->fetch_array()) {
print '<tr>';
print '<td>'.$row["id"].'</td>';
print '<td>'.$row["product_code"].'</td>';
print '<td>'.$row["product_name"].'</td>';
print '<td>'.$row["product_desc"].'</td>';
print '<td>'.$row["price"].'</td>';
print '</tr>';
}
print '</table>';
// Frees the memory associated with a result
$results->free();
// close connection
$mysqli->close();
?>
•
Select
Result
–
Object
<?php
//Open a new connection to the MySQL server
$mysqli = new mysqli('host','username','password','database_name');
//Output any connection error
if ($mysqli->connect_error) {
die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error);
}
//MySqli Select Query
$results = $mysqli->query("SELECT id, product_code, product_desc, price FROM
products");
print '<table border="1">';
while($row = $results->fetch_object()) {
print '<tr>';
print '<td>'.$row->id.'</td>';
print '<td>'.$row->product_code.'</td>';
print '<td>'.$row->product_name.'</td>';
print '<td>'.$row->product_desc.'</td>';
print '<td>'.$row->price.'</td>';
print '</tr>';
}
print '</table>';
// close connection
$mysqli->close();
?>
WHERE
$qry = “SELECT * FROM Persons WHERE FirstName='Peter’”;
$qry = “SELECT * FROM Persons WHERE FirstName=‘$name’”;
ORDER BY
$query = “SELECT * FROM Persons ORDER BY age”;
UPDATE
$query = “UPDATE Persons SET Age=36 WHERE FirstName='Peter' AND
LastName='Griffin‘”;
DELETE
• $query = “DELETE FROM Persons WHERE LastName='Griffin‘”;
Further Reading
• SQL Injections
•
http://www.unixwiz.net/techtips/sql-injection.html
Assignment
• Extend the Appointment Calendar from last week.
• Allow users to store appointments and display them on the calendar.
Add Appointment
Title
Date
Detail
FoE Mtg
13/12/2011
ASEAN-QA meeting, review
the handbook first.
Submit 2
View Calendar
Assignment
2
Add Appointment
View Calendar
December 2011
Foe Mtg in 1 day(s)
3
Assignment
3
Add Appointment
Appointment Detail
Title
Date
Detail
FoE Mtg
13/12/2011
ASEAN-QA meeting, review
the handbook first.
View Calendar