CRUD Records in Authors Table

ALL RECORDS
	ID	First Name	Second Name
	1	Johny	Joness
	2	Steve	Pet
	3	Tom	Thumb
	5	Don	Duck
	8	tedd	sperling
	12	Joey	Shears
	14	Thurston	Howell III
	15	Jan	Brady
	16	Mark	Twain
	17	Yeehaw	Dawg
	20	Bunny	Rabit
	22	Bronwyn	Rruffus
	24	Lauren	Olin
	25	Billy	Williams
	26	Joseph	Tierney
	27	Owen	Bozsik
	30	Amin	Hassen
	31	Lawrence	Roberts
	32	Hyereen	Shin
	33	Dane	Farquhar
	36	Ajay	McDaniel
	37	Nick	Lycos
	38	Chris	Sycamore
	39	Don	Eckford
	40	Veronica	Hutchins
	41	Kaylyn	Fragel
	42	Ashlynn	Fritz
	43	Daniel	Paxton
	44	Kaitlyn	Silengo
	45	Mahmoud	Siryani
	46	Binita	Tamang
	47	Brian	Lark

 

Current Selected Record
Fields	Values
ID:	<-- You cannot edit this ID (This is generated via autoincrement)
First Name:
Last Name:

// 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
//==================================================================
//  authors.php (CRUD) Authors by Don Alexander Eckford
//==================================================================

// code
if (session_id() == '')
{
    session_start();
}

session_name("eckfordd");



// init vars and arrays
$self = basename($_SERVER['SCRIPT_NAME']);
$author_table = 'authors';
$author_book_table = 'author_book';

// author arrays
$first_names = array();
$last_names = array();

$first_name = '';
$last_name = '';
$rec_id = 0;
$result = '';
$comment = '';
$submit = '';
$con = '';

include('includes/open_db.php');    //====== open dB

// determine what the user selected

$submit = isset($_POST['submit']) ? $_POST['submit'] : '';

//==================================================================
// If User selected 'Save Record' then update author record

if ($submit == 'Save Record')
{
    $rec_id = isset($_POST['rec_id']) ? $_POST['rec_id'] : 0;
    $first_name = isset($_POST['first_name']) ? $_POST['first_name'] : '';
    $last_name = isset($_POST['last_name']) ? $_POST['last_name'] : '';

    // clean data for db entry

    $first_name = mysqli_real_escape_string($con, $first_name);
    $last_name = mysqli_real_escape_string($con, $last_name);

    if (ctype_digit($rec_id) and $rec_id > 0)    // clean -- make sure $rec_id is a number
    {
        $query = "UPDATE $author_table SET first_name='$first_name', last_name='$last_name' WHERE id='$rec_id' ";
        $comment = "Could not save $author_table records: $query";
        mysqli_query($con, $query) or die(report($comment, mysqli_error($con), __LINE__, __FILE__));
    }
}

//==================================================================
//  If User selected 'Add record' then create a new record in author table

if ($submit == 'Add Record')
{
    $first_name = isset($_POST['first_name']) ? $_POST['first_name'] : '';
    $last_name = isset($_POST['last_name']) ? $_POST['last_name'] : '';

    // clean data

    $first_name = mysqli_real_escape_string($con, $first_name);
    $last_name = mysqli_real_escape_string($con, $last_name);

    $query = "INSERT INTO $author_table (first_name, last_name) VALUES ('$first_name', '$last_name') ";
    $comment = "Could not insert $author_table records: $query";
    mysqli_query($con, $query) or die(report($comment, mysqli_error($con), __LINE__, __FILE__));
}

//==================================================================
// If User selected 'Delete record'
// then must Delete the Author AND any reference to the Author in author_book table

if ($submit == 'Delete Record')
{
    $rec_id = isset($_POST['rec_id']) ? $_POST['rec_id'] : 0;
    if (ctype_digit($rec_id) and $rec_id > 0)    // clean -- make sure $rec_id is a number
    {

        // delete all records where the author is listed
        $query = "DELETE FROM $author_book_table WHERE author_id= '$rec_id' ";
        $comment = "Could not delete $author_book_table records: $query";
        mysqli_query($con, $query) or die(report($comment, mysqli_error($con), __LINE__, __FILE__));

        // optimize the table -- do this after a delete
        $query = "OPTIMIZE TABLE $author_book_table ";
        $comment = "Could not optmize $author_book_table records: $query";
        mysqli_query($con, $query) or die(report($comment, mysqli_error($con), __LINE__, __FILE__));

        // delete the author
        $query = "DELETE FROM $author_table WHERE id = '$rec_id' ";
        $comment = "Could not delete $author_table records: $query";
        mysqli_query($con, $query) or die(report($comment, mysqli_error($con), __LINE__, __FILE__));

        // optimize the table -- do this after a delete
        $query = "OPTIMIZE TABLE $author_table ";
        $comment = "Could not optmize $author_table records: $query";
        mysqli_query($con, $query) or die(report($comment, mysqli_error($con), __LINE__, __FILE__));

    }
}

