Download
1 / 11
110 likes | 191 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 “tblUsers”
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 • “tblUsers” • What would happen if we named a table “select” or “update” or “values”? • The table has just four columns • id, an “autonumber” • userSurname, text • userForename, text • userPassword, text
Standard HTML header Example Code • <html> • <head> • <title>MS Access and OLEDB</title> • </head> • <body> • <h2>MS Access and OLEDB</h2> • <?php • //=====================================// • // The data we are going to store • // Fixed data for this simple test. • $sSurname = "Sanchez"; • $sForename = "Luis"; • $sPassword = "alhambra"; • $nID = 90; Switches into PHP mode Provides some fixed data to work with – Normally this would come from a Form
Example Code • //=====================================// • // creates a new Common-Object-Model (COM) connection object • $adoCon = new COM("ADODB.Connection"); • //=====================================// • // opens the connection using a standard Access connection string • try • { $adoCon->Open("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=c:/inetpub/wwwroot/phpTest/test.accdb"); • } • catch(Exception $e) • { die('Sorry - There was a problem with opening the database.<br />'); • } try/catch block allows us to catch errors
Example Code • //=====================================// • // NB the SQL Execute WILL FAIL unless the Internet Guest Account • // (IUSR_machineName) has read/write/modify access to both the MDB file and the • // directory (since it may need to create an LDB temp file). • // May also need R/W/M permissions on the system TEMP directory. • try • { $adoCon->Execute • ( "INSERT INTO tblUsers • (id, userSurname, userForename, userPassword) • VALUES • ($nID, '$sSurname', '$sForename', '$sPassword');" • ); • } • catch(Exception $e) • { echo 'Sorry - There was a problem with adding the data to the database.<br />'; • } try/catch block allows us to catch errors
Example Code Frees up resources – just good manners here, can be important in bigger scripts • //=====================================// • // closes the connection, frees up resources • $adoCon->Close(); • $adoCon = null; • ?> • </body> • </html>
Key parts • $adoCon = new COM("ADODB.Connection"); • This creates a new Common Object Model (COM) object based on the Active Database Objects (ADO) Database Connection template • This is Microsoft-specific code!
Key parts • $adoCon->Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:/inetpub/wwwroot/phpTest/test.mdb"); • $adoCon->Open("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=c:/inetpub/wwwroot/phpTest/test.accdb"); • Opens a database, given information about the driver to load (OLE DB version 4.0) and the location of the database • Note the forward slashes in the DB file path – works fine but you’d expect backslashes really E.g. Source=c:\\inetpub\\wwwroot\\phpTest\\test.mdb
Key parts • $adoCon->Execute • ( "INSERT INTO tblUsers • (id, userSurname, userForename, userPassword) • VALUES • ($nID, '$sSurname', '$sForename', '$sPassword');" • ); • Tries to run the SQL in the database • 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 path to the database 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
