Download
1 / 17
170 likes | 245 Views
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. DB Structure. Table: tblUsers Fields:
E N D
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
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)
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);
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
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
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]
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]
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]
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..."); • }
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"); • }
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>
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.
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”
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
