Back
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.

ID	Field 1	Field 2	Field 3
29	Lauren	likes	squirrels
30	Eastern Fox Squirrels	Eastern Fox Squirrels (Black)	Eastern Gray Squirrels
31	Billy	the	Kid
32	Joe	would like	a cat. ^^
35	Amin	does an excellent	presentation
66	Tyler	aye	vvs
68	Allie	Hunt	Rules
70	Luis Rodrigo	Dominguez Tapia	Eats A LOT
72	Natalie	Bachman	:>
73	Carla	Pitawanakwat	:)
74	Kali	Jury	:P
75	Evan	Wagner	!
79	Nataliia	Piterova	gray, red, brown, black, and white
80	Orlando	Flounory	Vroom Vroom!
81	Taylor	Austin	Hi!!!!!!
82	Jenna	Redman	Let's go!
83	Jess	Campbell	surviving on coffee :D
85	Micaela	was	here :)
Current record
Fields	Values
ID:	NOTE: You can not edit this field!
Field 1:
Field 2:
Field 3:
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('db/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('db/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">
                &nbsp; <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');
?>
Back