PHP RESTful Post

Page 8

MySQL Create, Read, Update, Delete

Introduction Connect Create: Add User Read: showTable() Update: setUser() Delete: removeTableRow() Summary

Introduction

This section demonstrates the basics of PHP with MySQL, including create, read, update and delete (CRUD). Learn to connect to a database and execute MySQL queries on a table in the database. Learn to display MySQL rows in an HTML Web table.

When you're done with this page, you've completed the tutorial that starts on page one, RESTful Post: Overview. You've seen a simplified, yet full process for RESTful Web development, from preparing a Web form, creating a MySQL database, inserting new users, updating rows, deleting rows in the database, and finally displaying the database to a Web page's table.

Connect: MySQL Connection

Function getSQL() is the main user defined function for MySQL access. Function getSQL() is defined in functions.php. Function getSQL() calls the following user defined functions:

setUser(), getSQLQuery() and showTable(). Each function will be covered later on this page.

Parameters

Function getSQL() has one parameter named $tableName. Parameter $tableName is a string with the name of the table we want to access. In this tutorial, we're accessing TestColors.

function getSQL($tableName)

Global Variables

Function getSQL() accesses global variables that were delcared in your file base-data.php, described earlier. The values for the variables declared in base-data.php, should remain hidden for security purposes. functions.php includes base-data.php, as follows.

include ('base-data.php');

The variables declared in base-data.php, include $hostname,$username,$password and $dbname. Apply the access modifier, global, inside getSQL() as follows.

global $hostname,$username,$password,$dbname;

Local Variables

Declare three local variables. Variable $con will be our MySQL database connection. Variable $sQuery receives SQL statements, in string format. Variable $sqlResult should contain zero or more rows of data from our MySQL database, after a SQL statement executes.

$con = null;
$sQuery = null;
$sqlResult = null;

The following line of code, in getSQL(), calls a built-in PHP MySQL function named, mysqli_connect(..), which connects to our MySQL database. Pass variable values stored in our hidden base-data.php file. The term OR DIE('...') displays an error message, then exits the application if the connection fails. Otherwise you should have a valid MySQL database connection.

$con = mysqli_connect($hostname,$username, $password) OR DIE ('Unable to connect to database! Please try again later.');

Built-in function mysqli_select_db(...), selects the database that is also declared in your file base-data.php. The variable $dbname contains the name of your database, in string format.

mysqli_select_db($con,$dbname);

Next, user defined function, setUser(), adds a row, to the database, with data for new users. If the user's not new, then setUser() updates their data. Function setUser() is defined later in this page.

Our first SQL query is assigned to $sQuery, as follows. The query will return every row in our table, TestColors.

$sQuery = "SELECT * FROM " . $tableName;

Remember that PHP file post-colors.php calls getSQL("TestColors");. The only parameter is the name to the table in our database, that we prepared earlier in this tutorial.

User defined function showTable(), displays the MySQL data in an HTML table. Function showTable() is explained later in this page.

showTable($sqlResult);

Most important, remember to close your database connection. Leaving files and database connections open can cause wasted resources, CPU, memory, and more.

mysqli_close($con);

The entire getSQL(...) function follows. The only parameter is the name of the database's table, in string format, that you want to access.

function getSQL($tableName){
 global $hostname,$username,$password,$dbname;
 $con = null;
 $sQuery = null;
 $sqlResult = null;
 
 // Connect to the database or exit:
 $con = mysqli_connect($hostname,$username, $password) OR DIE ('Unable to connect to database! Please try again later.');
 
 // Select our database:
 mysqli_select_db($con,$dbname);
 
 // Add or update this user
 // in the table:
 setUser($con,$tableName);
 
 $sQuery = "SELECT * FROM " . $tableName;
 // Get all the rows from the table.
 $sqlResult =  getSQLQuery($con,$sQuery);
 
 showTable($sqlResult);
 
 mysqli_close($con);
}

Update: setUser()

User defined function, setUser(), adds this user to the database, if they don't already have an entry in the database. If this user already entered values, then update the user's information. If we're displaying the Web page, without posting data, then just exit setUser().

Function setUser(), requires two parameters. The first parameter, $con, is a connection to the MySQL database. The second parameter, $tableName, is the name of the MySQL table, in string format.

Create a query that accesses the row from the database that has the same user name and password.

$sQuery = "SELECT * FROM " . $tableName . " Where user = '". $sUser . "' AND pwd = '" . $sPwd . "'";

Get the result of the MySQL query and store the result in the variable, $sResult.

