Download
1 / 17
170 likes | 304 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, Autonumber • userSurname, text • userForename, text • userPassword, text • Note that passwords are normally stored in an encrypted form (more in later lectures)
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=[frod0ba661n2]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=[frod0ba661n2]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 //=====================================// // gets data from form, may be an empty string $sUserSurname = $_POST["txtUserSurname"]; $sUserForename = $_POST["txtUserForename"]; $sBoolean = $_POST["radioLogic"]; // if all strings are empty, goes back to the search page if( strlen($sUserSurname) + strlen($sUserForename) == 0 ) { header("Location: ReadDB.htm"); } ?>
The Code, #2 <!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, #3 <?php //=====================================// // creates a new Common-Object-Model (COM) connection object $adoCon = new COM("ADODB.Connection"); // the path to the folder holding this PHP script $sHere = dirname(__FILE__); // Access 2007 only // opens the connection using a standard Access connection string $adoCon-> Open( "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=$sHere/UserDetails.accdb"); $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 = $adoCon->Execute( $sSQL );
The Code, #4 //=====================================// // Outputs all the selected fields in the table "tblUsers", // processes each record until we reach the end of the recordset while (!$rsMain->EOF) { // gets each of the fields $sSurname = $rsMain->Fields("userSurname")->value; $sForename = $rsMain->Fields("userForename")->value; $sPassword = $rsMain->Fields("userPassword")->value; $nID = $rsMain->Fields("id")->value; // prints each of the fields print "$nID: $sForename $sSurname; pwd=[$sPassword]<br />\n"; // moves to next record OR runs out of records (hits end of recordset) $rsMain->MoveNext(); }
The Code, #5 //=====================================// // closes the recordset, frees up resources, kills all traces $rsMain->Close(); $rsMain->Release(); $rsMain = null; // closes the connection, frees up resources, kills all traces $adoCon->Close(); $adoCon = null; ?> </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 “Stewart Allen”!) • There are serious security holes in the way the SQL is built • What if someone invoked this script from a different form? • The field “radioLogic” is inserted directly into the SQL command… could contain ANY commands!
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
Tables in PHP print"<table width=\"200\" border=\"1\">"; print" <tr>"; print" <td>My name is </td>"; print" <td>$sFirstName</td>"; print" </tr>"; print"</table>";
Tables in PHP ?> <table width="200" border="1"> <tr> <td>My Lastname is </td> <td><?phpprint"$sLastName" ?></td> </tr> </table> <?php
