PHP programming - MySql Interface
Frank Yap

MySql interface examples are shown in this section.
 
Resources

 
Lab Exercises
Exercise 1: Using MySql data base

In this section we will practice using MySql data base with PHP.
 
Start XAMPP Apache and MySql servers.
 
Download zip file customerdb.zip and extract the customerdb.php file.
Place the file in your project folder.
Display the customerdb.php page in your browser by using URL http://localhost/phplab/customerdb.php
 
You should get a display like this:
 
User IdeMailFirst NameLast NameStatus
johnsuijohnsemail@some.comJohnSmith
scarletsuiscarletsemail@some.comScarletBrown
marysuimarysemail@some.comMaryYoung

 
User IdeMailFirst NameLast NameStatus
johnsuijohnsemail@some.comJohnSmith
scarletsuiupdated@some.comupdatedBrown
marysuimarysemail@some.comMaryYoung

Study code how it generated the output.
The code creates data base, creates users data table, inserts users data, updates users data, and deletes data.
The overview of the code is shown below.
 
<?php 
 
class CustomersDb { 
public function initDb ($rebuild) {} 
public function insertUser ($userId, $passwd, $emailAddr, $firstName, $lastName, $status) {} 
public function updateUser ($userId, $paraArray) {} 
public function deleteUser ($userId) {} 
public function getUser ($userId) {} 
public function getAllUsers () {} 
public function insertDemo ($xmlData) {} 

 
----------------------- Execution Start --------------------------- 
 
$db = new CustomersDb; 
$rebuild = true; 
// if $rebuild then recreate else if data base does not exist create one 
$retArr = $db->initDb ($rebuild); 
 
if ($rebuild) { 
// insert data using XML string 
$retArr = $db->insertDemo($customersDbXml); 

 
// get customer list 
$retArr = $db->getAllUsers(); 
// display retrieved customer list 
 
// update a customer 
$userId = "scarletsui"; 
$retArr = $db->updateUser($userId, $paraArray); 
 
// get updated customer list 
$retArr = $db->getAllUsers(); 
// display retrieved customer list 
 
// delete a customer 
$userId = "marysui"; 
$retArr = $db->deleteUser($userId); 
 
// get updated customer list 
$retArr = $db->getAllUsers(); 
// display retrieved customer list 
 
?>
 
The full code is shown below.
 
 
------------------------------ code start -------------------------------
 
<?php 
 
$GLOBALS["host"] = "localhost"; 
$GLOBALS["username"] = "root"; 
$GLOBALS["password"] = ""; 
$GLOBALS["database"] = "customers"; // data base name 
 
 
// customers data table in XML format 
$customersDbXml = <<<xmlMark 
<CUSTOMER_LIST>  
<CUSTOMER_DATA>  
<USER_ID>johnsui</USER_ID>  
<PASSWD>*********</PASSWD>  
<EMAIL_ADDR>johnsemail@some.com</EMAIL_ADDR>  
<FIRST_NAME>John</FIRST_NAME>  
<LAST_NAME>Smith</LAST_NAME>  
<STATUS></STATUS>  
</CUSTOMER_DATA> 
<CUSTOMER_DATA>  
<USER_ID>scarletsui</USER_ID>  
<PASSWD>*********</PASSWD>  
<EMAIL_ADDR>scarletsemail@some.com</EMAIL_ADDR>  
<FIRST_NAME>Scarlet </FIRST_NAME>  
<LAST_NAME>Brown</LAST_NAME>  
<STATUS></STATUS>  
</CUSTOMER_DATA> 
<CUSTOMER_DATA>  
<USER_ID>marysui</USER_ID>  
<PASSWD>*********</PASSWD>  
<EMAIL_ADDR>marysemail@some.com</EMAIL_ADDR>  
<FIRST_NAME>Mary</FIRST_NAME>  
<LAST_NAME>Young</LAST_NAME>  
<STATUS></STATUS>  
</CUSTOMER_DATA> 
</CUSTOMER_LIST> 
xmlMark; 
 
 
 
class CustomersDb { 
 
// If $rebuild == true db will be re-built. 
// 
public function initDb ($rebuild) { 
$retArr = array ( 
"STATUS" => "true", 
); 
 
$db = $GLOBALS["database"]; 
 
$dbConn = @mysql_connect ($GLOBALS["host"], $GLOBALS["username"], $GLOBALS["password"]); 
if (!$dbConn) { 
$retArr ["STATUS"] = "false"; 
$retArr ["ERROR"] = "ERROR: DB Connection" . mysql_error(); 
return $retArr; 
} else { 
if ($rebuild) { 
if (mysql_query("DROP DATABASE IF EXISTS $db", $dbConn)) 
echo "DB dropped.. " . $db; 

 
$select_db = @mysql_select_db ($db, $dbConn); 
if ($select_db) // DB exists OK 
return $retArr; 
 
echo "DB does not exist... "; 
 
if (mysql_query("CREATE DATABASE $db", $dbConn)) { 
echo "DB database created "; 
} else { 
$retArr ["STATUS"] = "false"; 
$retArr ["ERROR"] = "ERROR: DB error creating database " . mysql_error(); 
return $retArr; 

 
$select_db = @mysql_select_db ($db, $dbConn); 
if (!$select_db) { 
$retArr ["STATUS"] = "false"; 
$retArr ["ERROR"] = "ERROR: DB could not connect " . mysql_error(); 
return $retArr; 

 
$sqlStr = 
"CREATE TABLE tbl_users ( 
id int NOT NULL AUTO_INCREMENT, 
userId varchar(32) NOT NULL, 
password varchar(32) NOT NULL, 
email varchar(128) NOT NULL, 
firstName varchar(32) NOT NULL, 
lastName varchar(32) NOT NULL, 
status varchar(255) NOT NULL DEFAULT '', 
create_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
PRIMARY KEY (id), 
UNIQUE KEY userId (userId) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8"; 
$result = mysql_query ($sqlStr, $dbConn); 
if (!$result) { 
$retArr ["STATUS"] = "false"; 
$retArr ["ERROR"] = "ERROR: DB invalid query " . mysql_error(); 
return $retArr; 
} else { 
echo "DB op success "; 



 
 
// Returns affected row number (inserted recordNum). 
// 
public function insertUser ($userId, $passwd, $emailAddr, $firstName, $lastName, $status) { 
$db = $GLOBALS["database"]; 
$retArr = array ( 
"STATUS" => "true", 
); 
 
$dbConn = @mysql_connect ($GLOBALS["host"], $GLOBALS["username"], $GLOBALS["password"]); 
if (!$dbConn) { 
$retArr ["STATUS"] = "false"; 
$retArr ["ERROR"] = "ERROR: DB Connection"; 
return $retArr; 
} else { 
$select_db = @mysql_select_db ($db, $dbConn); 
if (!$select_db) { 
$retArr ["STATUS"] = "false"; 
$retArr ["ERROR"] = "ERROR: DB Select"; 
return $retArr; 

 
$tbl = "tbl_users"; 
 
$sqlStr = // this is double quoted string, so variables will be expanded 
"INSERT INTO $tbl (userId, password, email, firstName, lastName, status) VALUES 
('$userId','$passwd','$emailAddr','$firstName','$lastName','$status')"; 
$result = mysql_query ($sqlStr, $dbConn); 
if (!$result) { 
$retArr ["STATUS"] = "false"; 
$retArr ["ERROR"] = "ERROR: Invalid Query " . mysql_error(); 
return $retArr; 

$recordNum = mysql_insert_id (); 
$retArr ["recordNum"] = $recordNum; 
return $retArr; 


 
 
// $userId selects row to be updated 
// 
public function updateUser ($userId, $paraArray) { 
$db = $GLOBALS["database"]; 
$retArr = array ( 
"STATUS" => "true", 
); 
 
$dbConn = @mysql_connect($GLOBALS["host"], $GLOBALS["username"], $GLOBALS["password"]); 
if (!$dbConn) { 
$retArr ["STATUS"] = "false"; 
$retArr ["ERROR"] = "ERROR: DB Connection"; 
return $retArr; 
} else { 
$select_db = @mysql_select_db($db, $dbConn); 
if (!$select_db) { 
$retArr ["STATUS"] = "false"; 
$retArr ["ERROR"] = "ERROR: DB Select"; 
return $retArr; 

 
$strSet = ""; 
$comma = ""; 
 
foreach ($paraArray as $key => $value) { 
if (is_string($value) === TRUE) { 
$strSet .= $comma. $key . " = '" . $value . "'"; 
} else { 
$strSet .= $comma. $key . ' = ' . $value; 

$comma = ","; 
}  
 
/* The above foreach block is equivalent to this commented out block. 
 
$para = 'password'; 
if (isset ($paraArray[$para])) { 
$paraVal = $paraArray[$para]; 
$strSet == ""? $comma="": $comma=","; 
$strSet .= $comma."$para='$paraVal'"; 

$para = 'email'; 
if (isset ($paraArray[$para])) { 
$paraVal = $paraArray[$para]; 
$strSet == ""? $comma="": $comma=","; 
$strSet .= $comma."$para='$paraVal'"; 

$para = 'firstName'; 
if (isset ($paraArray[$para])) { 
$paraVal = $paraArray[$para]; 
$strSet == ""? $comma="": $comma=","; 
$strSet .= $comma."$para='$paraVal'"; 

$para = 'lastName'; 
if (isset ($paraArray[$para])) { 
$paraVal = $paraArray[$para]; 
$strSet == ""? $comma="": $comma=","; 
$strSet .= $comma."$para='$paraVal'"; 

$para = 'status'; 
if (isset ($paraArray[$para])) { 
$paraVal = $paraArray[$para]; 
$strSet == ""? $comma="": $comma=","; 
$strSet .= $comma."$para='$paraVal'"; 

*/ 
 
$tbl = "tbl_users"; 
 
$sqlStr = 
"UPDATE $tbl SET $strSet WHERE userId='$userId'"; 
$result = mysql_query ($sqlStr, $dbConn); 
if (!$result) { 
$retArr ["STATUS"] = "false"; 
$retArr ["ERROR"] = "ERROR: Invalid Query"; 
return $retArr; 

return $retArr; 


 
 
public function deleteUser ($userId) { 
$db = $GLOBALS["database"]; 
$retArr = array ( 
"STATUS" => "true", 
); 
 
$dbConn = @mysql_connect($GLOBALS["host"], $GLOBALS["username"], $GLOBALS["password"]); 
if (!$dbConn) { 
$retArr ["STATUS"] = "false"; 
$retArr ["ERROR"] = "ERROR: DB Connection"; 
return $retArr; 
} else { 
$select_db = @mysql_select_db($db, $dbConn); 
if (!$select_db) { 
$retArr ["STATUS"] = "false"; 
$retArr ["ERROR"] = "ERROR: DB Select"; 
return $retArr; 

 
$tbl = "tbl_users"; 
 
$sqlStr = "DELETE FROM $tbl WHERE userId='$userId'"; 
 
$result = mysql_query($sqlStr, $dbConn); 
if (!$result) { 
$retArr ["STATUS"] = "false"; 
$retArr ["ERROR"] = "ERROR: Invalid Query " . mysql_error(); 
return $retArr; 

return $retArr; 


 
 
public function getUser ($userId) { 
$db = $GLOBALS["database"]; 
$retArr = array ( 
"STATUS" => "true", 
); 
 
$dbConn = @mysql_connect($GLOBALS["host"], $GLOBALS["username"], $GLOBALS["password"]); 
if (!$dbConn) { 
$retArr ["STATUS"] = "false"; 
$retArr ["ERROR"] = "ERROR: DB Connection"; 
return $retArr; 
} else { 
$select_db = @mysql_select_db($db, $dbConn); 
if (!$select_db) { 
$retArr ["STATUS"] = "false"; 
$retArr ["ERROR"] = "ERROR: DB Select"; 
return $retArr; 

 
$tbl = "tbl_users"; 
 
$sqlStr = "SELECT userId, password, email, firstName, lastName, status FROM $tbl WHERE userId='$userId'"; 
$result = mysql_query ($sqlStr, $dbConn); 
if (!$result) { 
$retArr ["STATUS"] = "false"; 
$retArr ["ERROR"] = "ERROR: Invalid Query " . mysql_error(); 
return $retArr; 
} else { 
$retArr ["queryResult"] = $result; 
return $retArr; 



 
 
public function getAllUsers () { 
$db = $GLOBALS["database"]; 
$retArr = array ( 
"STATUS" => "true", 
); 
 
$dbConn = @mysql_connect($GLOBALS["host"], $GLOBALS["username"], $GLOBALS["password"]); 
if (!$dbConn) { 
$retArr ["STATUS"] = "false"; 
$retArr ["ERROR"] = "ERROR: DB Connection"; 
return $retArr; 
} else { 
$select_db = @mysql_select_db($db, $dbConn); 
if (!$select_db) { 
$retArr ["STATUS"] = "false"; 
$retArr ["ERROR"] = "ERROR: DB Select"; 
return $retArr; 

 
$tbl = "tbl_users"; 
 
$sqlStr = "SELECT userId, password, email, firstName, lastName, status FROM $tbl"; 
$result = mysql_query($sqlStr, $dbConn); 
if (!$result) { 
$retArr ["STATUS"] = "false"; 
$retArr ["ERROR"] = "ERROR: Invalid Query " . mysql_error(); 
return $retArr; 
} else { 
$retArr ["queryResult"] = $result; 
return $retArr; 



 
 
 
public function insertDemo ($xmlData) { 
$retArr = array ( 
"STATUS" => "true", 
); 
 
$items = simplexml_load_string ($xmlData); // parse XML string 
 
foreach ($items->CUSTOMER_DATA as $item) { 
$userId = (string)$item->USER_ID; 
$passwd = (string)$item->PASSWD; 
$emailAddr = (string)$item->EMAIL_ADDR; 
$firstName = (string)$item->FIRST_NAME; 
$lastName = (string)$item->LAST_NAME; 
$status = (string)$item->STATUS; 
// insert one row 
$retArr = self::insertUser ($userId, $passwd, $emailAddr, $firstName, $lastName, $status); 
if ($retArr ["STATUS"] == "false") return $retArr; 

return $retArr; 


 
?> 
 
&nbsp;<br><br><br> 
----------------------- Execution Start --------------------------- 
&nbsp;<br><br> 
 
<?php 
// Execution Start 
 
echo "<b>-------------------- From database ----------------------</b><br>";  
echo "<br><br>";  
 
$db = new CustomersDb; 
 
//ob_start(); // if we want to capture the echo-ed outputs from the called function -- 
 
//$rebuild = false; // set false or true 
$rebuild = true; 
// if data base does not exist create one 
$retArr = $db->initDb ($rebuild); 
 
//ob_get_clean(); // -- and throw away 
 
echo "<br><br><br>"; 
 
$status = $retArr ["STATUS"]; 
if ($status == "false") { // error 
$err = $retArr ["ERROR"]; 
$resp = "<XMLDATA>"."<STATUS>false</STATUS>"."<ERROR>".$err."</ERROR>"."</XMLDATA>"; 
echo $resp; 
return; 

 
if ($rebuild) { 
// insert data using XML string 
$retArr = $db->insertDemo($customersDbXml); 
 
$status = $retArr ["STATUS"]; 
if ($status == "false") { // error 
$err = $retArr ["ERROR"]; 
$resp = "<XMLDATA>"."<STATUS>false</STATUS>"."<ERROR>".$err."</ERROR>"."</XMLDATA>"; 
echo $resp; 
return; 


 
 
// get customer list 
$retArr = $db->getAllUsers(); 
 
$status = $retArr ["STATUS"]; 
if ($status == "false") { // error 
$err = $retArr ["ERROR"]; 
$resp = "<XMLDATA>"."<STATUS>false</STATUS>"."<ERROR>".$err."</ERROR>"."</XMLDATA>"; 
echo $resp; 
return; 

 
// display retrieved customer list 
echo "Data base created<br><br>"; 
 
echo "<table>"; 
echo "<td>User Id</td><td>eMail</td><td>First Name&nbsp;&nbsp;</td><td>Last Name&nbsp;&nbsp;</td><td>Status</td>"; 
 
$result = $retArr ["queryResult"]; 
while ($row = mysql_fetch_array($result)){ 
$userId = $row['userId']; 
$email = $row['email']; 
$firstName = $row['firstName']; 
$lastName = $row['lastName']; 
$status = $row['status']; 
 
echo "<tr>"; 
echo "<td>$userId</td><td>$email</td><td>$firstName</td><td>$lastName</td><td>$status</td>"; 
echo "</tr>"; 

echo "</table>"; 
 
echo "<br><br>"; 
 
// update a customer 
$userId = "scarletsui"; 
$paraArray = array(); 
$paraArray ['email'] = 'updated@some.com'; 
$paraArray ['firstName'] = 'updated'; 
 
$retArr = $db->updateUser($userId, $paraArray); 
 
$status = $retArr ["STATUS"]; 
if ($status == "false") { // error 
$err = $retArr ["ERROR"]; 
$resp = "<XMLDATA>"."<STATUS>false</STATUS>"."<ERROR>".$err."</ERROR>"."</XMLDATA>"; 
echo $resp; 
return; 

 
 
// get updated customer list 
$retArr = $db->getAllUsers(); 
 
$status = $retArr ["STATUS"]; 
if ($status == "false") { // error 
$err = $retArr ["ERROR"]; 
$resp = "<XMLDATA>"."<STATUS>false</STATUS>"."<ERROR>".$err."</ERROR>"."</XMLDATA>"; 
echo $resp; 
return; 

 
// display retrieved customer list 
echo "Data base updated<br><br>"; 
 
echo "<table>"; 
echo "<td>User Id</td><td>eMail</td><td>First Name&nbsp;&nbsp;</td><td>Last Name&nbsp;&nbsp;</td><td>Status</td>"; 
 
$result = $retArr ["queryResult"]; 
while ($row = mysql_fetch_array($result)){ 
$userId = $row['userId']; 
$email = $row['email']; 
$firstName = $row['firstName']; 
$lastName = $row['lastName']; 
$status = $row['status']; 
 
echo "<tr>"; 
echo "<td>$userId</td><td>$email</td><td>$firstName</td><td>$lastName</td><td>$status</td>"; 
echo "</tr>"; 

echo "</table>"; 
 
 
echo "<br><br>"; 
 
// delete a customer 
$userId = "marysui"; 
 
$retArr = $db->deleteUser($userId); 
 
$status = $retArr ["STATUS"]; 
if ($status == "false") { // error 
$err = $retArr ["ERROR"]; 
$resp = "<XMLDATA>"."<STATUS>false</STATUS>"."<ERROR>".$err."</ERROR>"."</XMLDATA>"; 
echo $resp; 
return; 

 
 
// get updated customer list 
$retArr = $db->getAllUsers(); 
 
$status = $retArr ["STATUS"]; 
if ($status == "false") { // error 
$err = $retArr ["ERROR"]; 
$resp = "<XMLDATA>"."<STATUS>false</STATUS>"."<ERROR>".$err."</ERROR>"."</XMLDATA>"; 
echo $resp; 
return; 

 
// display retrieved customer list 
echo "Data record deleted<br><br>"; 
 
echo "<table>"; 
echo "<td>User Id</td><td>eMail</td><td>First Name&nbsp;&nbsp;</td><td>Last Name&nbsp;&nbsp;</td><td>Status</td>"; 
 
$result = $retArr ["queryResult"]; 
while ($row = mysql_fetch_array($result)){ 
$userId = $row['userId']; 
$email = $row['email']; 
$firstName = $row['firstName']; 
$lastName = $row['lastName']; 
$status = $row['status']; 
 
echo "<tr>"; 
echo "<td>$userId</td><td>$email</td><td>$firstName</td><td>$lastName</td><td>$status</td>"; 
echo "</tr>"; 

echo "</table>"; 
 
echo "<br><br>"; 
?>
------------------------------ code end -------------------------------
 
 
Exercise 2: Utilizing phpMyAdmin program

XAMPP's phpMyAdmin program can display and manage MySql databases. While you are developing database phpMyAdmin is a great aid for examining and debugging your database.
Here is the procedure how you can open the program and utilize.
 
From XAMPP control panel click Admin... button.
Click phpMyAdmin menu item.
Click Databases menu item.
Find your database and click on it.
Now you can browse your database and manage it.

Homework Exercise

The above code is based on MySql.
Convert the code to either procedural or object oriented MySqlI based code.
Verify that the converted code works.
Use customerdb.php file as the original code.
 
Informations could be found at the links below.
MySqlI overview:
http://www.php.net/manual/en/mysqli.overview.php
MySqlI function summary:
http://www.php.net/manual/en/mysqli.summary.php
 
The syntax of MySql and procedural MySqlI are virtually identical, except the arguments are ordered differently.
 
Submit the code.