Download - IT420: Database Management and Organization
![Page 1: IT420: Database Management and Organization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56813251550346895d98cde3/html5/thumbnails/1.jpg)
PHP - MySQL Connection8 March 2006
Adina Crainiceanu
www.cs.usna.edu/~adina
IT420: Database Management and Organization
![Page 2: IT420: Database Management and Organization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56813251550346895d98cde3/html5/thumbnails/2.jpg)
Web Database Architecture
Client browserWeb server with PHP enabled
Database Management System
HTTP API
![Page 3: IT420: Database Management and Organization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56813251550346895d98cde3/html5/thumbnails/3.jpg)
Goals Today
Connect from PHP to MySQL
![Page 4: IT420: Database Management and Organization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56813251550346895d98cde3/html5/thumbnails/4.jpg)
MySQL
Relational Database Management System Free Open source Portable High performance Support available
![Page 5: IT420: Database Management and Organization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56813251550346895d98cde3/html5/thumbnails/5.jpg)
Example Application
Database: dbmusicTable: songs(ISBN, Title, SingerID, Length)
![Page 6: IT420: Database Management and Organization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56813251550346895d98cde3/html5/thumbnails/6.jpg)
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!
![Page 7: IT420: Database Management and Organization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56813251550346895d98cde3/html5/thumbnails/7.jpg)
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);
![Page 8: IT420: Database Management and Organization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56813251550346895d98cde3/html5/thumbnails/8.jpg)
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;
}
![Page 9: IT420: Database Management and Organization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56813251550346895d98cde3/html5/thumbnails/9.jpg)
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’);
![Page 10: IT420: Database Management and Organization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56813251550346895d98cde3/html5/thumbnails/10.jpg)
Query the Database
qresult mysql_query(query) Example:
$query = “select * from songs where Title like ‘%home%’ ”;
$results = mysql_query($query);
![Page 11: IT420: Database Management and Organization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56813251550346895d98cde3/html5/thumbnails/11.jpg)
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 />”;
}
![Page 12: IT420: Database Management and Organization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56813251550346895d98cde3/html5/thumbnails/12.jpg)
Check Modification Results
intvar = mysql_affected_rows() Used after INSERT, DELETE, UPDATE
![Page 13: IT420: Database Management and Organization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56813251550346895d98cde3/html5/thumbnails/13.jpg)
Disconnect from Database
Free query results mysql_free_result(qresult);
Close connection mysql_close(connection)
![Page 14: IT420: Database Management and Organization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56813251550346895d98cde3/html5/thumbnails/14.jpg)
VP-5 MVR Fund Raiser Application
![Page 15: IT420: Database Management and Organization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56813251550346895d98cde3/html5/thumbnails/15.jpg)
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
![Page 16: IT420: Database Management and Organization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56813251550346895d98cde3/html5/thumbnails/16.jpg)
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.
![Page 17: IT420: Database Management and Organization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56813251550346895d98cde3/html5/thumbnails/17.jpg)
Lab Exercise: PHP + MySQL
Save order data from VP-5 Fund Raiser application into vp5fund database.
![Page 18: IT420: Database Management and Organization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56813251550346895d98cde3/html5/thumbnails/18.jpg)
Save Your Work!
Copy the D:\sokkit\mysql\data\ directory to your X drive