it420 database management and organization
Download
Skip this Video
Download Presentation
IT420: Database Management and Organization

Loading in 2 Seconds...

play fullscreen
1 / 18

IT420: Database Management and Organization - PowerPoint PPT Presentation


  • 250 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'IT420: Database Management and Organization' - Philip


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
it420 database management and organization

IT420: Database Management and Organization

PHP - MySQL Connection

8 March 2006

Adina Crainiceanu

www.cs.usna.edu/~adina

web database architecture
Web Database Architecture

HTTP

API

Database

Management

System

Web server with

PHP enabled

Client browser

goals today
Goals Today
  • Connect from PHP to MySQL
mysql
MySQL
  • Relational Database Management System
  • Free
  • Open source
  • Portable
  • High performance
  • Support available
example application
Example Application

Database: dbmusic

Table: songs(ISBN, Title, SingerID, Length)

use dbms from php
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
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].'
';

}

//close connection

mysql_free_result($results);

mysql_close($db);

connect to mysql
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
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
Query the Database
  • qresult mysql_query(query)
  • Example:

$query = “select * from songs where Title like ‘%home%’ ”;

$results = mysql_query($query);

process select results
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 “
”;

}

check modification results
Check Modification Results
  • intvar = mysql_affected_rows()
    • Used after INSERT, DELETE, UPDATE
disconnect from database
Disconnect from Database
  • Free query results
    • mysql_free_result(qresult);
  • Close connection
    • mysql_close(connection)
lab exercise
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
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 mysql17
Lab Exercise: PHP + MySQL
  • Save order data from VP-5 Fund Raiser application into vp5fund database.
save your work
Save Your Work!
  • Copy the D:\sokkit\mysql\data\ directory to your X drive
ad