1

Internet Programming

Connecting PHP to a Database

2

Objectives

To construct programs that interact with MySQL database.

TAB1033 - Internet Programming 3

How they are connected?

4

MySQL

A Relational Database with a license, allowing users to use it cost-free for most non-commercial purposes.A database choice for PHP developers.Conceived as fast and flexible DBMS.

5

Getting a Connection

Connect to any server that have permission to use.The mysql_connect function arranges the communication link between MySQL and PHP.The mysql_connect() function requires three parameters: Server name : name or URL of the MySQL

server Username : The username in MySql. Password : password identified by username

6

Steps for Accessing MySQL Database

1.Open a connection to MySQL

2.Select a particular database

$connection = mysql_connect(host,username, password);

$db = mysql_select_db( database_name , $connection );

$connection = mysql_connect("160.0.58.165", "stud0011", "iiiiiiii");

$db = mysql_select_db("test" , $connection );

7

Steps for Accessing MySQL Database (cont.)3. Access the tables in the database

as required a. Create an SQL command string

containing your query.

$sql = "sql_command parameters";

$sql = "SELECT * FROM contact";

8

Steps for Accessing MySQL Database (cont.)

b. Execute the query

c. Deal with the result of the query

$result = mysql_query( $sql , $connection ) or

die("Couldn't execute query");

while($row = mysql_fetch_array($result)){ ... }

"SELECT * FROM contact";

9

Creating an SQL TableA bit tedious processEasier if you could use phpMyAdmin

CREATE TABLE table_name(

fieldname1 data_type(size);fieldname2 data_type(size);fieldname3 data_type(size);...fieldname-n data_type(size);

);

10

SQL CommandsThe main day-to-day queries used from PHP scripts: INSERT INTO tablename ... VALUES ... ; REPLACE INTO tablename ... VALUES ... ; DELETE FROM tablename WHERE ... ; UPDATE tablename SET ... WHERE ... ; SELECT ... FROM tablename WHERE ... ORDER BY... ;

11

SQL Commands (cont.)When writing SQL commands into PHP scripts the usual problem of too many quotes arises and 'internal' quotes (") have to be escaped. SQL query must finish with a semicolon, as must a PHP script command.

12

Examples of Built-In MySQL Functions

Returns an array that represents all the fields for a row in the result set.

Eg. $row = mysql_fetch_row($result)

Moves the internal row pointer of a result to the specified row, with rows counting from zero.

Use this function with mysql_fetch_row to jump to a specific row.

Eg. $row = mysql_data_seek($result, 3)

array mysql_fetch_row(resource result)

boolean mysql_data_seek(resource result, integer row)

13

Built-In MySQL Functionsarray mysql_fetch_array(resource result,integer type)

Returns an array that represents all the fields for a row in the result set.Value is stored twice: Once indexed by offset starting from zero, once indexed by the name of the field.Eg. $row = mysql_fetch_array($result, 5)

array mysql_fetch_assoc(resource result)

Returns an array that represents all the fields for a row in the result set.Element is indexed by the names of the field onlyEg. $row = mysql_fetch_assoc($result)

14

Examples

To create a web page that linked to sale items record from a database.Step 1: Create the table named catalog in a databaseCREATE TABLE catalog(

ID INT(11) NOT NULL AUTO_INCREMENT,Name CHAR(32),Price DECIMAL(6,2),

PRIMARY KEY (ID));

15

ExampleStep 2 : Insert some items along with some dummy prices.

INSERT INTO catalog (Name, Price) VALUES(' Shampoo ' , 6.50),(' Brush ', 2.00),(' Conditioner ', 7.00),(' Toothbrush ', 3.50),(' Dental Floss ', 5.00);

SELECT * FROM catalog

16

ExampleStep 3: Write a PHP script to retrieve the data from table and display them in HTML table<?php //connect to server, then test for failure

if(!($dbLink = mysql_connect(" localhost ", " user1", ""))){

print("Failed to connect to database!<br>\n");print("Aborting!<br>\n");exit();

}

//select database then test for failureif (!($dbResult = mysql_query("USE test", $dbLink))){

print("Cannot use the test database!<br>\n");print("Aborting!<br>\n");exit();

}

Database connection

No password

query

17

Example

Creating HTML table from a query (cont.)

//get eveything from catalog table$Query = "SELECT Name, Price " . "FROM catalog " . "ORDER BY Name ";if(!($dbResult = mysql_query($Query, $dbLink))){

print("Could not execute query!<br>\n");print("MySQL reports: " . mysql_error() . !<br>\n");print("Query was: $Query<br>\n");exit();

}

TAB1033 - Internet Programming 18

ExampleCreating HTML table from a query (cont.)

//start tableprint("<table border = \"0\ ">\n");

//create header rowprint("<tr>\n");print("<td><b>Item</b></td>\n");print("<td><b>Price</b></td>\n");print("</tr>\n");

//get each rowwhile ($dbRow = mysql_fetch_assoc($dbResult)){

print("<tr>\n");print("<td>{$dbRow['Name']}</td>\n");print("<td align=\right\">{$dbRow['Price']}</td>\n");

print("</tr>\n");}

//end tableprint("</table>\n");

?>

Fetch each row from the result set. Expresses each column in the result as an element of an associative array.

Field name

19

Output

20

Drop Table

DROP TABLE IF EXIST catalog;

21

Changing Data With the Update Statement

UPDATE catalogSET price = 10.00 WHERE name = 'Shampoo';

UPDATE table_nameSET fieldname1 = value WHERE fieldname2 = value;

22

Delete Data?

DELETE FROM catalogWHERE name = 'Shampoo' AND price < 7.00 ;

DELETE FROM table_name WHERE fieldname2 = value;

23

SummaryDatabase Connection For example:

Host: 160.0.58.165 Username:stud9988 Password: mypass123 Database Name : db9988

Connection$connection = mysql_connect("160.0.58.165",

"stud9988", "mypass123");

24

SummarySelect database$db = mysql_select_db("db9988" , $connection );

Select data from table student in database db9988$query = "SELECT id, name FROM student WHERE id = ‘1234'";

$result = mysql_query($query,$connection) or die("Couldn't execute query");

The result (output)while($row = mysql_fetch_row($result)){

print($row[0]);//1234print($row[1]);//Ali

}

id name

1234 Ali

6789 Mary

student

25

Summary

Update data $query1 = "UPDATE student SET name = 'Ahmad' WHERE id = 1234";

• Delete data $query2 = "DELETE FROM student WHERE name =

'Mary'";

Drop table $query3 = "DROP TABLE IF EXIST student;";

id name

1234 Ali

6789 Mary

student