IT420: Database Management and Organization

1. IT420: Database Management and Organization PHP - MySQL Connection 8 March 2006 Adina Crainiceanu www.cs.usna.edu/~adina

2. Web Database Architecture HTTP API Database Management System Web server with PHP enabled Client browser

3. Goals Today • Connect from PHP to MySQL

4. MySQL • Relational Database Management System • Free • Open source • Portable • High performance • Support available

5. Example Application Database: dbmusic Table: songs(ISBN, Title, SingerID, Length)

6. Use DBMS from PHP • Connect to the database server • Specify database to use • Send queries and retrieve results • Process results • Close connection • All PHP functions return ‘false‘ if operation unsuccessful!

7. Example: $searchterm = $_POST['searchterm']; //connect @ $db = mysql_connect('localhost','root'); if (!$db){ echo('connect failed'); exit; } $dbselected= mysql_select_db('dbmusic') or exit('could not select db'); //query $query = "select * from songs where Title like '%$searchterm%'"; //process results $results = mysql_query($query) or die("could not retrieve rows"); while ($row = mysql_fetch_row($results)){ echo 'Title: '.$row[1].' <br>'; } //close connection mysql_free_result($results); mysql_close($db);

8. Connect to MySQL • dbconnection mysql_connect(servername, username, [password]) • Always test and handle errors! • Example: $dbconn = mysql_connect(‘localhost’,’root’); if (!$dbconn){ echo ‘Could not connect to db. Exit’; exit; }

9. Select Database to Use • bool mysql_db_select(dbname, [dbconnection]) • Always test and handle errors! • Example: $dbs = mysql_db_select(‘dbmusic’) or die(‘Could not select db’);

10. Query the Database • qresult mysql_query(query) • Example: $query = “select * from songs where Title like ‘%home%’ ”; $results = mysql_query($query);

11. Process Select Results • nbrows = mysql_num_rows(qresult) • row = mysql_fetch_row(qresult) • row = mysql_fetch_array(qresult) • Example: while ($row = mysql_fetch_row($results)){ foreach($row as $column) echo “$column ”; echo “<br />”; }

12. Check Modification Results • intvar = mysql_affected_rows() • Used after INSERT, DELETE, UPDATE

13. Disconnect from Database • Free query results • mysql_free_result(qresult); • Close connection • mysql_close(connection)

14. VP-5 MVR Fund Raiser Application

15. Lab Exercise • Recover the database vp5fund created last time • D:\sokkit\mysql\data • Start MySQL Monitor • D: • cd sokkit\mysql\bin • mysql –u root • To use the database: use vp5fund; • These tables should exist: • Items(ItemName, Price) • Orders(OrderID, ShippingAddress) • ItemsOrdered(OrderID, ItemName, Quantity) • Insert few rows in tables

16. Lab Exercise: PHP + MySQL • Display all orders from VP-5 Fund Raiser application. For each order display: • OrderID • Shipping address • Items names and price • Display all orders from VP-5 Fund Raiser application with shipping address in Maryland.

17. Lab Exercise: PHP + MySQL • Save order data from VP-5 Fund Raiser application into vp5fund database.

18. Save Your Work! • Copy the D:\sokkit\mysql\data\ directory to your X drive