//==================================================================
// At this point we have finished altering all table(s)
// Now read back in all the Author records from the current table
//==================================================================

$query = "SELECT * FROM $author_table WHERE id > '0' ";
$comment = "Could not get $author_table records: $query";
$result = mysqli_query($con, $query) or die(report($comment, mysqli_error($con), __LINE__, __FILE__));

while ($row = mysqli_fetch_array($result))
{
    $id = $row['id'];
    $first_names[$id] = htmlentities($row['first_name']);
    $last_names[$id] = htmlentities($row['last_name']);

    // set current record to the last record from ALL records
    // can be overridden by 'Select Record' user selection

    $rec_id = $id;
    $first_name = $first_names[$id];
    $last_name = $last_names[$id];
}

//==================================================================
// User has selected a Record for edit/save operation
// Make the selected record the current record
//==================================================================

if ($submit == 'Select Record')
{
    $rec_id = isset($_POST['choice']) ? $_POST['choice'] : 1;
    if (!ctype_digit($rec_id))    // clean -- make sure $rec_id is a number
    {
        $rec_id = 1;
    }

    $query = "SELECT * FROM $author_table WHERE id = '$rec_id' ";
    $comment = "Could not get a specific $author_table records: $query";
    $result = mysqli_query($con, $query) or die(report($comment, mysqli_error($con), __LINE__, __FILE__));
    $row = mysqli_fetch_array($result);

    // set current record

    $rec_id = $row['id'];    // we already have it, but pull it anyway
    $first_name = htmlentities($row['first_name']);
    $last_name = htmlentities($row['last_name']);
}

include('includes/close_db.php');    //====== close dB

include('includes/header3.php');


// Now show data
?>
<h2 class="center">CRUD Records in Authors Table</h2>

<form action="<?php echo($self); ?>" method="post">
    <table class="full">
        <tr>
            <th colspan=4 class="header1">
                ALL RECORDS
            </th>
        </tr>
        <tr class="header2">
            <th></th>
            <th>ID</th>
            <th>First Name</th>
            <th>Second Name</th>
        </tr>

        <?php
        $i = 0;
        foreach ($first_names as $key => $value)
        {
            ?>

            <tr class="row<?php echo(++$i & 1); ?>">
                <td class="w5">
                    <input type="radio" name="choice"
                           value="<?php echo($key); ?>" <?php if ($key == $rec_id)
                    {
                        echo('CHECKED');
                    } ?>>
                </td>
                <td class="w5 center yellow">
                    <?php echo($key); ?>
                </td>
                <td class="w30">
                    <?php echo($first_names[$key]); ?>
                </td>
                <td class="w30">
                    <?php echo($last_names[$key]); ?>
                </td>
            </tr>

            <?php
        }
        ?>

    </table>

    <div class="clear">
        &nbsp;
    </div>

    <table class="full">
        <tr>
            <th colspan=2 class="header1">
                Current Selected 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 noborder">
                <span class="red bold"> &lt;-- You cannot edit this ID (This is generated via autoincrement) </span>
            </td>
        </tr>
        <tr>
            <td class="right">
                First Name:
            </td>
            <td class="left">
                <input type="text" size="60" name="first_name" value="<?php echo($first_name); ?>">
            </td>
        </tr>
        <tr>
            <td class="right">
                Last Name:
            </td>
            <td class="left">
                <input type="text" size="60" name="last_name" value="<?php echo($last_name); ?>">
            </td>
        </tr>
    </table>
    <br>
    <input type="submit" name="submit" value="Select Record">
    <input type="submit" name="submit" value="Save Record">
    <input type="submit" name="submit" value="Add Record">
    <input type="submit" name="submit" value="Delete Record">
</form>

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


---