Transcript PHP connectivity

IT420: Database Management
and Organization
PHP - MySQL Connection
8 March 2006
Adina Crainiceanu
www.cs.usna.edu/~adina
Web Database Architecture
HTTP
Client browser
Web server with
PHP enabled
API
Database
Management
System
Goals Today
 Connect from PHP to MySQL
MySQL






Relational Database Management System
Free
Open source
Portable
High performance
Support available
Example Application
Database: dbmusic
Table: songs(ISBN, Title, SingerID, Length)
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!
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);
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;
}
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’);
Query the Database
 qresult mysql_query(query)
 Example:
$query = “select * from songs where Title like
‘%home%’ ”;
$results = mysql_query($query);
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 />”;
}
Check Modification Results
 intvar = mysql_affected_rows()
 Used after INSERT, DELETE, UPDATE
Disconnect from Database
 Free query results
 mysql_free_result(qresult);
 Close connection
 mysql_close(connection)
VP-5 MVR Fund Raiser Application
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
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.
Lab Exercise: PHP + MySQL
 Save order data from VP-5 Fund Raiser
application into vp5fund database.
Save Your Work!
 Copy the D:\sokkit\mysql\data\ directory to
your X drive