1 / 40

F27DB Introduction to Database Systems Accessing MySQL database via PHP - 1

F27DB Introduction to Database Systems Accessing MySQL database via PHP - 1. Helen Hastie h.hastie@hw.ac.uk Room: EMB244 Material available on Vision (modified from slides by Monica Farrow). Recap - Using PHP to process form data and reply. CLIENT. SERVER. XHTML containing form.

barnabas
Download Presentation

F27DB Introduction to Database Systems Accessing MySQL database via PHP - 1

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. F27DB Introduction to Database SystemsAccessing MySQL database via PHP - 1 Helen Hastie h.hastie@hw.ac.uk Room: EMB244 Material available on Vision (modified from slides by Monica Farrow) Intro PHP & MySQL

  2. Recap - Using PHP to process form data and reply CLIENT SERVER XHTML containing form HTML PAGE lawn_length = 2.1 lawn_width = 10 PHP SCRIPT • receives form data • processes it • outputs HTML XHTML Intro PHP & MySQL

  3. Recap - PHP script <html> <head> <title>Displaying the lawn area</title> </head> <body> <p> The area is <?PHP $width = $_POST["lawn_width"]; $length = $_POST["lawn_length"]; print $width*$length; ?> Sq m. </p> </body> </html> Intro PHP & MySQL

  4. Spy Website design • Pages in green with italics need to know about username and password • Login stores the username and password • The response pagesquery or update the database • The ‘enter’ pagereads the DBto get valuesfor pull-downlists Login Choices RequestEnter new spy (enter details) Requestupdate spy (enter details) Request spy details (enter code) Response:Spy details displayed Response:Add confirmed Response:Changes confirmed Intro PHP & MySQL

  5. Remembering data • One big problem is that after the response to a request is sent, HTTP closes the connection • Each request is treated as an independent connection, with no relationship to any preceding requests/responses • However, when a user visits a website, there are various reasons for wanting to remember about this user throughout their visit • In our case, remembering a username and password entered at the start to allow connection to the mysql database in any page Intro PHP & MySQL

  6. Sessions • A session is ‘The sequence of HTTP transactions generated when a user visits a website and browses through the pages of that site. The session terminates when the user leaves the site.’ • A session could also be referred to as a visit Intro PHP & MySQL

  7. Session Management - theory • Commonly a middleware language such as PHP will provide methods for storing session data on the server • It does this by allocating each individual session a unique session ID, which is sent between responses and requests. • The session ID is stored in your computer’s memory and not written to your hard drive. • It is only stored for the duration of your session, and it is automatically deleted after you leave the domain. • This ID is used on the server side to identify data stored for that session Intro PHP & MySQL

  8. Sessions in PHP – example SERVER CLIENT XHTML containing form HTML PAGE lawn_length = 3 lawn_width = 4 PHP SCRIPT • receives form data • processes it • outputs HTML XHTML & Session ID Session variable ID, length Session ID PHP SCRIPT • receives form data • processes it • outputs HTML XHTML Intro PHP & MySQL

  9. Session ID on the server-side - theory • Anything that needs to kept for the duration of the session is stored in session variables • These session variables are associated with the ID for that session, so they can be retrieved • E.g. • Username and password • Parts of an order • Which adverts have been seen Intro PHP & MySQL

  10. End of a session - theory • All the data for a particular session is destroyed at the end of the session • Either when the user logs out • Or when the php script has been written to explicitly destroy the session using session_destroy() • Or when the session ‘times out’ : possibly 15 minutes with no activity Intro PHP & MySQL

  11. Sessions in PHP - practice • PHP provides functions to manage sessions • We use the these functions • Behind the scenes, a session ID is associated with each session. The user doesn’t need to know about it. • You will need to use this php function: • session_start(); • It’s very important to put the call to this function near the start of every script that uses session variables, before you output any html at all. • You need to store any data that you want to keep for the whole session in the $_SESSION associative array • i.e. username and password Intro PHP & MySQL

  12. Session variables - practice • Use the associative array $_SESSION to store and retrieve data (similar to the $_POST array but make up your own keynames) • Elements identified by key/value pairs • You can add many elements to this array by specifying the key and the value • E.g. Adding a session variable in one script $_SESSION[‘password’]=$_POST[‘password’]; • E.g. Retrieving a session variable in a later script $password = $_SESSION[‘password’]; Intro PHP & MySQL

  13. Summary • Sessions are visits to a website • PHP provides functions for managing sessions • Session variables are used to store and retrieve session data • A session ends when the user moves away from the site, or it can time-out Intro PHP & MySQL

  14. Login page • This page is an html form • There is a special input type for passwords • <input type = "password" name = "password" /> • After submit, the username and password are sent to a php script Intro PHP & MySQL

  15. Receiving the username and password • The receiving php script starts the session then stores the username and password into session variables • The rest of the page is output in html <?php //display all errors error_reporting(E_ALL); ini_set('display_errors', 1); //start session session_start(); //store username and password for later $_SESSION['username'] = $_POST['username']; $_SESSION['password'] = $_POST['password']; ?> <!DOCTYPE html . . . Lots of html Intro PHP & MySQL

  16. Requesting to View a spy • Links to other action(s) • Enter codename of spy you want to see the details of • This is an HTML form • On submit, the codename is sent to a php script. Intro PHP & MySQL

  17. Body of the html – the request <p> <a href = "SpyInput.php" > Add a spy</a> </p> <h1>view a spy</h1> <p> <br/> <form method = "post" action = "DisplayOneSpy.php" > Enter codename of the spy you wish to know about: <input type = "text" size = "10" name = "codename" /> <input type = "submit" value = "submit"/> </form> </p> Intro PHP & MySQL

  18. Display spy - response • Links to other actions • One table for the Spy data • Another for the skills • To keep things simple,I am using the columnname from the databaseas headings • Not ideal! Intro PHP & MySQL

  19. Finding the spy details – the response • The PHP script generating the response must • Ask for errors, start the session • Output the initial html • Retrieve username and password from session variables and connect to the database • Get hold of the Spy codename from the form parameters • $codename = $_POST[“codename”]; • Search the database for the spy with that codename • Output an html table showing column headings and the data found • Search the database for the spy’s skills • Output an html table showing column heading and the skills found • Output the rest of the html. Intro PHP & MySQL

  20. Body of spy display page Most of this page stays the same for all spies However, data in red italics comes from the databaseand (except for column names) is different for each spy. <body> <h1> Spies </h1> . <p> <ahref = "SpyInput.html"> Add a spy </a> &nbsp;&nbsp; <ahref = "SpyPayment.html" > Update Spy Payment </a>&nbsp;&nbsp; <ahref = "ViewOneSpy.html"> View one spy </a> </p> <h2> View a Spy </h2> <h3> Spy with codename =bud</h3> <tableborder = "1"> <tr><td><em>codeName</em></td> <td>bud</td></tr> <tr><td><em>firstName</em></td> <td>Fanny</td></tr> Etc...... Intro PHP & MySQL

  21. DisplayOneSpy.php - 1 • The php script • Starts with 2 lines asking for errors to be reported • Continues by naming another php file whose contents we want to include • I have put all the database interaction into a separate php file consisting of useful db functions. • This simplifies the code in the main script and avoids repetition <?php error_reporting(E_ALL); ini_set('display_errors', 1); include ("dbfunctions.php"); Intro PHP & MySQL

  22. DisplayOneSpy.php - 2 • The php script • Continues by starting the session • This MUST come before any html at all is output //start session session_start(); Intro PHP & MySQL

  23. DisplayOneSpy.php - 3 • The php script • Continues by outputting all the start html • End PHP tag, then some html, then start php tag ?> <!DOCTYPE html . . . A lot of html. . . <h2>View a Spy</h2> <?php Intro PHP & MySQL

  24. DisplayOneSpy.php - 4 • The php script • Continues by retrieving the username and password from the session variables //retrieve username and password $username = $_SESSION['username']; $password = $_SESSION['password']; Intro PHP & MySQL

  25. PHP and database • PHP provides database functions such as • Connect to the database management system • Select the database • Run a query • Look at the results • Was the insert/ delete/update successful? • How many rows were returned from a SELECT query? • What are the contents of these rows? • Provide error information if it didn’t work • PHP provides these functions for many different databases. • The ones for MySQL start with ‘mysql’ Intro PHP & MySQL

  26. Connecting to mysql 1 • For every php command using mysql, we need to run it, and then check that it worked • If it failed, need to print a useful message and then finish the html off tidily before stopping //use mysql function to connect $dbConn = mysql_pconnect ("anubis", $username, $password) ; if (!$dbConn ) //if it didn’t work { //print error message and end html, exit script print "<p>Cannot connect to database - check username and password<br/>"; print mysql_error()."</p>"; print "</body>"; print "</html>"; exit(); } This is part of the dbfunctions.php file Intro PHP & MySQL

  27. If you can’t connect to the database • If the script can’t connect to the database, • there is either something wrong with your code • or something seriously wrong with the database connection • When you are developing your code, you want to see details of errors, because the problem is probably with your code • My error message on the last slide gives this info. • When your application is ‘live’, you should change it to output a nice message to the user • E.g. ‘There is a problem with the database connection. Please try again later’ Intro PHP & MySQL

  28. The dbfunctions.php file • This file contains my own functions to run each of the mysql database functions, and also to automatically display a table • This makes the code in the main script easier to read • It means I can use these functions in any of my pages, and only write the scripts once • It means that beginning programmers can use my example php scripts, and not change much. E.g.: • the SQL queries • the parameters from the form, which are needed for the SQL query Intro PHP & MySQL

  29. Connecting to the database • The DisplayOneSpy.php script continues, using the following functions in the dbfunctions file • dbConnect, dbSelect • runQuery (next page) //connect to mysql //using retrieved username and password //(see slide 24) dbConnect("$username", "$password") ; //select your own database dbSelect("$username"); Intro PHP & MySQL

  30. DisplayOneSpy.php Creating the query • I suggest that you write the query and run it in MySQl first, to make sure it works • Then include it in your PHP script as shown below, using a form parameter in the WHERE clause • MAKE SURE YOU INCLUDE A SPACE BETWEEN LINES i.e. ‘Spy WHERE’ not ‘SpyWHERE’ //create query $codename = $_POST['codename']; $query = "SELECT * FROM Spy WHERE codeName= '$codename'"; //run query $result = runQuery($query); Intro PHP & MySQL

  31. Running the query • The runQuery function function runQuery($query) { $result = mysql_query($query); if ($result) { //print($query . "<br/>"); return $result; } else { //don't come here unless program logic error //or some other problem with the database print $query. " " . mysql_error(). "<br/>"; print "</body>"; print "</html>"; exit("Bye"); } } Intro PHP & MySQL

  32. The results from a SELECT query • If the query didn’t work, the runQuery method prints details and exits the script. Likely errors here: • Your SQL command is incorrect (wrong syntax, or wrong table or column names) • If the query returns 0 rows • It has worked successfully • There just aren’t any results to match what you asked for • Either there really aren’t any. Maybe the codename was mistyped. • Or you didn’t ask for what you should have asked for! Intro PHP & MySQL

  33. DisplayOneSpy.php - 0 rows • If no rows are returned, you need to check for this and write a sensible message to the user. • There is a MySQL function to find the number of rows //run query $result = runQuery($query); $numrows = mysql_num_rows($result); if ($numrows == 0) { print "No spy with codename = $codename"; print "</body></html>"; exit(); } Intro PHP & MySQL

  34. DisplayOneSpy.php – some results • The heading includes the codename searched for (obtained from the form parameter) • The file dbfunctions includes a function ‘displayVertTable’ from the query result • It creates a table from the column names and the data //run query $result = runQuery($query); //check there are some rows ... //print table of results print "<h3>Spy with codename = $codename</h3>"; displayVertTable($result); Intro PHP & MySQL

  35. What we want <tableborder = "1"> <tr><td><em>codeName</em></td> <td>bud</td></tr> <tr><td><em>firstName</em></td> <td>Fanny</td></tr> Etc...... Intro PHP & MySQL

  36. Creating the table • Inside the displayVertTable function //set up table print '<table border = "1">'; //find how many fields (columns) $fieldCount = mysql_num_fields($result); $row = mysql_fetch_row($result); //for each field, print column name and data for ($i=0; $i<$fieldCount; $i++) { print ("<tr>"); $fieldName = mysql_field_name($result, $i); print "<td><em>".$fieldName."</em></td>"; print ("<td>". $row[$i] . "</td>") ; print "</tr>"; } print ("</table>"); This is part of the dbfunctions.php file Intro PHP & MySQL

  37. Displaying your own table • In this module, you are welcome to use the displayVertTable function • For those who are keen programmers, and for serious websites • Obviously it is not ideal to display the column names as headings for the user • no spaces in the text! • You could write your own display for each table, choosing better headings Intro PHP & MySQL

  38. Displaying the skills • Just as before, define the query and run it • There is another function in dbfunctions.php called ‘displayTable’ which displays column names across the top then rows of data //display skills print "<h3>Skills</h3>"; $query = "SELECT skillName FROM SpySkillList L, SpyWithSkill W WHERE L.skillCode = W.skillCode AND W.spyCode = '$codename'"; $result = runQuery($query); displayTable($result); Intro PHP & MySQL

  39. The MySpy website • My Spies website is available for downloading on the module website • Download it, put into your www folder, and play with it using a URL like http://www2.macs.hw.ac.uk/~username/IntroDB/Spies/SpyStart.html • You can then take another copy and adapt it for use in your coursework, rather than starting from scratch, although you can also do this if you prefer! Intro PHP & MySQL

  40. Next few weeks • Week 6 (this week) • Monday – Lecture and lab as usual • Wed – no lecture: work on the assignment • Week 7 (next week) • Monday- Double lab • Wednesday Lecture: Continuing with MySQL and PHP- how to insert a record into the database from an html form • Week 8 • Monday- Double Lab • Wednesday Continuing with MySQL and PHP Intro PHP & MySQL

More Related