F27db introduction to database systems accessing mysql database via php 1
This presentation is the property of its rightful owner.
Sponsored Links
1 / 40

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


  • 60 Views
  • Uploaded on
  • Presentation posted in: General

F27DB Introduction to Database Systems Accessing MySQL database via PHP - 1. Helen Hastie [email protected] 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.

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


F27db introduction to database systems accessing mysql database via php 1

F27DB Introduction to Database SystemsAccessing MySQL database via PHP - 1

Helen Hastie

[email protected]

Room: EMB244

Material available on Vision (modified from slides by Monica Farrow)

Intro PHP & MySQL


Recap using php to process form data and reply

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


Recap php script

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


Spy website design

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


Remembering data

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


Sessions

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


Session management theory

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


Sessions in php example

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


Session id on the server side theory

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


End of a session theory

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


Sessions in php practice

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


Session variables practice

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


Summary

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


Login page

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


Receiving the username and password

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


Requesting to view a spy

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


Body of the html the request

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


Display spy response

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


Finding the spy details the response

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


Body of spy display page

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


Displayonespy php 1

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


Displayonespy php 2

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


Displayonespy php 3

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


Displayonespy php 4

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


Php and database

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


Connecting to mysql 1

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


If you can t connect to the database

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


The dbfunctions php file

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


Connecting to the database

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


Displayonespy php creating the query

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


Running the query

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


The results from a select query

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


Displayonespy php 0 rows

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


Displayonespy php some results

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


What we want

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


Creating the table

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


Displaying your own table

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


Displaying the skills

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


The myspy website

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


Next few weeks

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


  • Login