CRUD Records in Books Table

ALL RECORDS
	ID	Title	ISBN
	3	My Little Pony	A123B4569334
	4	Fishing Made Easy	45645457878
	5	PHP for Kids	7894466889922
	7	West of Eden	102333555777
	8	South of Eden	1023335557999
	11	My Kitty Cat	1023335557999
	12	You're the	Dude!
	14	Tedd	12345
	15	South of Eden	1023335557999
	16	North of Holt	1234554321
	18	Help! I'm a 34 Year Old Stuck in a 90 Year Old Body!	24985612
	19	Lessons Learned	24985613
	20	The Sun Rises	24985614
	21	Who Moved My Cheese	388444
	22	Rich Dad Poor Dad	388445
	23	Think And Grow Rich	388446
	24	To Kill a Mockingbird	9780446310789
	25	Of Mice and Men	9780140292916
	26	The Great Gatsby	042272010264
	27	Magician	042272010264
	28	Silverthorn	042272010264
	29	Jimmy the Hand	042272010264
	30	The Great Gatsby	9780333791035
	31	Murder By Midnight	1651037973
	32	Systems Analysis and Design	1305494601
	33	The Book	4057154125
	34	Wadada Toosan	0475511452
	35	JavaScript and jQuery	9781118531648
	36	The Ultimate Final Fantasy 14 CookBook	9781647225117
	37	Learning Web Design	9781491960202
	38	The Chronicles of Narnia	9780066238500
	39	Adobe Photoshop Classroom in a Book (2022 release)	0137621108
	42	The Silmarillion	0544338014
	43	The Lord of The Rings	9780544003415
	44	The Hobbit	054792822X
	45	My Way of Doing Things	054792822X
	46	The Boy in the Striped Pyjamas	9781862305274
	47	1984	9780451524935
	48	Last Book In The Universe	0439087597
	49	Twentieth-Century Harmony	0393095398
	50	Effective Practicing for Musicians	9783982252018
	51	The Drummer's Toolbox	1999151933
	52	Daemon	9780451228734
	53	Titanium Noir	9781472156938
	54	The Dark Forest	9780765386694
	55	The Hitchhiker's Guide to the Galaxy	9781529046137
	56	World War Z	9780307950819
	57	Lord of the Flies	9780195634921
	58	Silence of the Lambs	9780312195267
	59	A Clockwork Orange	9780393928099
	60	The Sociopath Nextdoor	9780739456743
	61	Wuthering Heights	9781358772528
	62	The Call of the Wild	9781603400220
	63	Alice's Adventures in Wonderland	9781503222687
	64	brat	9781503222688
	65	brat and it's completely different but also still brat	9781503222689
	66	brat and it's the same but there's three more songs so it's not	9781503222690
	67	Hunter x Hunter vol.1	9784088725710
	68	Hunter x Hunter vol.2	4088726065
	69	Hunter x Hunter vol.3	4088726304
	70	Dragon Ball Z vol.1	9781569319307
	71	Dragon Ball Z vol.2	9781569319314
	72	Dragon Ball Z vol.3	9781569319321
	73	Harry Potter and the Deathly Hallows (Book 7)	9780545010221
	74	Harry Potter and the Chamber of Secrets (Harry Potter, Book 2)	9781338878936
	75	Harry Potter and the Chamber of Secrets	9781338878936
	76	Star Wars: Thrawn Ascendancy (Book I: Chaos Rising) (Star Wars: The Ascendancy Trilogy)	978-0593157688
	77	The Icarus Coda	978-1668072356
	78	The Art of War and Other Stories	978-1504096942

 

Current Selected Record
Fields	Values
ID:	<-- You cannot edit this ID (This is generated via autoincrement)
Book Title:
Book ISBN:

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

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

session_name("eckfordd");



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

// book arrays
$titles = array();
$isbns = array();

$title = '';
$isbn = '';
$rec_id = 0;
$result = '';
$comment = '';
$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 book record

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

    // clean data for db entry

    $title = mysqli_real_escape_string($con, $title);
    $isbn = mysqli_real_escape_string($con, $isbn);

    if (ctype_digit($rec_id) and $rec_id > 0)    // clean -- make sure $rec_id is a number
    {
        $query = "UPDATE $book_table SET title='$title', isbn='$isbn' WHERE id='$rec_id' ";
        $comment = "Could not save $book_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 book table

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

    // clean data

    $title = mysqli_real_escape_string($con, $title);
    $isbn = mysqli_real_escape_string($con, $isbn);

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

//==================================================================
// User selected 'Delete record'
// then must delete the Book AND any reference to the Book 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 the book
        $query = "DELETE FROM $book_table WHERE id = '$rec_id' ";
        $comment = "Could not delete $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 $book_table ";
        $comment = "Could not optmize $book_table records: $query";
        mysqli_query($con, $query) or die(report($comment, mysqli_error($con), __LINE__, __FILE__));

        // also delete all records where the book is listed
        $query = "DELETE FROM $author_book_table WHERE book_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__));
    }
}

//==================================================================
// At this point we have finished altering the table
// Now read back in all the Book records from the current table
//==================================================================

$query = "SELECT * FROM $book_table WHERE id > '0' ";
$comment = "Could not get $book_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'];
    $titles[$id] = htmlentities($row['title']);
    $isbns[$id] = htmlentities($row['isbn']);

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

    $rec_id = $id;
    $title = $titles[$id];
    $isbn = $isbns[$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 $book_table WHERE id = '$rec_id' ";
    $comment = "Could not get a specific $book_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
    $title = htmlentities($row['title']);
    $isbn = htmlentities($row['isbn']);
}

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

// Now show data
?>

<h2 class="center">CRUD Records in Books 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>Title</th>
            <th>ISBN</th>
        </tr>

        <?php
        $i = 0;
        foreach ($titles 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($titles[$key]); ?>
                </td>
                <td class="w30">
                    <?php echo($isbns[$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">
                Book Title:
            </td>
            <td class="left">
                <input type="text" size="60" name="title" value="<?php echo($title); ?>">
            </td>
        </tr>
        <tr>
            <td class="right">
                Book ISBN:
            </td>
            <td class="left">
                <input type="text" size="60" name="isbn" value="<?php echo($isbn); ?>">
            </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');
?>


---