YOUR PHP Example

Back

CRUD Records in Books Table


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
79	Caught Singing in the Shower at Midnight!	654613168843354654
80	That day the squirrel went berserk!	5164654631646946368416696
81	Wallie and the Carmel Factory!	654163468513658
82	Roland and his Rolos...	654163468513659
83	My story	123
84	My story 2	1234
85	My story 3	12345
86	Business Secrets of the Pharaohs	789789
87	Mr. Nice	789789789
88	⁠Rubicon: The Triumph and Tragedy of the Roman Republic	789789789789
89	My Hero Academia, Vol. 1	9781421582696
90	Jujutsu Kaisen, Vol. 25 (25)	1974751880
91	The Seven Deadly Sins 1 (Seven Deadly Sins, The)	1612629210
92	Wings of Fire: The Dragonet Prophecy	9781338883190
93	Haikyu!!, Vol. 1 (1)	1421587661
94	Wild Born (Spirit Animals, Book 1) (1)	0545522439
95	Diary Of An Oxygen Thief	978-1-5011-5785-1
96	They Both Die At The End	978-0-06-327854-7
97	We'll Fly Away	978-0-06-249428-3
98	The Fifth Element	113000
99	Something Wicked This Way Comes	113001
100	Watchmen	113002
101	The Primal Hunter	‎979-8835275045
102	Victor of Tucson	9781039418875
103	Rise of the Living Forge	9798306264035
104	War and Peace but with fairies	G123
105	The Heart of Darkness Teen Vampire	G124
106	A Tale of Two Cities also Dragons	G125

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

CODE FOLLOWS




<?php

//==================================================================

//  books.php (CRUD) Books by Tedd

//==================================================================



// code

if (session_id() == '')

{

    session_start();

}



session_name("sperlt");



include('includes/functions.php');    // standard functions



// 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/header.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/footer.php');

?>

Back