<?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;
}
Listing the Table
<?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);
}
Two More Scripts Complete the Example
<?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 Display / Update Script
<?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;
A Sortable Display / Update Script
<?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;
Table Locks and Racing Conditions
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)