Transcript Lecture 6

IS2803
Developing Multimedia
Applications for Business (Part 2)
Lecture 6: Accessing a database with PHP
Rob Gleasure
[email protected]
robgleasure.com
IS2803

Today's lecture
 Accessing a database with PHP
 An example
Accessing Databases with SQL

As we saw in lecture 4, accessing files in PHP requires a number of
steps and databases are no different. These steps (roughly) are:
 Open a connection with the file
 Read data from the file into a file handle variable
 Manipulate the contents of the file (optional)
 Close the connection
Accessing Databases with SQL

Imagine the following database table leaders in politicians.accdb
ID
Firstname
Surname
1
Enda
Kenny
2
Eamon
Gilmore
3
Micheal
Martin
4
Gerry
Adams
Accessing Databases with SQL

To connect to MS Access we use the COM() function
 This function creates an instance of the ActiveX Data Object
(ADO), which is part of the component object model (COM)
object for accessing databases and other similar data sources
<?php
//create an instance of the ADO connection object
$conn = new COM('ADODB.Connection') or die('Cannot start ADO');
?>

Note that, just as when we were opening files in lecture 4, we are
saving the connection details in a file handle (In this case ‘$conn’)
Accessing Databases with SQL

Next we create a connection string to hold the database driver
details and the absolute URL of the database
//in this example the URL for our database is ‘C:/myFolder/politicians.accdb’
$connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= C:/
myFolder/politicians.accdb;Persist Security Info=False;";

Note that if we’re using the old MS Access format (.mdb), our
connection string will look a little different
$connStr = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data
Source=C:/xampp/htdocs/politicians.mdb";
However, .mdb issues shouldn't be a concern for us in this course
Reading from a database

Next, we pass the connection string variable (in this case $connStr)
to the open function
//Open the connection to the database
$conn->open($connStr);

Again, note that we are saving all the connection details in the file
handle ‘$conn’
Reading from a database

Similar to the process for reading text files from lecture 4, we need
to read the contents of a database into a variable

When a variable is allocated the output from a SELECT query, this
variable now stores what is called a ‘record set’

To run the SELECT query (or any other SQL queries) we call the
ADO function ->execute() on the record set variable
//Code to create a variable $rS to store a new record set from your table
$rS = $conn->execute("SELECT * FROM leaders");
Reading from a database

We can now pull information out of the record set variable using the
->Fields() function.
$id= $rS->Fields(0);
$firstname= $rS->Fields(1);
$surname = $rS->Fields(2);

Note that ->Fields() works on a single record in the Recordset object
(i.e. one row in the table)
Reading from a database

We spoke last week about pointers.

ADO comes with a number of built-in functions (we have already
seen open() and execute()) and a couple of these are especially
important for moving a pointer (i.e. navigating) around a record set
 MoveFirst (): moves to the first record in a Recordset object
 MoveLast(): moves to the last record in a Recordset object
 MoveNext(): moves to the next record in a Recordset object
 MovePrevious(): moves to the previous record in a Recordset
object
Note: errors will be thrown if these functions are called and the pointer
has nowhere to go, e.g. if database is empty or EOF has been reached
Reading from a database

We can use these functions to iterate through the records stored in
the record set
while (!$rS->EOF) //while $rS is not at end of file
{
echo "First name is ".$rS->Fields(1)."<br />";
// we then move to next record
$rS->MoveNext();
}
Writing to a database

Writing to a database is very similar, the difference is in our SQL
statement
//Code to execute sql statement to insert name John into leaders field FirstName
$conn->execute("INSERT INTO leaders (FirstName) VALUES (6, 'Joan‘,’Collins’)");

Each time this statement runs, it will try to insert this data into a new
row in the table
Closing the connection

As with all files, when we’re finished we must ensure we close the
connection. This closing requires two steps with databases
//release memory from rS
$rS->Close();
//release memory from conn
$conn->Close();
An example

Try out the following code
<html>
<head>
<title>Lecture 6 example</title>
</head>
<body>
<?php
/**/
// create an instance of the ADO connection object
$conn = new COM ("ADODB.Connection") or die("Cannot start ADO");
// define connection string, specify database driver
$connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:/xampp2/htdocs/IS2803.accdb;Persist Security Info=False;";
// open the connection to the database
$conn->open($connStr);
// create a variable to store a SQL query that selects every field where the Username is the same as $username
$myQuery = "SELECT * FROM Politicians";
// create a variable $rS to store the new record returned when the SQL query is executed
$rS = $conn->execute($myQuery);
echo '<table>';
echo '<tr>';
echo '<th>First name</th><th>Surname</th>';
echo '<tr>';
while (!$rS->EOF){
// echo out the details from one record at a time
echo '<tr>';
echo '<td>'.$rS->Fields(1).'</td><td>'.$rS->Fields(1).'</td>';
echo '</tr>';
// then move to next record
$rS->MoveNext();
}
echo '</table>';
$rS->close();
$conn->close();
?>
</body>
</html>
Troubleshooting



You may get an error that reads “Fatal error: Class 'COM' not
found…”
This occurs because a previously default driver isn’t default any
more
The solution is to open xampp/php/php.ini and add the following
lines to the end
[PHP_COM_DOTNET]
extension=php_com_dotnet.dll
Want to read more?

Links and references
 W3Schools tutorial on PHP and MS Access
 http://www.w3schools.com/php/php_db_odbc.asp
 Generic tutorial on PHP and SQL connections
 http://www.phpeveryday.com/articles/PHP-Mysql-DatabaseTutorial-P843.html