CRUD (Create, Retrieve, Update, and Delete) Records in a Database Table

ALL RECORDS
ID Field 1 Field 2 Field 3
9 Dog Cat Fish
19 Cat xxx Tedd
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
36 Add Record by Tedd
38 My entry Record 2 by Tedd
43 Lawrence enjoys Watching YouTube
46 Kyle likes Football
50 river loves fetch
51 Chris Likes Golf
52 Don likes logic puzzles
54 Another Record Added
55 Yet Another record
57 Veronica Hutchins was_here
60 record 2 add
61 Ashlynn F Record
62 Mahmoud S Added
64 Binita Dog success
65 Brian Lark 3 Dogs I Have 3 little bad dogs!!



Current record
Fields Values
ID:   NOTE: You can not edit this field!
Field 1:
Field 2:
Field 3:

// 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 . '
' . $line . '
' . $file . '
'); } //-------------- 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; }
 
CODE FOLLOWS

<?php

//include('includes/header.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">
                &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


include('includes/functions.php');
include('includes/footer.php');
?>