PHP MySQL Connection

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
• _at_ 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 '.row1.'
  '
• //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
  

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