It420 database management and organization
Download
1 / 18

IT420: Database Management and Organization - PowerPoint PPT Presentation


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

IT420: Database Management and Organization

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

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

  • 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’) 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


ad
  • Login