CRUD (Create, Retrieve, Update, and Delete) Records in a Database Table
-
(Create) You may create a record by providing data and clicking the Add Record button.
-
(Retrieve) You may retrieve a record by selecting the record (radio button) and clicking the Select Record button.
-
(Update) You may update data by providing data and clicking the Save Record button.
-
(Delete) You may delete a record by selecting the record (radio button) and clicking the Delete Record button.
CODE FOLLOWS
<?php
error_reporting(E_ALL); // set error reporting to all
$self = basename($_SERVER['SCRIPT_NAME']);
// init arrays
$index = array();
$id = array();
$field_1 = array();
$field_2 = array();
$field_3 = array();
// init single variables
$field1 = '';
$field2 = '';
$field3 = '';
$rec_id = null;
$table = 'edit_table';
$con = '';
$TUID = '';
include('includes/open-db.php'); //====== open dB
$submit = '';
$submit = isset($_POST['submit']) ? $_POST['submit'] : null;
// determine what the user selected
if ($submit == 'Save Record') // User selected 'Save Record'
{
if (isset($_POST['rec_id']))
{
$rec_id = $_POST['rec_id'];
$field1 = isset($_POST['field1']) ? $_POST['field1'] : 0;
$field2 = isset($_POST['field2']) ? $_POST['field2'] : 0;
$field3 = isset($_POST['field3']) ? $_POST['field3'] : 0;
// clean data
$field1 = cleanForDB($con, $field1);
$field2 = cleanForDB($con, $field2);
$field3 = cleanForDB($con, $field3);
if (ctype_digit($rec_id)) // clean -- make sure $rec_id is a number
{
$query = "UPDATE $table SET field_1='$field1', field_2='$field2', field_3='$field3' WHERE id='$rec_id' ";
mysqli_query($con, $query) or die(report($query, __LINE__, __FILE__));
}
}
}
if ($submit == 'Add Record') // User selected 'Add record'
{
$field1 = isset($_POST['field1']) ? $_POST['field1'] : 0;
$field2 = isset($_POST['field2']) ? $_POST['field2'] : 0;
$field3 = isset($_POST['field3']) ? $_POST['field3'] : 0;
// clean data
$field1 = cleanForDB($con, $field1);
$field2 = cleanForDB($con, $field2);
$field3 = cleanForDB($con, $field3);
$query = "INSERT INTO $table (TUID, field_1, field_2, field_3) VALUES ('$TUID', '$field1', '$field2', '$field3') ";
mysqli_query($con, $query) or die(report($query, __LINE__, __FILE__));
}
if ($submit == 'Delete Record') // User selected 'Delete record'
{
if (isset($_POST['choice']))
{
$rec_id = $_POST['choice'];
if (ctype_digit($rec_id)) // clean -- make sure $rec_id is a number
{
$query = "DELETE FROM $table WHERE id = '$rec_id' ";
mysqli_query($con, $query) or die(report($query, __LINE__, __FILE__));
// optimize the table -- do this after a delete
$query = "OPTIMIZE TABLE $table ";
mysqli_query($con, $query) or die(report($query, __LINE__, __FILE__));
}
}
}
$select_flag = false;
if ($submit == 'Select Record') // User selected Record to be used in edit/save operation
{
if (isset($_POST['choice']))
{
$rec_id = $_POST['choice'];
if (ctype_digit($rec_id)) // clean -- make sure $rec_id is a number
{
$query = "SELECT * FROM $table WHERE id = '$rec_id' ";
$result = mysqli_query($con, $query) or die(report($query, __LINE__, __FILE__));
$row = mysqli_fetch_array($result);
// set the edit fields to the record selected
$field1 = $row['field_1'];
$field2 = $row['field_2'];
$field3 = $row['field_3'];
// prepare data for Browser
$field1 = htmlentities($field1);
$field2 = htmlentities($field2);
$field3 = htmlentities($field3);
$select_flag = true;
}
}
}
$i = 0;
// the following retrieves all the records from the database
$query = "SELECT * FROM $table WHERE id > '0'";
$result = mysqli_query($con, $query) or die(report($query, __LINE__, __FILE__));
// this pulls all fields from each record
while ($row = mysqli_fetch_array($result))
{
$i++;
$index[$i] = $i;
$id[$i] = $row['id'];
$field_1[$i] = $row['field_1'];
$field_2[$i] = $row['field_2'];
$field_3[$i] = $row['field_3'];
// prepare data for Browser
$field_1[$i] = htmlentities($field_1[$i]);
$field_2[$i] = htmlentities($field_2[$i]);
$field_3[$i] = htmlentities($field_3[$i]);
}
// if select flag is not set, then select last record pulled from above
if ($select_flag == false)
{
if ($i > 0)
{
$rec_id = $id[$i];
$field1 = $field_1[$i];
$field2 = $field_2[$i];
$field3 = $field_3[$i];
// prepare data for Browser
$field1 = htmlentities($field1);
$field2 = htmlentities($field2);
$field3 = htmlentities($field3);
}
}
include('includes/close-db.php'); //====== close dB
include('includes/header.php');
?>
<h1>CRUD (Create, Retrieve, Update, and Delete) Records in a Database Table</h1>
<ul>
<li>
<span class="red bold">(Create)</span> You may create a record by providing data and clicking the <span class="red bold">Add Record</span> button.
</li>
<li>
<span class="red bold">(Retrieve)</span> You may retrieve a record by selecting the record (radio button) and clicking the <span class="red bold">Select Record</span> button.
</li>
<li>
<span class="red bold">(Update)</span> You may update data by providing data and clicking the <span class="red bold">Save Record</span> button.
</li>
<li>
<span class="red bold">(Delete)</span> You may delete a record by selecting the record (radio button) and clicking the <span class="red bold">Delete Record</span> button.
</li>
</ul>
<form action="<?php echo($self); ?>" method="post">
<table>
<tr>
<th colspan=5 class="header1">
ALL RECORDS
</th>
</tr>
<tr class="header2">
<th></th>
<th>ID</th>
<th>Field 1</th>
<th>Field 2</th>
<th>Field 3</th>
</tr>
<?php
foreach ($index as $key)
{
?>
<tr class="row<?php echo(++$i & 1); ?>">
<td class="w5">
<input type="radio" name="choice"
value="<?php echo($id[$key]); ?>" <?php if ($id[$key] == $rec_id)
{
echo('CHECKED');
} ?>>
</td>
<td class="w5 center yellow">
<?php echo($id[$key]); ?>
</td>
<td class="w30">
<?php echo($field_1[$key]); ?>
</td>
<td class="w30">
<?php echo($field_2[$key]); ?>
</td>
<td class="w30">
<?php echo($field_3[$key]); ?>
</td>
</tr>
<?php
}
?>
</table>
<br>
<input type="submit" name="submit" value="Select Record">
<input type="submit" name="submit" value="Delete Record">
</form>
<br>
<br>
<form name="my_form" action="<?php echo($self); ?>" method="post">
<table>
<tr>
<th colspan=2 class="header1">
Current record
</th>
</tr>
<tr class="header2">
<th>Fields</th>
<th>Values</th>
</tr>
<tr>
<td class="right">
ID:
</td>
<td class="left">
<input type="text" size="5" name="rec_id" value="<?php echo($rec_id); ?>" READONLY class="bold">
<span class="red bold">NOTE: You can not edit this field!</span>
</td>
</tr>
<tr>
<td class="right">
Field 1:
</td>
<td class="left">
<input type="text" size="100" name="field1" value="<?php echo($field1); ?>">
</td>
</tr>
<tr>
<td class="right">
Field 2:
</td>
<td class="left">
<input type="text" size="100" name="field2" value="<?php echo($field2); ?>">
</td>
</tr>
<tr>
<td class="right">
Field 3:
</td>
<td class="left">
<input type="text" size="100" name="field3" value="<?php echo($field3); ?>">
</td>
</tr>
</table>
<br>
<input type="submit" name="submit" value="Save Record">
<input type="submit" name="submit" value="Add Record">
</form>
<?php
// The following two functions should be in your code
// -- OR -- be in the includes directory under "functions.php"
// AND include that file in this script.
// ================= functions =================
//-------------- show db errors --------------
// this function reports mysql errors with line number and script name
function report($query, $line, $file)
{
echo($query . '<br>' . $line . '<br>' . $file . '<br>');
}
//-------------- clean data for input into db --------------
// this function cleans all text for inserting into db
// in other words, this function returns SQL injection-proof strings
function cleanForDB($con, $str)
{
$str = stripslashes($str);
$str = trim($str);
$str = mysqli_real_escape_string($con, $str);
return $str;
}
include('includes/footer.php');
?>