$sResult = getSQLQuery($con, $sQuery);

Check to determine if this user and password were already in the database. A result may be returned from the query but if the number of rows are zero, then this is a new user.

if($sResult != null){
 $iRowCount = mysqli_num_rows($sResult); 
 if($iRowCount >= 1){...

If the user was in the database, then create a SQL statement to update this user to the database.

$sQuery = "UPDATE " . $tableName ." SET data = '" . $sData . "' WHERE user = '" . $sUser ."'";

Create: Add User

If the user wasn't in the database, then create a SQL statement to add this user's data, as follows.

$sQuery="INSERT INTO " . $tableName . " (user, pwd, data) VALUES ('" . $sUser ." ','" . $sPwd . "','" . $sData . "')";

Execute the statement, whether it's an UPDATE or an INSERT statement.

getSQLQuery($con, $sQuery);

Now get all the updated or inserted data from the table, as follows.

$sQuery = "SELECT * FROM " . $tableName;
$sResult = getSQLQuery($con, $sQuery);

Last call removeTableRow(), which is covered later, on this page. The entire setUser() function follows.

/**
 * Either add this user to the database,
 * or if the same user and password exists,
 * then update the data.
 * @param MySQL Database Connection $con
 * @param string name of table in the Database $tableName
 */
function setUser($con,$tableName){
 global $sUser,$sPwd,$sData;
 // The Web table displays
 // if there is no posted data.
 // Therefore don't process a user.
 if($sUser == null || $sUser == ""){
  return;
 }
 $sQuery = "SELECT * FROM " . $tableName . " Where user = '". $sUser . "' AND pwd = '" . $sPwd . "'";
 $sResult =  getSQLQuery($con, $sQuery); 
 $bNew = true;
 $iRowCount = 0;
 if($sResult != null){
   $iRowCount = mysqli_num_rows($sResult); 
   if($iRowCount >= 1){
    $bNew = false;
    $sQuery = "UPDATE " . $tableName ." SET data = '" . $sData . "' WHERE user = '" . $sUser ."'";
   }
  }
 if ($bNew == true){ 
   $sQuery="INSERT INTO " . $tableName . " (user, pwd, data) VALUES ('" . $sUser . "','" . $sPwd . "','" . $sData . "')";
 }
 
 // Insert or update:
 getSQLQuery($con, $sQuery);
 
 // Get entire table:
 $sQuery = "SELECT * FROM " . $tableName;
 $sResult =  getSQLQuery($con, $sQuery);
 
 // Remove any rows greater than MAX_ROWS.
 removeTableRow($con, $tableName, $sResult);
}

getSQLQuery()

The purpose of getSQLQuery() was to wrap repeated lines of code into one function. Function getSQLQuery() has two parameters. The first parameter, $con, is the SQL connection. The second parameter is a SQL statement, in string format.

I found the same few lines of code were repeated throughout this file. It's usually more efficient and easier to debug if everything that's reused resides in one place.

First call built-in function mysqli_query(). The returned value is stored in variable $rValue. If $rValue is null, then exit the program with an error message. Otherwise return the $rValue.

/**
 * Execute a query or exit this program.
 * @param SQL Connection $con
 * @param String $sQuery: SQL Query.
 * @return result of SQL query
 * null's never returned because this function
 * exits the program if the result's null.
 */
function getSQLQuery($con, $sQuery){
 // rows or an error value:
 $rValue = mysqli_query($con,$sQuery);
 if ($rValue == null){
  $sError = mysqli_error($con);
  echo ' error adding or updating: ' . $sError;
  // Exit the program
  // die(..) is an alias for exit(...)
  exit('Error: ' . $sError);
 }
 return $rValue;
}

Delete: removeTableRow()

Function setUser() calls removeTableRow(). This function tests to determine if your database rows have increased beyond the value you previously set in the constant, MAXROWS. If the number of rows exceeds MAXROWS then delete the first row.

You can set MAXROWS to a higher value. It's helpful to limit the size of a database. Very large volumes of Web users could overwhelm the amount of space you have available for databases.

Parameters to removeTableRow() include the SQL connection, $con, the table name in string format, $tableName, and $sR which is zero or more rows from the database.

Call built-in function, mysqli_num_rows(), to determine the number of rows in your result set. For this example, the result set is the entire table.

If the table has more rows than our constant MAXROWS, then just remove the top row. The entire function follows.

/**
 * Remove the top row in the table.
 * This allows new users to see their data
 * in the table, while also keeping the table's size down.
 * @param MySQL Connection $con
 * @param Database Table $tableName
 */
function removeTableRow($con, $tableName, $sR){
 $sResult = $sR;
 $iRowCount = mysqli_num_rows($sResult); 
 $r = null;
 $id = null;
 $sQuery = null;
 $idTop = 1;
 if($iRowCount > MAXROWS){
  // Get first row:
  $r = mysqli_fetch_array($sResult);
  // Get first id
  $idTop = $r['id'];
  // Delete the top row.
  $sQuery = "DELETE FROM " .$tableName. " WHERE id = " . $idTop;
  $sResult =  getSQLQuery($con, $sQuery);
 }
}

Read: showTable()

Show the database to Web users, with showTable(). Use echo '<table... to start your table

Iterate over every row in your result set, as follows.

while($row = mysqli_fetch_array($result)){..

Access the value for each entry in the row. Use the names you assigned when creating your MySQL table, as explained earlier in this tutorial. We created a user, data and id field for each column.

$name = $row['user'];
$data = $row['data'];
$id = $row['id'];

Show each row with the user name, color data, and auto incremented id.

echo '<tr><td>' . $id . '</td><td>' . $name.'</td><td>'. $data . '</td></tr>';

The entire function, showTable(), follows.

function showTable($result){ 
 echo'<table><tr><th>id</th><th style="background-color:#0000aa;">Name</th><th>Color</th></tr>';
 if($result) {
  while($row = mysqli_fetch_array($result)){
   $name = $row['user'];
   $data = $row['data'];
   $id = $row['id'];
   echo '<tr><td>' . $id . '</td><td>' . $name.'</td><td>'. $data . '</td></tr>';
  }
 }
 echo'</table>';
}

functions.php

The entire PHP file, functions.php, follows.

<?php
include ('base-data.php');
define("MAXROWS", 10);
// Globally declared variables.
// Variables beginning with $s used here
// to represent strings.
$sUser = null;
$sPwd = null;
$sData = null;

/**
 * Render a Web page's header.
 * @param string $sTitle: Web page title.
 * @param string $sICO:   Path to Web page icon.
 * @param string $sCSS:   Path to style sheet.
 */
function showHeader($sTitle, $sICO, $sCSS){
 echo' <!DOCTYPE html>';
 echo' <html lang="en-US"><head><title>' . $sTitle . '</title>';
 echo' <meta name="description" content="Score board for the ' . $sTitle . '. Shows the top ten scores."/>';
 echo' <meta http-equiv="content-type" content="text/html; charset=utf-8" />';
 echo' <meta name="viewport" content="width=device-width, initial-scale=1">';
 echo'<style> ';
 echo' @import url( ' . $sCSS . ');';
 echo'</style>';
 echo' <link rel="shortcut icon" href="' . $sICO . '" />';
 echo '</head>';
 echo' <body>';
 echo '<nav><a class="text" href="https://code.7Thunders.biz/index.php" title="Home">Home</a></nav>';
 echo '<div class="content">';
 echo'<h1>'. $sTitle .'</h1>';
}

/**
 * Obtain the data posted
 * from the Web form.
 * Note 'user', 'pwd' and 'data' are
 * ALSO the values of each name field in your Web form:
 * <input type="text" name="user"  value="user"/>
 */
function getPostedData(){
 // Access global variables here:
 global $sUser,$sPwd,$sData;
 $sUser = $_POST['user'];
 $sPwd = $_POST['pwd'];
 $sData = $_POST['data'];
 // var_dump() is useful while debugging:
 // echo var_dump($_POST);
}

/**
 * Display to the user the
 * data that they just input into a form.
 * The user name, password and data are global.
 */
function showData(){
 // Access global variables here:
 global $sUser,$sPwd,$sData;
 if($sUser != null){
  echo '<h2>Welcome: ' . $sUser . '</h2>';
  echo '<p>User: ' . $sUser . ', data: ' . $sData .'</p>';
 }
 else{
  echo '<h2>Welcome</h2>';
  echo '<p>See <a href="https://code.7Thunders.biz/ph/topics-php.php" title="PHP & MySQL">PHP & MySQL</a> scripts.';
 }
}

/**
 * Render a Web page's footer.
 * @param string $sReturnPath: Apply to hyperlink
 * to return to the original application.
 */
function showFooter($sReturnPath){
 echo'</div>';
 echo'<footer><a class="text" href="' . $sReturnPath . '" title="' . $sReturnPath . '">Return to: '. $sReturnPath . '</a></footer>';
}

function showTable($result){ 
 echo'<table><tr><th>id</th><th style="background-color:#0000aa;">Name</th><th>Color</th></tr>';
 if($result) {
  while($row = mysqli_fetch_array($result)){
   $name = $row['user'];
   $data = $row['data'];
   $id = $row['id'];
   echo '<tr><td>' . $id . '</td><td>' . $name.'</td><td>'. $data . '</td></tr>';
  }
 }
 echo'</table>';
}

/**
 * Either add this user to the database,
 * or if the same user and password exists,
 * then update the data.
 * @param MySQL Database Connection $con
 * @param string name of table in the Database $tableName
 */
function setUser($con,$tableName){
 global $sUser,$sPwd,$sData;
 
 // The Web table displays
 // if there is no posted data.
 // Therefore don't process a user,
 // if a user name wasn't posted.
 if($sUser == null || $sUser == ""){
  return;
 }
 
 $sQuery = "SELECT * FROM " . $tableName . " Where user = '". $sUser . "' AND pwd = '" . $sPwd . "'";
 $sResult =  getSQLQuery($con, $sQuery); 
 $bNew = true;
 $iRowCount = 0;
 if($sResult != null){
   $iRowCount = mysqli_num_rows($sResult); 
   if($iRowCount >= 1){
    $bNew = false;
    $sQuery = "UPDATE " . $tableName ." SET data = '" . $sData . "' WHERE user = '" . $sUser ."'";
   }
  }
 if ($bNew == true){ 
   $sQuery="INSERT INTO " . $tableName . " (user, pwd, data) VALUES ('" . $sUser . "','" . $sPwd . "','" . $sData . "')";
 }
 
 // Insert or update:
 getSQLQuery($con, $sQuery);
 
 // Get entire table:
 $sQuery = "SELECT * FROM " . $tableName;
 $sResult =  getSQLQuery($con, $sQuery);
 
 // Remove any rows greater than MAX_ROWS.
 removeTableRow($con, $tableName, $sResult);
}

/**
 * Remove the top row in the table.
 * This allows new users to see their data
 * in the table, while also keeping the table's size down.
 * @param MySQL Connection $con
 * @param Database Table $tableName
 */
function removeTableRow($con, $tableName, $sR){
 $sResult = $sR;
 $iRowCount = mysqli_num_rows($sResult); 
 $r = null;
 $id = null;
 $sQuery = null;
 $idTop = 1;
 if($iRowCount > MAXROWS){
  // Get first row:
  $r = mysqli_fetch_array($sResult);
  // Get first id
  $idTop = $r['id'];
  // Delete the top row.
  $sQuery = "DELETE FROM " .$tableName. " WHERE id = " . $idTop;
  $sResult =  getSQLQuery($con, $sQuery);
 }
}

/**
 * Execute a query or exit this program.
 * @param SQL Connection $con
 * @param String $sQuery: SQL Query.
 * @return result of SQL query
 * null's never returned because this function
 * exits the program if the result's null.
 */
function getSQLQuery($con, $sQuery){
 // rows or an error value:
 $rValue = mysqli_query($con,$sQuery);
 if ($rValue == null){
  $sError = mysqli_error($con);
  echo ' error adding or updating: ' . $sError;
  // Exit the program
  // die(..) is an alias for exit(...)
  exit('Error: ' . $sError);
 }
 return $rValue;
}
 
function getSQL($tableName){
 global $sUser,$sPwd,$sData;
 global $hostname,$username,$password,$dbname;
 $con = null;
 $sQuery = null;
 $sqlResult = null;
 
 // Connect to the database or exit:
 $con = mysqli_connect($hostname,$username, $password) OR DIE ('Unable to connect to database! Please try again later.');
 
 // Select our database:
 mysqli_select_db($con,$dbname);
 
 // Add or update this user
 // in the table:
 setUser($con,$tableName);
 
 $sQuery = "SELECT * FROM " . $tableName;
 // Get all the rows from the table.
 $sqlResult =  getSQLQuery($con,$sQuery);
 
 showTable($sqlResult);
 
 mysqli_close($con);
}
?>

Summary

This section demonstrated the basics of PHP with MySQL, including create, read, update and delete (CRUD). You learned to connect to a database and execute MySQL queries on a table in the database. You learned to display MySQL rows in an HTML Web table.

You've completed the tutorial that starts on page one, RESTful Post: Overview. You've seen a simplified, yet full process for RESTful Web development, from preparing a Web form, creating a MySQL database, inserting new users, updating rows, deleting rows in the database, and finally displaying the database to a Web page's table.