1 / 32

Connecting to Database

Connecting to Database. 21 Feb 2011. Database Options. Can use many different databases in conjunction with php. MySql; MS Access; Oracle; etc etc Most commonly used with php is MySql Open source Huge support in terms of online forums, tutorials, documentation and so on. Database Options.

ellie
Download Presentation

Connecting to Database

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. Connecting to Database 21 Feb 2011

  2. Database Options • Can use many different databases in conjunction with php. • MySql; MS Access; Oracle; etc etc • Most commonly used with php is MySql • Open source • Huge support in terms of online forums, tutorials, documentation and so on

  3. Database Options • We will be using MS Access • Much of the material can be tweaked to suit connecting to MySql database. • As per usual a good resource is w3schools.com

  4. Create ADO connection • To connect to MS Access we need to create an instance of the ActiveX Data Object (ADO) • ADO are part of the component object model (COM) objects for accessing data sources //create an instance of the ADO connection object $conn = new COM('ADODB.Connection') or die('Cannot start ADO');

  5. Connection String • Create a connection string = Holds the database driver details and location of dB //define connection string variable to hold database driver details and location of dB //Make sure to change this to location of your DB $connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:/inetpub/wwwroot/mbsebus/ASugrue/databases/dbtotal.accdb;Persist Security Info=False;"; // If using old access format .mdb the following connection string appropriate: $connStr = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\wwwroot\MBSEBus\ASugrue\Databases\dbtotal.mdb";

  6. Open the Connection • Pass the connection string variable (in this case $connStr) to the open function //Open the connection to the database $conn->open($connStr);

  7. Reading from database //Code to create record set from your dB $rS = $conn->execute("SELECT * FROM tblCustomer"); $conn->execute() is used to run sql statements. $rS is created to store the record set from the sql statement

  8. Reading from database • Create variables to store the data from the record set $cust_id = $rS->Fields(0); $first_name = $rS->Fields(1);

  9. Reading from Database • Run a loop to iterate through all the records stored in the record set (as per the sql statement) while (!$rS->EOF) //while $rS is not at end of file (EOF) { echo $cust_id." ".$first_name."<br />\n"; $rS->MoveNext(); //move to next record }

  10. <?php //create an instance of the ADO connection object $conn = new COM('ADODB.Connection') or die('Cannot start ADO'); //define connection string variable to hold database driver details and location of dB //Make sure to change this to location of your DB $connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:/inetpub/wwwroot/mbsebus/ASugrue/databases/dbtotal.accdb;Persist Security Info=False;"; // If using old access format .mdb the following connection string appropriate: $connStr = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\wwwroot\MBSEBus\ASugrue\Databases\dbtotal.mdb"; //Open the connection to the database $conn->open($connStr); //Code to create record set from your dB $rS = $conn->execute("SELECT * FROM tblCustomer"); $cust_id = $rS->Fields(0); $first_name = $rS->Fields(1); while (!$rS->EOF) { echo $cust_id." ".$first_name."<br/>"; $rS->MoveNext(); } $rS->Close(); $conn->Close(); ?>

  11. Reading from database $rS->Close(); //release memory from rS $conn->Close(); //release memory from conn • Important to close connection to database and to release memory struture of rS

  12. Inserting a record //Code to execute sql statement to insert name John into tblCustomer field FirstName $conn->execute("INSERT INTO tblCustomer (FirstName) VALUES ('John')"); • Each time this statement runs, it inserts name John into tblCustomer. Not a very useful piece of code!

  13. Inserting a record //Code to execute sql statement to insert the value of a name variable into tblCustomer field FirstName $conn->execute("INSERT INTO tblCustomer (FirstName) VALUES (‘$name')"); • Immediately more dynamic!

  14. Insert using form input <form action="insert.php" method="post"> First name: <input type="text" name="name" /><br /> <input type="submit"/> </form>

  15. <?php $conn = new COM('ADODB.Connection') or die('Cannot start ADO'); $connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\inetpub\wwwroot\mbsebus\ASugrue\databases\dbtotal.accdb;Persist Security Info=False;"; $conn->open($connStr); $name = $_POST['name']; //Code to execute sql statement to insert name variable into tblCustomer field FirstName $conn->execute("INSERT INTO tblCustomer (FirstName) VALUES ('".$name."')"); echo "Name: ".$name." was inserted hopefully!"; $conn->Close(); ?>

  16. Good idea to Store SQL statements in variable! <?php $conn = new COM('ADODB.Connection') or die('Cannot start ADO'); $connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\inetpub\wwwroot\mbsebus\ASugrue\databases\dbtotal.accdb;Persist Security Info=False;"; $conn->open($connStr); $name = $_POST['name']; $conn->execute("INSERT INTO tblCustomer (FirstName) VALUES ('".$name."')"); echo "Name: ".$name." was inserted hopefully!"; $conn->Close(); ?>

  17. Good idea to Store SQL statements in variable! <?php $conn = new COM('ADODB.Connection') or die('Cannot start ADO'); $connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\inetpub\wwwroot\mbsebus\ASugrue\databases\dbtotal.accdb;Persist Security Info=False;"; $conn->open($connStr); $name = $_POST['name']; $sqlString = “INSERT INTO tblCustomer (FirstName) VALUES ('".$name."')”; $conn->execute($sqlString); echo "Name: ".$name." was inserted hopefully!"; $conn->Close(); ?>

  18. Update Record You might want to update data stored in the database. For instance, if someone needs to change the title or name of their project

  19. Update SQL Example: $sqlString="UPDATE tblCustomer SET FirstName=‘Jim' WHERE name=‘John'";

  20. Update Record <?php $conn = new COM('ADODB.Connection') or die('Cannot start ADO'); $connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\inetpub\wwwroot\mbsebus\ASugrue\databases\dbtotal.accdb;Persist Security Info=False;"; $conn->open($connStr); $sqlString="UPDATE test SET name=‘Jim’ WHERE name=‘John’"; $conn->execute($sqlString); $conn->Close(); ?>

  21. Delete Record • You may need to remove data stored in your database. • For instance, the project is no longer sponsored by a company and their details must be removed from the dB

  22. Delete SQL $sqlStringDelete="DELETE * FROM tblCustomer WHERE FirstName=‘Sean’";

  23. Update Record <?php $conn = new COM('ADODB.Connection') or die('Cannot start ADO'); $connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\inetpub\wwwroot\mbsebus\ASugrue\databases\dbtotal.accdb;Persist Security Info=False;"; $conn->open($connStr); $sqlStringDelete="DELETE * FROM tblCustomer WHERE FirstName=‘Sean’"; $conn->execute($sqlStringDelete); $conn->Close(); ?>

  24. For other databases - • http://www.connectionstrings.com/

More Related