1 / 26

Lecture 7

Lecture 7. Database 20/2/12 Connection. Useful Link. http://www.connectionstrings.com/. Database Connections. Web Server – Corvus 3. Request Data stored on DB. Client PC. Db. Data retrieved from Db and sent to browser window. ADO – ActiveX Data Objects.

ginger-neal
Download Presentation

Lecture 7

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Lecture 7 Database 20/2/12 Connection

  2. Useful Link • http://www.connectionstrings.com/

  3. Database Connections Web Server – Corvus 3 Request Data stored on DB Client PC Db Data retrieved from Db and sent to browser window

  4. ADO – ActiveX Data Objects. • 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

  5. Connection Object • 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

  6. The Connection Object • 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.

  7. Connection String • 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.

  8. Connection String $connStr = "PROVIDER=Microsoft.ACE.OLEDB.12.0;Data Source=c:\Inetpub\wwwroot\MBSEBus\CHeavin\Databases\cus.accdb"; Note: WWWRoot is a hidden folder (by default) and can't be displayed in directory listing

  9. Connection String • 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

  10. Opening the connection. • 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

  11. Closing the Connection • 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();

  12. Closing the connection. • 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.

  13. RecordSet • Setting up the recordset $rS = $conn->execute("SELECT * FROM test");

  14. Reading Data from a Database After connecting to a database. You may want to do all or some of the following: • Read Data • Write Data • Delete Data

  15. Reading Data from a Database • 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

  16. Recordset $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();

  17. 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>

  18. Update SQL Example: $sql="UPDATE test SET name='Mary' WHERE name='Ciaran'";

  19. Update 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>"; $sql="UPDATE test SET name='Ciara' WHERE name='harry'"; $rS = $conn->execute($sql); echo "Record Updated<br>"; $conn->Close(); ?> </body> </html>

  20. Delete SQL $sql="DELETE * FROM test WHERE name='barry'";

  21. Delete 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); $sql="DELETE * FROM test WHERE name='Ciara'"; echo "Connection Open<br>"; $rS = $conn->execute($sql); print "Record Deleted"; $conn->Close(); ?> </body> </html>

  22. Insert SQL $sql="INSERT INTO test (name,age) VALUES ('".$name1."','".$age1."' );"; //Set SQL query

  23. Insert 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>"; $name1="barackobama"; $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. Add Record through Form <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. memberadded.php <!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> <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>

More Related