Using PHP to Store Data

Transcript Using PHP to Store Data

Storing Data on the Server
Introduction
We are going to look at some working
code
It writes fixed data into a simple onetable database
We will look at the key parts of the code
in detail
Nic Shulver, [email protected]
Storing Data on the Server
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
Nic Shulver, [email protected]
Example Code
<html>
<head>
<title>MS Access and OLEDB</title>
</head>
Standard HTML header
<body>
<h2>MS Access and OLEDB</h2>
<?php
Switches into
PHP mode
//=====================================//
// The data we are going to store
// Fixed data for this simple test.
$sSurname = "Sanchez";
$sForename = "Luis";
$sPassword = "alhambra";
$nID = 90;
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/catch block allows
us to catch errors
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 />');
}
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/catch block allows
us to catch errors
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 />';
}
Example Code
//=====================================//
// closes the connection, frees up resources
Frees up resources –
$adoCon->Close();
just good manners
$adoCon = null;
here, can be important
in bigger scripts
?>
</body>
</html>
Storing Data on the Server
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!
Nic Shulver, [email protected]
Storing Data on the Server
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
Nic Shulver, [email protected]
Storing Data on the Server
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”
Nic Shulver, [email protected]
Storing Data on the Server
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
Nic Shulver, [email protected]
Storing Data on the Server
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
Nic Shulver, [email protected]

Using PHP to Store Data

Transcript Using PHP to Store Data

Directory