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
lt?php connect mysql_connect
(mysql.cs.orst.edu", "name", "password") or
die ("Unable to connect\n")
mysql_select_db(my_database) ?gt
5
Common Include File common.inc
lt?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
serverltbrgt\n") mysql_select_db(DB_NAME) or
die ("Unable to select database" .
DB_NAME . "ltbrgt\n") echo "Connected to
database! ?gt
6
Retrieving Records
query "select from s" result
mysql_query(query)
7
Displaying Retrieved Records
while (record mysql_fetch_array(result))
echo Supplier Number . recordsno .
\n echo Supplier Name . recordsname
. \n echo Supplier Status .
recordstatus . \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-gtsno\n echo
Supplier Name record-gtsname\n echo
Supplier Status record-gtstatus\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 lt 20"
• result
• mysql_query(query)
• if (!result)
• echo Deletion failed