Transcript Lecture

Database
20/2/12 Connection

http://www.connectionstrings.com/
Web Server –
Corvus 3
Client PC
Request Data stored
on DB
Db
Data retrieved from Db
and sent to browser
window
3

This ability to access multiple types of data
stores with a relative simple and flat object
model, make ADO the simplest method yet
devised for retrieving data
◦ ADO works with ODBC compliant sources
4

The Connection Object is used to hold
information about the data store you want to
access

Because it is an Object it has properties and
methods

Some of these will be needed when creating
you PHP application
5

Before you can retrieve any data from a database, you have to
create and initalise a connection to that database

ADODB contains all the ADO objects that you will need to use

In order to use any of the Object from the ADO Object Model
you will need to create them

PHP uses the following syntax for creating an Instance of an
object
$conn = new COM ("ADODB.Connection") or die("Cannot start
ADO");
Note: we store the connection in a variable ($conn)
for later use in the script. The "die" part will be
executed if the connection fails.
6


Connection String ‘tells’ your connection
what database you wish to connect to.
If you have a system DSN set up all you
need do is supply this when setting the
connection String.

This is the simplest form of string that can
be used.

The connection can also be used for a
DSN-less connection.
7
$connStr =
"PROVIDER=Microsoft.ACE.OLEDB.12.0;Data
Source=c:\Inetpub\wwwroot\MBSEBus\CHeav
in\Databases\cus.accdb";
Note: WWWRoot is a hidden folder (by default) and can't be displayed
in directory
listing
8



Connection strings are string variables which
contain database connection information and then
passed to ADO(ActiveX Data Objects) which will
interpret them and act accordingly
They need to be in a comprehensible for ADO
format
If you're connecting to an MS Access database, you
need the DRIVER, and the DBQ, and some other
optional information
9



When the you have created an instance of
the connection object and you have created
the assigned a connection string you then
need to explicitly open the connection to
the database.
To open a connection:
$conn->open($connStr);
If the Open method executes you have a
working connection to the database
10


As with any Object you need to free the
memory associated with it when you are
finished.
However, before you do that, you need to
close the connection
$conn->Close();
11


If you wanted to close the connection but
connect to a different data source, you can
use the same instance of the connection
object.
Simply close the connection first, the set the
connection information appropriately and,
and reopen it.
12

Setting up the recordset
$rS = $conn->execute("SELECT * FROM test");
13
After connecting to a database. You may want
to do all or some of the following:
◦ Read Data
◦ Write Data
◦ Delete Data
14
◦ Recordset is simply a set of records.
◦ The Recordset may be used to contain
 a subset of all the records in a table
 All the records in a table.
 A specifically chosen set of records
 Using a specific SQL statement.

You will need to create an instance of the
recordset object before you can use it
15
$rS = $conn->execute("SELECT * FROM test");
$f1 = $rS->Fields(0);
$f2 = $rS->Fields(1);
while (!$rS->EOF)
{
print $f1->value." ".$f2->value."<br />\n";
$rS->MoveNext();
}
$rS->Close();
16
Select Records
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta content="text/html; charset=windows-1252" http-equiv="Content-Type" />
<title>Untitled 1</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:\Inetpub\wwwroot\MBSEBus\CHeavin\Databases\cus.accdb";
//Open the connection to the database
$conn->open($connStr);
echo "Connection Open<br>";
//recordset code
$rS = $conn->execute("SELECT * FROM test");
$f1 = $rS->Fields(0);
$f2 = $rS->Fields(1);
while (!$rS->EOF)
{
print $f1->value." ".$f2->value."<br />\n";
$rS->MoveNext();
}
$rS->Close();
$conn->Close();
?>
</body>
</html>
17
18
Example:
$sql="UPDATE test SET name='Mary' WHERE
name='Ciaran'";
19
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta content="text/html; charset=windows-1252" http-equiv="Content-Type" />
<title>Untitled 1</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:\Inetpub\wwwroot\mbsebus\CHeavin\Databases\cus.accdb";
//Open the connection to the database
$conn->open($connStr);
echo "Connection Open<br>";
$sql="UPDATE test SET name='Ciara' WHERE name='harry'";
$rS = $conn->execute($sql);
echo "Record Updated<br>";
$conn->Close();
?>
</body>
</html>
20
$sql="DELETE * FROM test WHERE
name='barry'";
21
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta content="text/html; charset=windows-1252" http-equiv="Content-Type" />
<title>Untitled 1</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:\Inetpub\wwwroot\mbsebus\CHeavin\Databases\cus.accdb";
//Open the connection to the database
$conn->open($connStr);
$sql="DELETE * FROM test WHERE name='Ciara'";
echo "Connection Open<br>";
$rS = $conn->execute($sql);
print "Record Deleted";
$conn->Close();
?>
</body>
</html>
22
$sql="INSERT INTO test (name,age) VALUES
('".$name1."','".$age1."' );";
//Set SQL query
23
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta content="text/html; charset=windows-1252" http-equiv="Content-Type" />
<title>Untitled 1</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:\Inetpub\wwwroot\mbsebus\CHeavin\Databases\cus.accdb";
//Open the connection to the database
$conn->open($connStr);
echo "Connection Open<br>";
$name1="barack obama";
$age1="32";
$sql="INSERT INTO test (name,age) VALUES ('".$name1."','".$age1."' );"; //Set SQL query
$conn->Execute($sql); //Execute SQL query
print "Record Inserted";
$conn->Close(); //Close database connection
?>
</body>
</html>
24
<html>
<head>
<title>Join</title>
</head>
<body>
<FORM METHOD="POST"
ACTION="memberadded.php">
Name:<INPUT TYPE="text" SIZE="40"
name="cust_name"><br>
Age:<INPUT TYPE="text" SIZE="40"
name="cust_age"><br>
<input type="submit" value="Proceed">
</form>
</body>
</html>
25
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta content="text/html; charset=windows-1252" http-equiv="Content-Type" />
<title>Untitled 1</title>
</head>
<body>
<html>
<head><title>New User</title></head>
<body>
<br/>
Name:<?php echo($_POST['cust_name']); ?><br/>
Age:<?php echo($_POST['cust_age']); ?><br/>
<?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:\Inetpub\wwwroot\mbsebus\CHeavin\Databases\cus.accdb";
//Open the connection to the database
$conn->open($connStr);
$name=$_POST['cust_name'];
$age=$_POST['cust_age'];
$sql="INSERT INTO test (name,age) VALUES ('".$name."','".$age."' );"; //Set SQL query
$conn->Execute($sql); //Execute SQL query
$conn->Close(); //Close database connection
?>
</body>
</html>
</body>
</html>
26