PHP and MySQL

This section will show you how to interact with MySQL database using PHP language. Beside the code below you need to know PHP's built-in functions for MySQL by access the following url:

http://www.php.net/manual/en/ref.mysql.php

Build a connection

<?php
//Connect to MySQL on localhost using parameter username & password
$conn = mysql_connect("localhost", "username", "password") 
                                   or die("Could not connect to MySQL");

//Select database name using connection retrieve from the previous line
mysql_select_db("DBName", $conn) or die("Could not select database");
?>

Retrieve data from database

//Create sql command

$sql = "SELECT username FROM users 
                    WHERE username='username' and password='password'";

//Query the database
$result = mysql_query($sql, $conn);

//get number of record found
$recordSelected = mysql_num_rows($result);

//Test the query result
if($result){
      //Retrieve username field value at row 0
      $username = mysql_result($result, 0, "username");
      ...
}

Add new record

The following code show how to build an insert statement and interact with MySQL, and how to retrieve the number of record inserted.

//create sql insert statement
$sql = "INSERT INTO users (username, password) VALUES('Ruby', 'r0012')";

//execute query
$result_id = mysql_query($sql, $conn);

//retrieve number of record added
$addedRecord = mysql_affected_rows($result_id);

Update record

The following code show how to build an update statement and interact with MySQL, and how to retrieve the number of record updated.

//create sql update statement
$sql = "UPDATE users SET password='rNewPwd' WHERE username='Ruby'";

//execute query
$result_id = mysql_query($sql, $conn);

//retrieve number of record updated
$updatedRecord = mysql_affected_rows($result_id);

Delete record

The following code show how to build an delete statement and interact with MySQL, and howto retrieve the number of record deleted.

//create sql delete statement
$sql = "DELETE FROM users WHERE username='Ruby'";

//execute query
$result_id = mysql_query($sql, $conn);

//retrieve number of record deleted
$deletedRecord = mysql_affected_rows($result_id);

Close connection

Don't forget the following line to make the code complete.

mysql_close($conn);