PHP and MySQL

1. PHP and MySQL After this lecture, you should be able to: Access a MySQL database with PHP mysql_connect() mysql_select_db() mysql_query() mysql_fetch_array() mysql_fetch_object() Complete the Assignment 5.

2. Accessing a MySQL Database with a PHP Script 1 Client Browser Apache Web Server 6 2 5 MySQL DB 3 PHP Module 4

3. MySQL Functions • Prefixed with mysql_ • mysql_connect() • mysql_select_db() • mysql_query() • etc. • http://www.php.net/manual/en/ref.mysql.php

4. Connecting to a Database <?php $connect = mysql_connect (“mysql.cs.orst.edu", "name", "password") if(!$connect) die("Unable to connect\n"); mysql_select_db(“my_database”) ?>

5. Common Include File: common.inc <?php define("DB_SERVER", "mysql.cs.orst.edu"); define("DB_USER", “my_database_user_name"); define("DB_PASSWORD", “my_password"); define("DB_NAME", “my_database_name"); $connect = mysql_connect(DB_SERVER, DB_USER, DB_PASSWORD) or die ("Unable to connect to server<br>\n"); mysql_select_db(DB_NAME) or die ("Unable to select database" . DB_NAME . "<br>\n"); echo "Connected to database!”; ?>

6. Retrieving Records $query = "select * from s"; $result = mysql_query($query);

7. Displaying Retrieved Records while ($record = mysql_fetch_array($result)) { echo “Supplier Number: “ . $record[‘sno’] . “\n”; echo “Supplier Name: “ . $record[‘sname’] . “\n”; echo “Supplier Status: “ . $record[‘status’] . “\n\n”; } Connected to database! Supplier Number: s1 Supplier Name: Smith Supplier Status: 20 Supplier Number: s2 Supplier Name: Jones Supplier Status: 10 …

8. Displaying Retrieved Records while ($record = mysql_fetch_object($result)) { echo “Supplier Number: $record->sno\n”; echo “Supplier Name: $record->sname\n”; echo “Supplier Status: $record->status\n\n”; }

9. Inserting a Record $query = "insert into s(sno, sname) values(‘s10', ‘Bose’); $result = mysql_query($query); if (!$result) { echo “Insertion failed”; }

10. Updating Records $query = "update s set city = ‘Albany' where sname = ‘Bose'"; $result = mysql_query($query); if (!$result) { echo “Update failed”; }

11. Deleting Records $query = "delete from s where status < 20"; $result = mysql_query($query); if (!$result) { echo “Deletion failed”; }

12. Example: get_menu() <?php function get_menu() { $query = "select * from item;"; $result = db_query($query); while($arr = mysql_fetch_array($result)) { $menu[$arr['item_id']] = $arr; } return $menu; } ?>

13. Example: get_item() <?php function get_item($item_id) { $query = "select * from item where item_id = " . $item_id; $result = db_query($query); $item = mysql_fetch_array($result); return $item; } ?>