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