it420: database management and organization
DESCRIPTION
IT420: Database Management and Organization. PHP - MySQL Connection 8 March 2006 Adina Crainiceanu www.cs.usna.edu/~adina. Web Database Architecture. HTTP. API. Database Management System. Web server with PHP enabled. Client browser. Goals Today. Connect from PHP to MySQL. MySQL. - PowerPoint PPT PresentationTRANSCRIPT
PHP - MySQL Connection8 March 2006
Adina Crainiceanu
www.cs.usna.edu/~adina
IT420: Database Management and Organization
Web Database Architecture
Client browserWeb server with PHP enabled
Database Management System
HTTP API
Goals Today
Connect from PHP to MySQL
MySQL
Relational Database Management System Free Open source Portable High performance Support available
Example Application
Database: dbmusicTable: 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 connectionmysql_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’) ordie(‘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