nmd202 web scripting week5. what we will cover today php & mysql displaying dynamic pages...
TRANSCRIPT
NMD202 Web Scripting
Week5
What we will cover today
PHP & MySQL Displaying Dynamic Pages Exercises Modifying Data PHP Exercises Assignment 1
Displaying Dynamic Pages
A database driven website allows the content of the site to live in a database
Web Browser
Web Server
PHPMySQL
Request
Response
Request Data
Provides Recordset
Displaying Dynamic Pages
PHP has a client built into the Language.
You can run all the operation available in MySQL from within PHP
Displaying Dynamic Pages
Connect to the Database:
$conn = mysql_connect(address,username,password);
$conn will hold a connection identifier after the operation is successful or false if connection fails
Displaying Dynamic Pages
Select the Database:
mysql_select_db(databaseName,$conn);
$conn is the connection identifier provided by the connection statement
Displaying Dynamic Pages
Query the Database:
$result = mysql_query(query,$conn);
$conn is the connection identifier provided by the connection statement
$result will hold the result of the query, or false if query fails
Displaying Dynamic Pages
Handling errors
Whenever your code relies on third party systems (ie:MySQL) you should always handle unforseen errors.
$conn = mysql_connect(address,username,password);
If (!$conn)
{
echo “<p>Could not connect</p>”;
die();
}
Displaying Dynamic Pages
Handling errors
If (! mysql_select_db($databaseName,$conn)
{
echo “<p>Could not locate”.$ databaseName.”</p>”;
die();
}
Displaying Dynamic Pages
Handling errors
$result = mysql_query(query,$conn);
If (!$result)
{
die (“<p>Error performing query:”.mysql_error().”</p>”);
}
Displaying Dynamic Pages
Handling Resultsets
$result = mysql_query(query,$conn);
If (!$result)
{
die (“<p>Error performing query:”.mysql_error().”</p>”);
}
while ($row = mysql_fetch_array($result))
{
// $row will hold an assoc array with the columns names as keys
echo $row[“studentName”];
}
Displaying Dynamic Pages
Keep config information separate:
configuration.php
$mySQLhost = “127.0.0.1”;
$userName = “root”;
$password = “”;
$database = databaseName;
Include configuration.php from your main file, if using inside a function don’t forget to include the keyword global
Exercise
Display all students loaded in previous class as a table with all the attributes (name, email, etc)
Modifying Data
Inserting data (example):
• Form is submitted• Validation is performed• Query is built based on submitted data (data should be sanitized)• Query is performed• Feedback is given to the user
Modifying Data
Editing Data (example):
• Id record to edit is passed through the querystring • Select query is executed to get the required record • Form is built pre populating fields with record info• Form is submitted• Validation is performed• Query is built based on submitted data (data should be sanitized)• Query is performed• Feedback is given to the user
Modifying Data
Deleting Data (example):
• Id record to delete is passed through the querystring • Query is built based on id (data should be sanitized)• Query is performed• Feedback is given to the user
Exercise
Build a complete set of screens to insert, update, display and delete the students records.
On the display table insert two more columns with links to the delete and edit pages, providing the id of the record.
Split the logical parts into separate php files: display.php, edit.php, insert.php, delete.php, configuration.php
Assignment 1
Project Brief
ER Diagram