Using PHP to Retrieve Data

Transcript Using PHP to Retrieve Data

Retrieving Stored Data
Introduction
This set of slides shows:
The information source database structure
The data we are expecting to deal with
The output of the data reader PHP script
The code to build a dynamic SQL database
The code to read values from a database
Nic Shulver, [email protected]
Retrieving Stored Data
DB Structure
 Table: tblUsers
 Fields:
–
id, an “auto-increment” integer
–
userSurname, text
–
userForename, text
–
userPassword, text
 Note that passwords are normally stored in an
encrypted form (more in later lectures)
Nic Shulver, [email protected]
Retrieving Stored Data
The PHP/SQL to create the table
// Creates the table if it isn't already there. You may prefer to build
// tables by hand using the management GUI (e.g. phpMyAdmin)
$query = 'CREATE TABLE IF NOT EXISTS tblUsers (
id INT NOT NULL AUTO_INCREMENT,
userSurname VARCHAR(30),
userForename VARCHAR(30),
userPassword VARCHAR(30),
PRIMARY KEY(id) )';
$mysqli->query($ query) or die("Could not create table: " .
$mysqli->error);
Nic Shulver, [email protected]
Retrieving Stored Data
The data set we are using
 Example data
 Small selection of
names and
passwords
 But no repeated
surnames – not
such good test
data
 Hard to design
“realistic” data
Nic Shulver, [email protected]
Retrieving Stored Data
Simple Search Page
 The user can type in
 a surname,
 a forename,
 then specify the type of
search
 The interface allows for a
“wildcard” character, the
asterisk sign %
 % matches any text
 % is built into SQL
 % used in PHP, * used in
Access queries
Nic Shulver, [email protected]
Retrieving Stored Data
Example Output
Read A DB Example Search Results
Running the SQL command:
SELECT * FROM tblUsers WHERE
userSurname LIKE 'suarez' OR
userForename LIKE 'jason' ORDER BY id
ASC;
492: Linda Suarez; pwd=[fr0d0ba991n5]
921: Jason Imtiaz; pwd=[maskmypony]
Nic Shulver, [email protected]
Retrieving Stored Data
Example Output
Read A DB Example Search Results
Running the SQL command:
SELECT * FROM tblUsers WHERE
userSurname LIKE 's%' OR userForename
LIKE 'j%' ORDER BY id ASC;
90: Luis Sanchez; pwd=[alhambra]
492: Linda Suarez; pwd=[fr0d0ba991n5]
921: Jason Imtiaz; pwd=[maskmypony]
Nic Shulver, [email protected]
Retrieving Stored Data
Example Output
Read A DB Example Search Results
Running the SQL command:
SELECT * FROM tblUsers WHERE
userSurname LIKE ‘%i%' AND userForename
LIKE ‘%i%';
911: Toni Collins; pwd=[swissair]
901: Wilbur Harris; pwd=[wilburharris]
Nic Shulver, [email protected]
The Code, #1
<?php
// ReadDB example code
// gets data from form, may be an empty string
if(isset($_REQUEST["ReadDBsearch"]))
{
$sUserSurname = $_REQUEST["txtUserSurname"];
$sUserForename = $_REQUEST["txtUserForename"];
$sBoolean = $_REQUEST["radioLogic"];
}
else // shows the REQUEST fields and server variables
{ phpInfo(32); // useful when testing, NOT published site!
die("We don't seem to be running the right web form...");
}
Retrieving Stored Data
The Code, #2
// --------------------------------------------------- //
// checks to see if all strings are empty,
// if so we just go back to the search page
if( strlen($sUserSurname)
+ strlen($sUserForename) == 0 )
{ header("Location: ReadDB.htm");
}
Nic Shulver, [email protected]
The Code, #3
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01
Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;
charset=iso-8859-1">
<link rel=stylesheet href="simple.css" type="text/css">
<title>Read A DB Example</title>
</head>
<body>
<h2>Read A DB Example Search Results</h2>
The Code, #4
<?php
//=====================================//
// Database Section
//=====================================//
// Connects to a MySQL server
$id= 'xy123456';
$mysqli = new mysqli("web.fcet.staffs.ac.uk", $id, $id, $id);
$sSQL= "SELECT * FROM tblUsers WHERE
userSurname LIKE '$sUserSurname'
$sBoolean
userForename LIKE '$sUserForename'
ORDER BY id ASC;";
echo "Running the SQL command:<br /> $sSQL <br /><br />";
// searches the DB
$rsMain = $mysqli->query( $sSQL );
The Code, #5
//=====================================//
// Outputs all the selected fields in table "tblUsers",
// processes each record until we reach end of recordset
while($row = $rsMain->fetch_assoc())
{ $sSurname = $row["userSurname"];
$sForename = $row["userForename"];
$sPassword = $row["userPassword"];
$nID = $row["id"];
// prints each of the fields
print "$nID: $sForename $sSurname; pwd=[$sPassword]<br
/>\n";
}
The Code, #6
//=====================================//
// frees up resources
$rsMain = null;
$mysqli->close();
?>
</body>
</html>
Retrieving Stored Data
Search Script Steps
So the search software takes the form data,
Connects to the database,
Reads only the interesting records,
Holds data in a recordset,
Reads the data from each of the fields,
Writes information out to the user,
Moves on to the next record.
Nic Shulver, [email protected]
Retrieving Stored Data
Some notes and comments
The user interface for searching is poor –
relies on knowing the “ste%en” approach to
match “Steven” or “Stephen” (or “stellar alien”!)
There are serious security holes in the way
the SQL is built
What if this script is run from a different form?
The field “radioLogic” is inserted directly into the
SQL command… could contain ANY commands!
More secure if we use “prepared statements”
Nic Shulver, [email protected]
Retrieving Stored Data
Conclusion
The database, script and HTML interface
page are available for download
The script is only 80 lines long (so pretty
short) and really needs more error trapping
The SQL, database and output are all pretty
simple
But putting it all together in a working script
isn’t so easy
Nic Shulver, [email protected]

Using PHP to Retrieve Data

Transcript Using PHP to Retrieve Data

Directory