Browse All Articles > PHP and MySQLi Table Maintenance
The CRUD Functions
CRUD, meaning "Create, Read, Update, Delete" is a common term to data base developers. It describes the essential functions of data base table maintenance. This article lays out a minimum example showing how PHP and MySQLi work together to create a table maintenance script. Of course you could use phpMyAdmin and get a lot more capability, but this is intended to be a teaching example showing the moving parts. It can also serve as a model for maintenance of any table, with the classic example being a personal address book. We will use just the names from our address book in this example, but it would be very easy to add other columns for email, phone numbers, etc.
The CRUD functions map to the SQL query commands more or less like this:
Create = INSERT
Read = SELECT
Update = UPDATE
Delete = DELETE
The HTTP Requests
In our scripts we will use both GET and POST request methods. HTTP protocols tell us that GET can only be used when there is no change in the data model, therefore we will only be able to use the GET method for scripts that use SELECT queries. The other three functions will require us to use POST method requests. The method we use will be identified in the HTML form tag, via the method attribute. If the method attribute is omitted, the assumption is GET. If the action attribute is omitted, browsers will submit the request to the current URL.
GET is considered both safe and idempotent. POST is neither.
Getting Started
In order for any software project to be successful, we must have a test data set. If you're wondering why, please read this other article, then come back here. Our first script creates the test data in the form of a small data base table with a list of names. Our table maintenance script(s) will be written to operate on a table that looks like this.
![Our little table]()
The script in the first code snippet below can be used to create the table shown here. You should be able to install it and run it on your own server to create the test data needed for the other scripts. Here is some annotation about the test data creation process.
Lines 2-3:
We set our script up so that we see all of the possible errors, warnings and notices. This is critically important in the development environment.
Lines 16-19:
You should be able to put your own credentials into these lines, install this script and run it without any other changes.
Lines 22-34:
This shows how to connect to the data base engine and select the named data base. It tests for connection errors and makes them visible. You might try putting in a bad password to see what the error handling does.
Line 37:
This destroys the table so that we can re-create it as many times as necessary. This gives us a consistent starting point for our tests. Since we will be performing "maintenance" on this table, its contents will change. If we set up a consistent set of tests, we would also want to set up a consistent starting point for the tests.
Lines 40-49:
This is the SQL query to CREATE the table.
Lines 52-67:
This is how to run a MySQLi query and test for success. If the query fails, we use trigger_error() to visualize the error information.
Line 70:
This is the iterator that goes through our test data iteratively and loads the table one row at a time.
Lines 73-74:
This escapes the data for safe use in the query string. Because one of our names has an apostrophe in it, the escape process is required to tell MySQLi that the apostrophe is not a control character, but is actually part of the data we want to insert. As a practical matter, every data element that is used in a MySQLi query string should be escaped.
Lines 77-80:
Creates the query string and runs the query.
Lines 83-95:
Tests for query success or failure and visualizes error information, if any.
Lines 98-99:
Reports the AUTO_INCREMENT key for each row of data that is inserted into our test data set.
CRUD is sometimes abbreviated CRUDL, giving a nod to the idea that we need to be able to list the data as well as manipulate it. This script will list the table. We can return to this script many times during our development to see what our programming has done to the data in the table. It will give us a screen like this.
![A list of data from our table]()
Lines 9-27:
Just like our CREATE script, this is our data base hook-up.
Lines 30-42:
This is our SELECT query. Notice that I did not use SELECT * but instead chose to list the names of the columns that I wanted to SELECT. SELECT ALL is an anti-practice and is to be avoided. Notice also that I constructed the query in several lines. The SQL interpreter does not care about this, and I find that when I need to change a query, this multi-line format makes changes easier and helps me minimize errors.
Lines 45-57:
This runs the SELECT query and visualizes any errors.
Lines 60-63:
This is the iterator that shows us the rows of the results set created by the SELECT query.
While it would be possible to create the entire table maintenance script in a single script file, I find it easier to think in terms of two scripts. One of the scripts handles the Create, Read, Update and List functions (I call this the "form" script). The other script handles Delete.
When we first run our form script we will see a screen like this
![The view, with a form to add new data]()
When we want to edit the contents of a row, we can click on the "update" link
![Updating a row]()
Just type our changes directly into the form, and click the "update" button
![Ready to update]()
And a new view with our changes will appear
![Update complete]()
Deleting a row is done by clicking the "delete" link
![Preparing to delete]()
The client gets a chance to confirm any deletions
![Confirming the delete action]()
And after each update, deletion or addition, our form script will give us a new list
![After the deletion, a new list]()
The Delete Script
Let's look at the delete script first. It is the simpler of the two scripts. Remembering that any changes to the data model require a POST-method request, the Delete script can be called with both GET and POST requests. The GET request uses the id in the URL. When called with the GET request, the Delete script presents us with the form to confirm the deletion and we can choose Yes or No. When we choose Yes, the Delete script makes a POST-method request to itself sending the id of the row to be deleted.
The first part of the script is like the others, connecting to the data base. The real work begins on line 30. If this is a GET-method request, the $_POST array will be empty, and we expect to find the id in the URL parameter.
Line 33:
We use ternary operator notation to get the id in the form of an integer, or if it's missing, we choose a zero. This is an acceptable default because no row of our table will ever have a key of zero.
Lines 34-50:
We run a query with LIMIT 1 to retrieve the single row that is to be deleted.
Lines 53-58:
If no row is found, there is nothing to do, so we go back to the "form" script. But if a row is found, we need to ask the client for confirmation before deleting.
Lines 61-70:
We use HEREDOC notation to create an HTML form containing the id in a hidden input control. This form has two submit buttons to give the client the choices. We echo the form to the browser output stream, and that completes our processing for GET-method requests.
Lines 75-83:
We look for the "signal" of Yes or No in the POST-method request. If the signal is anything but "Yes" we will redirect the browser back to the "form" script and will not do any deletion.
Line 86, et seq:
If we get here, the deletion has been confirmed. We use the id from the POST request in the DELETE query, then redirect the browser back to the "form" script.
The "form" script handles the other parts of the CRUDL process. This script can be called via either GET or POST methods. POST-methods are used to INSERT or UPDATE rows in the table. GET-methods are used to list the contents of the table, and optionally to expose an HTML form to receive client input for updates. The top of this script is the same as the others - establishing the link to the SQL engine and choosing the data base.
Line 30:
If this is a POST-method request, we can either INSERT a new row or UPDATE an existing row.
Line 33:
If the id is zero, that will be our signal to add a new row.
Lines 36-37:
Prepare the external data for use in a query.
Lines 40-54:
Creates the INSERT query and runs the query. That's the end of the "Create" process.
Line 57:
If the id is not zero, this is a signal to UPDATE the row.
Lines 60-62:
Prepares the external data for use in a query.
Lines 65-79:
Create and run the UPDATE query. This completes the "Update" process and also completes the POST-method processing.
Line 84:
If there is the "id" input in a GET-method request, we sanitize it by converting it to an integer. If there's nothing there, we set $id to zero. Because PHP is a loosely typed language, zero can have the same meaning as empty, FALSE, NULL, so it's convenient for our if() statements.
Line 85:
If there is a non-zero value in the $id variable, it's a signal that the client has clicked the update link. This means that we need to retrieve the row to be updated.
Lines: 87-100:
We create and run the query and test for errors. Notice that we did not use the MySQLI::real_escape_string() method on the $id variable before putting it into a query string. It would have done no harm to do so, but since we just created the variable a few lines above, and since it can only be an integer because of explicit typecasting, we are safe using the unescaped variable in this instance. Our SELECT query uses LIMIT 1 to prevent a table scan when we know that we only want one row.
Lines 103-108:
We will either find a row, or not find a row (unlikely if the client clicked one of the "update" links, but possible if there was a bookmark or a hand-typed URL). If we find a row, we copy the data into our variables that will be used later in creating the UPDATE forms. That completes the first part of the GET-method request processing, and we move on to list the table.
Lines 112-125:
We run a SELECT query without a LIMIT clause this time because we want all of the rows from the table.
Line 128:
We will create our web page as we process the results set by concatenating HTML and data to the $out variable.
Lines 131-137:
If there is a non-zero value in $id we will need an HTML form to handle the UPDATE request, so we start a form if it is needed.
Line 140:
We start a tabular display of the information. There will be one row of HTML for each row in the query results set.
Line 141:
We use the while() iterator to retrieve the rows of the query results set. We retrieve these in the form of an object instead of an array because the punctuation used in the the object notation is easier to get right -- no fiddly quotes and brackets are needed.
Lines 143-153:
These lines start one table row for each row from the query results set, and create the update and delete links for each of the rows. The signal to these linked scripts is the id= input in the URL.
Line 156:
This tests to see if the current row id matches the id of the row to be updated. This lets us create either a set of form input controls (158-160) or a simple listing of the data from the row (164).
Lines 166-169:
This ties off the table data and table row, and is the end of the display loop.
Lines 171-172:
This ties off the table, and if a form was opened, it closes the form, too.
Lines 174-183:
This creates the form we would use to add a new row to the table. Note that it uses a hidden control with id=0, telling the action script that the data is new, and not associated with a request to edit one of the existing rows.
It's easy to implement a sorting strategy when you're using data base queries with the ORDER BY clause. We can use dynamic query creation, a process that allows your PHP script to add or remove information from the SQL query string. By adding a URL parameter we can send a signal in the $_GET array that indicates the sort preference(s) and we can implement these preferences in the ORDER BY clause of the query. Although this article's example is very simple, one could envision something like "fdla" to signal a requirement for ORDER BY fname ASC, lname DESC.
![A sortable version of the maintenance script]()
The sortable script is identical to the previous script in every respect, except for four additional bits of functionality, all occurring after line 110.
Lines 111-115:
We determine the sort order by interpreting the o= parameter of the GET request. First we set a default value in $ord for use in the ORDER BY clause, then acquire the value of $_GET['o']. If the value matches one of our expectations, we may change the ORDER BY clause, otherwise we just ignore the incorrect input and use our default value. This variable becomes the dynamic part of our query on line 118.
Lines 148-155:
We create the table headers that contain the URL parameters to allow sorting.
Lines 161-162:
We add the sort signal to the URL parameter so that we preserve the requested sort order from one request to the next.
Line 194:
The action script name is changed to point to this Sortable version of the Form script.
There is one more thing -- a small revision to the Delete script. The Delete script uses header("Location") to redirect the client browser back to the appropriate Form script. It would make sense to change the Delete script at lines 56, 84, 105 so that the redirect command would bring the client back to this Sortable script. (Perhaps it would be worth abstracting the return URL into a variable so there would be only one line to change).
If you are the only person on earth who will be maintaining this table (or you have turned this administrative task over to a single individual) you probably don't need to worry about overlapping updates. But if more than one person has access to the tables, you probably want to learn about Transactions and Locking. Here is the risk that you must consider.
The nature of the client server architecture is important here. Let's imagine that two clients at two separate computers access the table for the purpose of making some changes. Client #1 runs the "form" script and gets a list of rows. Then Client #2 runs the same script and gets the same list of rows. Both Clients are looking at the same information on the screen, and at this point the server is disconnected, awaiting the next request. Let's say Client #1 deletes a row. The data model has changed, and Client #1 is now looking at the new information, but Client #2 is still looking at the old list of rows. Client #1 is satisfied with the changes. Then Client #2 tries to modify the row that Client #1 deleted. Since the id is not in the table any more, the modification will not occur -- the row is already gone. Pretty soon your phone will ring and it will be Client #2 saying that your web site is broken and it lost his data.
One strategy for dealing with this sort or issue would be to make a message digest from the results set at the time your script processes a GET-method request to list the contents of the table. To do this, concatenate the contents of all of the columns in all of rows of the results set together into one long string variable. At the end of the retrieval, make the md5() string and put the string into the PHP session. When your script processes a POST-method request to UPDATE the table or to DELETE from the table, LOCK the table, run a SELECT ALL query, make a new message digest, and compare it to the md5() string in the session. If there is a match, the client is working with the latest copy of the table and you can proceed with UPDATE or DELETE, followed by UNLOCK. If there is a mismatch, the table has changed since it was displayed to the client. UNLOCK the table, and show the client a new list of the table contents, along with a notice that the changes might have collided with a racing script from another client. LOCK and UNLOCK are considered somewhat ham-fisted and unsophisticated, but until your tables get fairly large, this strategy will work well.
No locking is needed for INSERT queries since they do not modify any of the existing rows of the table.
Summary
This article has shown a short, self-contained correct example that teaches how to expand on the simple PHP forms tutorial, by incorporating SQL table maintenance into the process. We can now perform all of the CRUDL functions across all of the fields of our data base table. With a little more creativity, we might be able to put this functionality behind an access controlled page so that we could allow a site administrator to change selected parts of the data model.
References and Recommended Reading
http://www.w3.org/TR/html401/interact/forms.html
http://php.net/manual/en/tutorial.forms.php
http://php.net/manual/en/mysqli.overview.php
http://php.net/manual/en/class.mysqli.php
http://php.net/manual/en/class.mysqli-result.php
http://php.net/manual/en/mysqli.construct.php
http://php.net/manual/en/mysqli.real-escape-string.php
http://php.net/manual/en/mysqli.query.php
http://php.net/manual/en/mysqli.errno.php
http://php.net/manual/en/mysqli.error.php
http://php.net/manual/en/mysqli.insert-id.php
http://php.net/manual/en/mysqli-result.num-rows.php
http://php.net/manual/en/mysqli-result.fetch-object.php
http://www.restapitutorial.com/lessons/httpmethods.html
Please give us your feedback!
If you found this article helpful, please click the "thumb's up" button below. Doing so lets the E-E community know what is valuable for E-E members and helps provide direction for future articles. If you have questions or comments, please add them. Thanks!
CRUD, meaning "Create, Read, Update, Delete" is a common term to data base developers. It describes the essential functions of data base table maintenance. This article lays out a minimum example showing how PHP and MySQLi work together to create a table maintenance script. Of course you could use phpMyAdmin and get a lot more capability, but this is intended to be a teaching example showing the moving parts. It can also serve as a model for maintenance of any table, with the classic example being a personal address book. We will use just the names from our address book in this example, but it would be very easy to add other columns for email, phone numbers, etc.
The CRUD functions map to the SQL query commands more or less like this:
Create = INSERT
Read = SELECT
Update = UPDATE
Delete = DELETE
The HTTP Requests
In our scripts we will use both GET and POST request methods. HTTP protocols tell us that GET can only be used when there is no change in the data model, therefore we will only be able to use the GET method for scripts that use SELECT queries. The other three functions will require us to use POST method requests. The method we use will be identified in the HTML form tag, via the method attribute. If the method attribute is omitted, the assumption is GET. If the action attribute is omitted, browsers will submit the request to the current URL.
GET is considered both safe and idempotent. POST is neither.
Getting Started
In order for any software project to be successful, we must have a test data set. If you're wondering why, please read this other article, then come back here. Our first script creates the test data in the form of a small data base table with a list of names. Our table maintenance script(s) will be written to operate on a table that looks like this.
The script in the first code snippet below can be used to create the table shown here. You should be able to install it and run it on your own server to create the test data needed for the other scripts. Here is some annotation about the test data creation process.
Lines 2-3:
We set our script up so that we see all of the possible errors, warnings and notices. This is critically important in the development environment.
Lines 16-19:
You should be able to put your own credentials into these lines, install this script and run it without any other changes.
Lines 22-34:
This shows how to connect to the data base engine and select the named data base. It tests for connection errors and makes them visible. You might try putting in a bad password to see what the error handling does.
Line 37:
This destroys the table so that we can re-create it as many times as necessary. This gives us a consistent starting point for our tests. Since we will be performing "maintenance" on this table, its contents will change. If we set up a consistent set of tests, we would also want to set up a consistent starting point for the tests.
Lines 40-49:
This is the SQL query to CREATE the table.
Lines 52-67:
This is how to run a MySQLi query and test for success. If the query fails, we use trigger_error() to visualize the error information.
Line 70:
This is the iterator that goes through our test data iteratively and loads the table one row at a time.
Lines 73-74:
This escapes the data for safe use in the query string. Because one of our names has an apostrophe in it, the escape process is required to tell MySQLi that the apostrophe is not a control character, but is actually part of the data we want to insert. As a practical matter, every data element that is used in a MySQLi query string should be escaped.
Lines 77-80:
Creates the query string and runs the query.
Lines 83-95:
Tests for query success or failure and visualizes error information, if any.
Lines 98-99:
Reports the AUTO_INCREMENT key for each row of data that is inserted into our test data set.
<?php // RAY_EE_table_maintenance_create.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
// THIS SCRIPT CREATES A TEST DATA SET
// CREATE AN ARRAY OF NAMES TO USE FOR TEST DATA
$test_names_arrays = array
( array( "fname" => "Walter" , "lname" => "Williams" )
, array( "fname" => "Ray" , "lname" => "Paseur" )
, array( "fname" => "Bill" , "lname" => "O'Reilly" )
)
;
// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";
// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);
// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
$err
= "CONNECT FAIL: "
. $mysqli->connect_errno
. ' '
. $mysqli->connect_error
;
trigger_error($err, E_USER_ERROR);
}
// ALLOW US TO RECREATE AND RELOAD FOR MUTIPLE TESTS
$mysqli->query('DROP TABLE EE_maintenance');
// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TABLE EE_maintenance
( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
, fname VARCHAR(24) NOT NULL DEFAULT ''
, lname VARCHAR(24) NOT NULL DEFAULT ''
)
"
;
// RUN THE QUERY TO CREATE THE TABLE
$res = $mysqli->query($sql);
// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
$err
= 'QUERY FAILURE:'
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
. ' QUERY: '
. $sql
;
trigger_error($err, E_USER_ERROR);
}
// LOADING OUR DATA INTO THE TABLE
foreach ($test_names_arrays as $person)
{
// ESCAPE THE DATA FOR SAFE USE IN A QUERY
$safe_fn = $mysqli->real_escape_string($person['fname']);
$safe_ln = $mysqli->real_escape_string($person['lname']);
// CONSTRUCT THE QUERY USING THE ESCAPED VARIABLES
$sql = "INSERT INTO EE_maintenance ( fname, lname ) VALUES ( '$safe_fn', '$safe_ln' )";
// RUN THE QUERY TO INSERT THE ROW
$res = $mysqli->query($sql);
// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
$err
= 'QUERY FAILURE:'
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
. ' QUERY: '
. $sql
;
trigger_error($err, E_USER_ERROR);
}
// GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED
$id = $mysqli->insert_id;
echo "<br>MySQLI INSERTED A ROW CONTAINING <b>$safe_fn $safe_ln</b> WITH AUTO_INCREMENT ID = $id" . PHP_EOL;
}CRUD is sometimes abbreviated CRUDL, giving a nod to the idea that we need to be able to list the data as well as manipulate it. This script will list the table. We can return to this script many times during our development to see what our programming has done to the data in the table. It will give us a screen like this.
Lines 9-27:
Just like our CREATE script, this is our data base hook-up.
Lines 30-42:
This is our SELECT query. Notice that I did not use SELECT * but instead chose to list the names of the columns that I wanted to SELECT. SELECT ALL is an anti-practice and is to be avoided. Notice also that I constructed the query in several lines. The SQL interpreter does not care about this, and I find that when I need to change a query, this multi-line format makes changes easier and helps me minimize errors.
Lines 45-57:
This runs the SELECT query and visualizes any errors.
Lines 60-63:
This is the iterator that shows us the rows of the results set created by the SELECT query.
<?php // RAY_EE_table_maintenance_list.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';
// THIS SCRIPT LISTS A TEST DATA SET
// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";
// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);
// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
$err
= "CONNECT FAIL: "
. $mysqli->connect_errno
. ' '
. $mysqli->connect_error
;
trigger_error($err, E_USER_ERROR);
}
// CREATING A QUERY TO LIST THE TEST DATA
$sql
=
"
SELECT
id
, fname
, lname
FROM
EE_maintenance
ORDER BY
id
"
;
// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res= $mysqli->query($sql))
{
$err
= 'QUERY FAILURE:'
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
. ' QUERY: '
. $sql
;
trigger_error($err, E_USER_ERROR);
}
// PRINT OUT THE ROWS OF THE TABLE
while ($row = $res->fetch_object())
{
print_r($row);
}While it would be possible to create the entire table maintenance script in a single script file, I find it easier to think in terms of two scripts. One of the scripts handles the Create, Read, Update and List functions (I call this the "form" script). The other script handles Delete.
When we first run our form script we will see a screen like this
When we want to edit the contents of a row, we can click on the "update" link
Just type our changes directly into the form, and click the "update" button
And a new view with our changes will appear
Deleting a row is done by clicking the "delete" link
The client gets a chance to confirm any deletions
And after each update, deletion or addition, our form script will give us a new list
The Delete Script
Let's look at the delete script first. It is the simpler of the two scripts. Remembering that any changes to the data model require a POST-method request, the Delete script can be called with both GET and POST requests. The GET request uses the id in the URL. When called with the GET request, the Delete script presents us with the form to confirm the deletion and we can choose Yes or No. When we choose Yes, the Delete script makes a POST-method request to itself sending the id of the row to be deleted.
The first part of the script is like the others, connecting to the data base. The real work begins on line 30. If this is a GET-method request, the $_POST array will be empty, and we expect to find the id in the URL parameter.
Line 33:
We use ternary operator notation to get the id in the form of an integer, or if it's missing, we choose a zero. This is an acceptable default because no row of our table will ever have a key of zero.
Lines 34-50:
We run a query with LIMIT 1 to retrieve the single row that is to be deleted.
Lines 53-58:
If no row is found, there is nothing to do, so we go back to the "form" script. But if a row is found, we need to ask the client for confirmation before deleting.
Lines 61-70:
We use HEREDOC notation to create an HTML form containing the id in a hidden input control. This form has two submit buttons to give the client the choices. We echo the form to the browser output stream, and that completes our processing for GET-method requests.
Lines 75-83:
We look for the "signal" of Yes or No in the POST-method request. If the signal is anything but "Yes" we will redirect the browser back to the "form" script and will not do any deletion.
Line 86, et seq:
If we get here, the deletion has been confirmed. We use the id from the POST request in the DELETE query, then redirect the browser back to the "form" script.
<?php // RAY_EE_table_maintenance_delete.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
// THIS SCRIPT LETS US DELETE A SINGLE ROW
// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";
// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);
// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
$err
= "CONNECT FAIL: "
. $mysqli->connect_errno
. ' '
. $mysqli->connect_error
;
trigger_error($err, E_USER_ERROR);
}
// IF THIS IS A GET-METHOD REQUEST
if (empty($_POST))
{
// GET INFORMATION TO PROMPT THE CLIENT TO CONFIRM THE DELETE
$id = isset($_GET['id']) ? (int)$_GET['id'] : 0;
$sql = "SELECT id, lname, fname FROM EE_maintenance WHERE id = $id LIMIT 1";
if (!$res= $mysqli->query($sql))
{
$err
= 'QUERY FAILURE:'
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
. ' QUERY: '
. $sql
;
trigger_error($err, E_USER_ERROR);
}
// EXPECT TO RETRIEVE ONE ROW
$row = $res->fetch_object();
// BUT IF THERE IS NO ROW, THERE IS NOTHING TO DELETE
if (!$row)
{
// REDIRECT THE BROWSER
header("Location: RAY_EE_table_maintenance_form.php");
exit;
}
// PUT UP THE CONFIRMATION FORM (HEREDOC SYNTAX)
$out = <<<EOD
<form method="post">
Do you really want to delete $row->fname $row->lname?
<input type="hidden" name="id" value="$id" />
<br>
<input type="submit" name="signal" value="Yes" />
<input type="submit" name="signal" value="No" />
</form>
EOD;
echo $out;
}
// IF THERE IS A POST-METHOD REQUEST, DELETE THE ROW
elseif (!empty($_POST['signal']))
{
// IF THE RESPONSE IS NOT YES
if ($_POST['signal'] != 'Yes')
{
// REDIRECT THE BROWSER
header("Location: RAY_EE_table_maintenance_form.php");
exit;
}
// CAPTURE THE ID
$id = (int)$_POST['id'];
// CREATE AND RUN THE DELETE QUERY, TEST FOR ERRORS
$sql = "DELETE FROM EE_maintenance WHERE id = $id LIMIT 1";
if (!$res= $mysqli->query($sql))
{
$err
= 'QUERY FAILURE:'
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
. ' QUERY: '
. $sql
;
trigger_error($err, E_USER_ERROR);
}
// REDIRECT THE BROWSER
header("Location: RAY_EE_table_maintenance_form.php");
exit;
}The "form" script handles the other parts of the CRUDL process. This script can be called via either GET or POST methods. POST-methods are used to INSERT or UPDATE rows in the table. GET-methods are used to list the contents of the table, and optionally to expose an HTML form to receive client input for updates. The top of this script is the same as the others - establishing the link to the SQL engine and choosing the data base.
Line 30:
If this is a POST-method request, we can either INSERT a new row or UPDATE an existing row.
Line 33:
If the id is zero, that will be our signal to add a new row.
Lines 36-37:
Prepare the external data for use in a query.
Lines 40-54:
Creates the INSERT query and runs the query. That's the end of the "Create" process.
Line 57:
If the id is not zero, this is a signal to UPDATE the row.
Lines 60-62:
Prepares the external data for use in a query.
Lines 65-79:
Create and run the UPDATE query. This completes the "Update" process and also completes the POST-method processing.
Line 84:
If there is the "id" input in a GET-method request, we sanitize it by converting it to an integer. If there's nothing there, we set $id to zero. Because PHP is a loosely typed language, zero can have the same meaning as empty, FALSE, NULL, so it's convenient for our if() statements.
Line 85:
If there is a non-zero value in the $id variable, it's a signal that the client has clicked the update link. This means that we need to retrieve the row to be updated.
Lines: 87-100:
We create and run the query and test for errors. Notice that we did not use the MySQLI::real_escape_string
Lines 103-108:
We will either find a row, or not find a row (unlikely if the client clicked one of the "update" links, but possible if there was a bookmark or a hand-typed URL). If we find a row, we copy the data into our variables that will be used later in creating the UPDATE forms. That completes the first part of the GET-method request processing, and we move on to list the table.
Lines 112-125:
We run a SELECT query without a LIMIT clause this time because we want all of the rows from the table.
Line 128:
We will create our web page as we process the results set by concatenating HTML and data to the $out variable.
Lines 131-137:
If there is a non-zero value in $id we will need an HTML form to handle the UPDATE request, so we start a form if it is needed.
Line 140:
We start a tabular display of the information. There will be one row of HTML for each row in the query results set.
Line 141:
We use the while() iterator to retrieve the rows of the query results set. We retrieve these in the form of an object instead of an array because the punctuation used in the the object notation is easier to get right -- no fiddly quotes and brackets are needed.
Lines 143-153:
These lines start one table row for each row from the query results set, and create the update and delete links for each of the rows. The signal to these linked scripts is the id= input in the URL.
Line 156:
This tests to see if the current row id matches the id of the row to be updated. This lets us create either a set of form input controls (158-160) or a simple listing of the data from the row (164).
Lines 166-169:
This ties off the table data and table row, and is the end of the display loop.
Lines 171-172:
This ties off the table, and if a form was opened, it closes the form, too.
Lines 174-183:
This creates the form we would use to add a new row to the table. Note that it uses a hidden control with id=0, telling the action script that the data is new, and not associated with a request to edit one of the existing rows.
<?php // RAY_EE_table_maintenance_form.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
// THIS SCRIPT PROVIDES ADD / EDIT / LIST FUNCTIONS
// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";
// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);
// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
$err
= "CONNECT FAIL: "
. $mysqli->connect_errno
. ' '
. $mysqli->connect_error
;
trigger_error($err, E_USER_ERROR);
}
// IF THERE IS A POST-METHOD REQUEST, INSERT OR UPDATE THE ROW
if (!empty($_POST))
{
// IF THE ID IS ZERO, INSERT A NEW ROW
if ($_POST['id'] == 0)
{
// ESCAPE THE DATA FOR USE IN THE QUERY
$fname = $mysqli->real_escape_string($_POST['fname']);
$lname = $mysqli->real_escape_string($_POST['lname']);
// CREATE AND RUN THE INSERT QUERY, TEST FOR ERRORS
$sql = "INSERT INTO EE_maintenance ( fname, lname ) VALUES ( '$fname', '$lname' )";
if (!$res= $mysqli->query($sql))
{
$err
= 'QUERY FAILURE:'
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
. ' QUERY: '
. $sql
;
trigger_error($err, E_USER_ERROR);
}
}
// IF THE ID IS NOT ZERO, UPDATE THE ROW
else
{
// ESCAPE THE DATA FOR USE IN THE QUERY
$id = $mysqli->real_escape_string($_POST['id']);
$fname = $mysqli->real_escape_string($_POST['fname']);
$lname = $mysqli->real_escape_string($_POST['lname']);
// CREATE AND RUN THE UPDATE QUERY, TEST FOR ERRORS
$sql = "UPDATE EE_maintenance SET fname = '$fname', lname = '$lname' WHERE id = $id LIMIT 1";
if (!$res= $mysqli->query($sql))
{
$err
= 'QUERY FAILURE:'
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
. ' QUERY: '
. $sql
;
trigger_error($err, E_USER_ERROR);
}
}
} // END OF POST-METHOD PROCESSING
// IF THERE IS AN ID IN THE GET-METHOD REQUEST GET THE RECORD
$id = !empty($_GET['id']) ? (int)$_GET['id'] : 0;
if ($id)
{
$sql = "SELECT fname, lname FROM EE_maintenance WHERE id = $id LIMIT 1";
if (!$res= $mysqli->query($sql))
{
$err
= 'QUERY FAILURE:'
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
. ' QUERY: '
. $sql
;
trigger_error($err, E_USER_ERROR);
}
// THERE WILL ONLY BE ONE ROW (AT MOST) BECAUSE OF LIMIT CLAUSE
$row = $res->fetch_object();
if ($row)
{
$fname = $row->fname;
$lname = $row->lname;
}
}
// CREATING A QUERY TO LIST THE TEST DATA ALPHABETICALLY
$sql = "SELECT id, fname, lname FROM EE_maintenance ORDER BY lname, fname";
if (!$res= $mysqli->query($sql))
{
$err
= 'QUERY FAILURE:'
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
. ' QUERY: '
. $sql
;
trigger_error($err, E_USER_ERROR);
}
// OUR WEB PAGE WILL BE CREATED IN THIS VARIABLE
$out = NULL;
// IF THERE IS A CURRENT UPDATE CREATE THE "UPDATE" FORM
if ($id)
{
$out = <<<EOD
<form method="post" action="RAY_EE_table_maintenance_form.php">
<input type="hidden" name="id" value="$id" />
EOD;
}
// PRINT OUT THE ROWS OF THE TABLE WITH UPDATE / DELETE LINKS
$out .= '<table>' . PHP_EOL;
while ($row = $res->fetch_object())
{
$out .= '<tr>'
. '<td>'
. '[<a href="RAY_EE_table_maintenance_form.php?id='
. $row->id
. '">update</a>]'
. '[<a href="RAY_EE_table_maintenance_delete.php?id='
. $row->id
. '">delete</a>]'
. '</td>'
. '<td>'
;
// IF THIS IS TO BE UPDATED
if ($row->id == $id)
{
$out .= '<input name="fname" value="' . $fname . '" />';
$out .= '<input name="lname" value="' . $lname . '" />';
$out .= '<input type="submit" name="signal" value="Update" />';
}
else
{
$out .= "$row->fname $row->lname";
}
$out .= '</td>'
. '</tr>'
. PHP_EOL
;
}
$out .= '</table>' . PHP_EOL;
if ($id) $out .= '</form>' . PHP_EOL;
// PUT THE "ADD NEW" TABLE AT THE END (CRUD "CREATE")
$out .= <<<EOD
<form method="post" action="RAY_EE_table_maintenance_form.php">
Add new information
<input type="hidden" name="id" value="0" />
<br>fname: <input name="fname" placeholder="First Name" />
<br>lname: <input name="lname" placeholder="Last Name" />
<br><input type="submit" name="signal" value="Add" />
</form>
EOD;
echo $out;It's easy to implement a sorting strategy when you're using data base queries with the ORDER BY clause. We can use dynamic query creation, a process that allows your PHP script to add or remove information from the SQL query string. By adding a URL parameter we can send a signal in the $_GET array that indicates the sort preference(s) and we can implement these preferences in the ORDER BY clause of the query. Although this article's example is very simple, one could envision something like "fdla" to signal a requirement for ORDER BY fname ASC, lname DESC.
The sortable script is identical to the previous script in every respect, except for four additional bits of functionality, all occurring after line 110.
Lines 111-115:
We determine the sort order by interpreting the o= parameter of the GET request. First we set a default value in $ord for use in the ORDER BY clause, then acquire the value of $_GET['o']. If the value matches one of our expectations, we may change the ORDER BY clause, otherwise we just ignore the incorrect input and use our default value. This variable becomes the dynamic part of our query on line 118.
Lines 148-155:
We create the table headers that contain the URL parameters to allow sorting.
Lines 161-162:
We add the sort signal to the URL parameter so that we preserve the requested sort order from one request to the next.
Line 194:
The action script name is changed to point to this Sortable version of the Form script.
There is one more thing -- a small revision to the Delete script. The Delete script uses header("Location") to redirect the client browser back to the appropriate Form script. It would make sense to change the Delete script at lines 56, 84, 105 so that the redirect command would bring the client back to this Sortable script. (Perhaps it would be worth abstracting the return URL into a variable so there would be only one line to change).
<?php // RAY_EE_table_maintenance_form_sortable.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
// THIS SCRIPT PROVIDES ADD / EDIT / LIST FUNCTIONS WITH SORTING
// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";
// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);
// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
$err
= "CONNECT FAIL: "
. $mysqli->connect_errno
. ' '
. $mysqli->connect_error
;
trigger_error($err, E_USER_ERROR);
}
// IF THERE IS A POST-METHOD REQUEST, INSERT OR UPDATE THE ROW
if (!empty($_POST))
{
// IF THE ID IS ZERO, INSERT A NEW ROW
if ($_POST['id'] == 0)
{
// ESCAPE THE DATA FOR USE IN THE QUERY
$fname = $mysqli->real_escape_string($_POST['fname']);
$lname = $mysqli->real_escape_string($_POST['lname']);
// CREATE AND RUN THE INSERT QUERY, TEST FOR ERRORS
$sql = "INSERT INTO EE_maintenance ( fname, lname ) VALUES ( '$fname', '$lname' )";
if (!$res= $mysqli->query($sql))
{
$err
= 'QUERY FAILURE:'
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
. ' QUERY: '
. $sql
;
trigger_error($err, E_USER_ERROR);
}
}
// IF THE ID IS NOT ZERO, UPDATE THE ROW
else
{
// ESCAPE THE DATA FOR USE IN THE QUERY
$id = $mysqli->real_escape_string($_POST['id']);
$fname = $mysqli->real_escape_string($_POST['fname']);
$lname = $mysqli->real_escape_string($_POST['lname']);
// CREATE AND RUN THE UPDATE QUERY, TEST FOR ERRORS
$sql = "UPDATE EE_maintenance SET fname = '$fname', lname = '$lname' WHERE id = $id LIMIT 1";
if (!$res= $mysqli->query($sql))
{
$err
= 'QUERY FAILURE:'
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
. ' QUERY: '
. $sql
;
trigger_error($err, E_USER_ERROR);
}
}
} // END OF POST-METHOD PROCESSING
// IF THERE IS AN ID IN THE GET-METHOD REQUEST GET THE RECORD
$id = !empty($_GET['id']) ? (int)$_GET['id'] : 0;
if ($id)
{
$sql = "SELECT fname, lname FROM EE_maintenance WHERE id = $id LIMIT 1";
if (!$res= $mysqli->query($sql))
{
$err
= 'QUERY FAILURE:'
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
. ' QUERY: '
. $sql
;
trigger_error($err, E_USER_ERROR);
}
// THERE WILL ONLY BE ONE ROW (AT MOST) BECAUSE OF LIMIT CLAUSE
$row = $res->fetch_object();
if ($row)
{
$fname = $row->fname;
$lname = $row->lname;
}
}
// SORT ORDER BY DEFAULT "LNAME,FNAME" OR BY "FNAME,LNAME"
$ord = 'lname, fname';
$sig = !empty($_GET['o']) ? $_GET['o'] : 'l';
if ($sig == 'f') $ord = 'fname, lname';
if ($sig == 'l') $ord = 'lname, fname';
// CREATING A QUERY TO LIST THE TEST DATA ALPHABETICALLY
$sql = "SELECT id, fname, lname FROM EE_maintenance ORDER BY $ord";
if (!$res= $mysqli->query($sql))
{
$err
= 'QUERY FAILURE:'
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
. ' QUERY: '
. $sql
;
trigger_error($err, E_USER_ERROR);
}
// OUR WEB PAGE WILL BE CREATED IN THIS VARIABLE
$out = NULL;
// IF THERE IS A CURRENT UPDATE CREATE THE "UPDATE" FORM
if ($id)
{
$out = <<<EOD
<form method="post" action="RAY_EE_table_maintenance_form_sortable.php">
<input type="hidden" name="id" value="$id" />
EOD;
}
// PRINT OUT THE ROWS OF THE TABLE WITH UPDATE / DELETE LINKS
$out .= '<table>' . PHP_EOL;
// COLUMN HEADERS ALLOW SORTING
$out .= '<tr><th colspan="2">';
$out .= 'Sort by ';
$out .= '<a href="RAY_EE_table_maintenance_form_sortable.php?o=f">fname</a>';
$out .= ' ';
$out .= '<a href="RAY_EE_table_maintenance_form_sortable.php?o=l">lname</a>';
$out .= '</th></tr>' . PHP_EOL;
// ITERATE OVER THE RESULTS SET TO BUILD THE WEB PAGE
while ($row = $res->fetch_object())
{
$out .= '<tr>'
. '<td>'
. '[<a href="RAY_EE_table_maintenance_form_sortable.php?o='
. $sig
. '&id='
. $row->id
. '">update</a>]'
. '[<a href="RAY_EE_table_maintenance_delete.php?id='
. $row->id
. '">delete</a>]'
. '</td>'
. '<td>'
;
// IF THIS IS TO BE UPDATED
if ($row->id == $id)
{
$out .= '<input name="fname" value="' . $fname . '" />';
$out .= '<input name="lname" value="' . $lname . '" />';
$out .= '<input type="submit" name="signal" value="Update" />';
}
else
{
$out .= "$row->fname $row->lname";
}
$out .= '</td>'
. '</tr>'
. PHP_EOL
;
}
$out .= '</table>' . PHP_EOL;
if ($id) $out .= '</form>' . PHP_EOL;
// PUT THE "ADD NEW" TABLE AT THE END
$out .= <<<EOD
<form method="post" action="RAY_EE_table_maintenance_form_sortable.php">
Add new information
<input type="hidden" name="id" value="0" />
<br><input name="fname" placeholder="First Name" />
<br><input name="lname" placeholder="Last Name" />
<br><input type="submit" name="signal" value="Add" />
</form>
EOD;
echo $out;If you are the only person on earth who will be maintaining this table (or you have turned this administrative task over to a single individual) you probably don't need to worry about overlapping updates. But if more than one person has access to the tables, you probably want to learn about Transactions and Locking. Here is the risk that you must consider.
The nature of the client server architecture is important here. Let's imagine that two clients at two separate computers access the table for the purpose of making some changes. Client #1 runs the "form" script and gets a list of rows. Then Client #2 runs the same script and gets the same list of rows. Both Clients are looking at the same information on the screen, and at this point the server is disconnected, awaiting the next request. Let's say Client #1 deletes a row. The data model has changed, and Client #1 is now looking at the new information, but Client #2 is still looking at the old list of rows. Client #1 is satisfied with the changes. Then Client #2 tries to modify the row that Client #1 deleted. Since the id is not in the table any more, the modification will not occur -- the row is already gone. Pretty soon your phone will ring and it will be Client #2 saying that your web site is broken and it lost his data.
One strategy for dealing with this sort or issue would be to make a message digest from the results set at the time your script processes a GET-method request to list the contents of the table. To do this, concatenate the contents of all of the columns in all of rows of the results set together into one long string variable. At the end of the retrieval, make the md5() string and put the string into the PHP session. When your script processes a POST-method request to UPDATE the table or to DELETE from the table, LOCK the table, run a SELECT ALL query, make a new message digest, and compare it to the md5() string in the session. If there is a match, the client is working with the latest copy of the table and you can proceed with UPDATE or DELETE, followed by UNLOCK. If there is a mismatch, the table has changed since it was displayed to the client. UNLOCK the table, and show the client a new list of the table contents, along with a notice that the changes might have collided with a racing script from another client. LOCK and UNLOCK are considered somewhat ham-fisted and unsophisticated, but until your tables get fairly large, this strategy will work well.
No locking is needed for INSERT queries since they do not modify any of the existing rows of the table.
Summary
This article has shown a short, self-contained correct example that teaches how to expand on the simple PHP forms tutorial, by incorporating SQL table maintenance into the process. We can now perform all of the CRUDL functions across all of the fields of our data base table. With a little more creativity, we might be able to put this functionality behind an access controlled page so that we could allow a site administrator to change selected parts of the data model.
References and Recommended Reading
http://www.w3.org/TR/html4
http://php.net/manual/en/t
http://php.net/manual/en/m
http://php.net/manual/en/c
http://php.net/manual/en/c
http://php.net/manual/en/m
http://php.net/manual/en/m
http://php.net/manual/en/m
http://php.net/manual/en/m
http://php.net/manual/en/m
http://php.net/manual/en/m
http://php.net/manual/en/m
http://php.net/manual/en/m
http://www.restapitutorial
Please give us your feedback!
If you found this article helpful, please click the "thumb's up" button below. Doing so lets the E-E community know what is valuable for E-E members and helps provide direction for future articles. If you have questions or comments, please add them. Thanks!
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)