Download
1 / 10
100 likes | 195 Views
Introduction. We are going to look at some working code It writes fixed data into a simple one-table database We will look at the key parts of the code in detail. Database Structure. Test database has one table “tblWeek6”
E N D
Introduction • We are going to look at some working code • It writes fixed data into a simple one-table database • We will look at the key parts of the code in detail
Database Structure • Test database has one table • “tblWeek6” • What would happen if we named a table “select” or “update” or “values” or “table of users”? • The table has just four columns • id, an “auto-increment” integer field • twLastname, text, a varchar(30) field • twFirstname, text, also a varchar(30) field • twPassword, text, as above
Standard HTML header Example Code • <html> • <head> • <title>Writing Simple Data</title></head> • <body> • <h2>Writing Simple Data</h2> • <?php • //=====================================// • // The data we are going to store • // Fixed data for this simple test. • $sLastname = "Sanchez"; • $sFirstname = "Luis"; • $sPassword = "lsanchez"; • $nID = 65; Switches into PHP mode Provides some fixed data to work with – Normally this would come from a Form
The [action] or die(“message”) block allows us to view simple errors Example Code • // Connects to a MySQL server • $myID='xy123456'; • $mysqli = new mysqli("web.fcet.staffs.ac.uk",$myID,$myID,$myID) or die("Failed to connect to DB" . $mysqli->error); • //=====================================// • // Inserts new data into our users table • try • { $bItWorked = $mysqli->query( • "INSERT INTO tblWeek6 • (id, twLastname, twFirstname, twPassword) • VALUES • ($nID, '$sLastname', '$sFirstname', '$sPassword')" ); try/catch block allows us to catch errors
Example Code • if($bItWorked) • { echo "Successfully added the data ($nID, '$sLastname', '$sFirstname', '$sPassword') to the database.<br />"; • } • else • { echo "Failed to add the data ($nID, '$sLastname', '$sFirstname', '$sPassword') to the database!<br />"; • } • } • catch(Exception $e) • { echo 'Sorry - There was a problem with adding the data to the database.<br />'; • }
Example Code Frees up resources – just good manners here, can be important in bigger scripts [why?] • // closes the connection, frees up resources • $mysqli->close(); • $mysqli = null; • ?> • </body> • </html>
Key parts $mysqli = new mysqli("web.fcet.staffs.ac.uk", $myID, $myID, $myID); • This creates a new connection to our mySQL database • www.fcet.staffs.ac.uk is the database server • $myIDis the username, password and db name
Key parts $bItWorked = $mysqli->query( "INSERT INTO tblWeek6 (id, twLastname, twFirstname, twPassword) VALUES ($nID, '$sLastname', '$sFirstname', '$sPassword')" ); • Tries to run the SQL in the database (returns FALSE on failure, TRUE on success) • May cause an error, which we can trap • Notice the single quotes around string parameters • Notice the double quotes allowing “variable interpolation”
Re-use • The highlighted code is worth re-using • Makes sense to copy and paste this “boilerplate” code and modify it for your needs • Usually only needs the username to be changed • try/catch blocks are useful in lots of areas, not just database handling
Summary • We have seen some very simple PHP code • Parts of the application presented can be used as “boilerplate code” • Database access in PHP is quite simple to do • There are pitfalls for the unwary
