It420 database management and organization
Download
1 / 18

IT420: Database Management and Organization - PowerPoint PPT Presentation


  • 247 Views
  • Updated 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 l.jpg

IT420: Database Management and Organization

PHP - MySQL Connection

8 March 2006

Adina Crainiceanu

www.cs.usna.edu/~adina


Web database architecture l.jpg
Web Database Architecture

HTTP

API

Database

Management

System

Web server with

PHP enabled

Client browser


Goals today l.jpg
Goals Today

  • Connect from PHP to MySQL


Mysql l.jpg
MySQL

  • Relational Database Management System

  • Free

  • Open source

  • Portable

  • High performance

  • Support available


Example application l.jpg
Example Application

Database: dbmusic

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


Use dbms from php l.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!


Example l.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 connection

mysql_free_result($results);

mysql_close($db);


Connect to mysql l.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;

    }


Select database to use l.jpg
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 l.jpg
Query the Database

  • qresult mysql_query(query)

  • Example:

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

    $results = mysql_query($query);


Process select results l.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 />”;

    }


Check modification results l.jpg
Check Modification Results

  • intvar = mysql_affected_rows()

    • Used after INSERT, DELETE, UPDATE


Disconnect from database l.jpg
Disconnect from Database

  • Free query results

    • mysql_free_result(qresult);

  • Close connection

    • mysql_close(connection)



Lab exercise l.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


Lab exercise php mysql l.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.


Lab exercise php mysql17 l.jpg
Lab Exercise: PHP + MySQL

  • Save order data from VP-5 Fund Raiser application into vp5fund database.


Save your work l.jpg
Save Your Work!

  • Copy the D:\sokkit\mysql\data\ directory to your X drive


ad