IT420: Database Management and Organization

1. IT420: Database Management and Organization MySQL and PHP 3 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. Why Use DBMS? • Fast access to data • Queries to easily extract data • Built-in concurrency control • Built-in security control

4. HTTP API Database Management System Web server with PHP enabled Client browser Learned So Far…

5. Goals Today • MySQL • Connect from PHP to MySQL

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

7. Working with MySQL • SQL Monitor • Always available • Write SQL statements • ; after each statement! • PHPMyAdmin

8. Current Settings • Each machine is a server – web, db • Web server: localhost:80 • MySQL server: localhost:3306 • MySQL user: root, no password • ALL privileges • Disk location D:/sokkit • site/ • mysql/

9. Lab Demo • Start MySQL server • Use MySQL – local machine

10. Lab Exercise • Start MySQL server • Sokkit Control Panel  Start database • Start MySQL monitor • D:/sokkit/mysql/bin/mysql –u root • Create a database called vp5fund • create database vp5fund; • Check database was created • show databases;

11. SQL for MySQL • Surrogate keys variant: • AUTO_INCREMENT • If column value left blank, generated value = max+1 • show • databases • tables • describe tableName

12. VP-5 MVR Fund Raiser Application

13. Lab Exercise • To use the database just created: • use vp5fund; • Create table (use SQL) • Items(ItemName, Price) • Orders(OrderID, ShippingAddress) • ItemsOrdered(OrderID, ItemName, Quantity) • Insert few rows in tables • List all rows in Orders table (use SQL)

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

15. 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!

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

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

18. 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’);

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

20. Process 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 />”; }

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

22. Lab Exercise • Save order data from VP-5 Fund Raiser application into vp5fund database. Display appropriate message in order confirmation screen. • Display all orders from VP-5 Fund Raiser application. • Display all orders from VP-5 Fund Raiser application with shipping address in Maryland.

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