Create Author to Book(s) Connection

ALL AUTHOR RECORDS (pick one)
	Author ID	First Name	Last 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

 

ALL BOOK RECORDS (pick any)
	Book 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

NOTE:

Clicking "Save" will connect the selected Author to the Book(s) checked. This will delete any previous connections the Author may have had. Also, clicking "Save" with a selected Author without any Book(s) checked will delete all "Author to Book" connections for that Author.

---

ALL AUTHOR TO BOOK ASSIGNMENTS
Author ID	Author Name	Book ID	Book Title : ISBN
17	Yeehaw Dawg	3	My Little Pony
17	Yeehaw Dawg	12	You're the
17	Yeehaw Dawg	26	The Great Gatsby
17	Yeehaw Dawg	32	Systems Analysis and Design
25	Billy Williams	23	Think And Grow Rich
25	Billy Williams	24	To Kill a Mockingbird
25	Billy Williams	25	Of Mice and Men
25	Billy Williams	26	The Great Gatsby
2	Steve Pet	7	West of Eden
1	Johny Joness	3	My Little Pony
1	Johny Joness	4	Fishing Made Easy
1	Johny Joness	5	PHP for Kids
30	Amin Hassen	33	The Book
30	Amin Hassen	34	Wadada Toosan
24	Lauren Olin	18	Help! I'm a 34 Year Old Stuck in a 90 Year Old Body!
12	Joey Shears	12	You're the
31	Lawrence Roberts	35	JavaScript and jQuery
31	Lawrence Roberts	36	The Ultimate Final Fantasy 14 CookBook
31	Lawrence Roberts	37	Learning Web Design
32	Hyereen Shin	38	The Chronicles of Narnia
32	Hyereen Shin	39	Adobe Photoshop Classroom in a Book (2022 release)
36	Ajay McDaniel	46	The Boy in the Striped Pyjamas
36	Ajay McDaniel	47	1984
36	Ajay McDaniel	48	Last Book In The Universe
37	Nick Lycos	49	Twentieth-Century Harmony
37	Nick Lycos	50	Effective Practicing for Musicians
37	Nick Lycos	51	The Drummer's Toolbox
38	Chris Sycamore	35	JavaScript and jQuery
38	Chris Sycamore	37	Learning Web Design
38	Chris Sycamore	43	The Lord of The Rings
20	Bunny Rabit	3	My Little Pony
39	Don Eckford	52	Daemon
39	Don Eckford	53	Titanium Noir
39	Don Eckford	54	The Dark Forest
40	Veronica Hutchins	55	The Hitchhiker's Guide to the Galaxy
40	Veronica Hutchins	56	World War Z
40	Veronica Hutchins	57	Lord of the Flies
41	Kaylyn Fragel	58	Silence of the Lambs
41	Kaylyn Fragel	59	A Clockwork Orange
41	Kaylyn Fragel	60	The Sociopath Nextdoor
42	Ashlynn Fritz	61	Wuthering Heights
42	Ashlynn Fritz	62	The Call of the Wild
42	Ashlynn Fritz	63	Alice's Adventures in Wonderland
43	Daniel Paxton	64	brat
43	Daniel Paxton	65	brat and it's completely different but also still brat
43	Daniel Paxton	66	brat and it's the same but there's three more songs so it's not
44	Kaitlyn Silengo	67	Hunter x Hunter vol.1
44	Kaitlyn Silengo	68	Hunter x Hunter vol.2
44	Kaitlyn Silengo	69	Hunter x Hunter vol.3
45	Mahmoud Siryani	70	Dragon Ball Z vol.1
45	Mahmoud Siryani	71	Dragon Ball Z vol.2
45	Mahmoud Siryani	72	Dragon Ball Z vol.3
46	Binita Tamang	73	Harry Potter and the Deathly Hallows (Book 7)
46	Binita Tamang	74	Harry Potter and the Chamber of Secrets (Harry Potter, Book 2)
46	Binita Tamang	75	Harry Potter and the Chamber of Secrets
47	Brian Lark	76	Star Wars: Thrawn Ascendancy (Book I: Chaos Rising) (Star Wars: The Ascendancy Trilogy)
47	Brian Lark	77	The Icarus Coda
47	Brian Lark	78	The Art of War and Other Stories

NOTE:

Clicking "RESET TABLE" will clear the entire "Author to Book" Table. In other words, there will be no connections between any Authors and Books!

// 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
//==================================================================
//  author-book.php -- Author to Book(s) Connection 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';
$book_table = 'books';
$author_book_table = 'author_book';

// author arrays
$author_first = array();
$author_last = array();

// book arrays
$book_title = array();
$book_isbn = array();

// author_book arrays
$authorID = array();
$bookID = array();

$rec_id = 0;
$result = '';
$comment = '';
$con = '';

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

// determine what the user selected

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

//==================================================================
// User selected 'RESET TABLE'  (TRUNCATE removes all records)

if($submit == 'RESET TABLE')
{
    $query = "TRUNCATE TABLE $author_book_table ";
    $comment =  "Could not TRUNCATE TABLE $author_book_table : $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__));
}

//==================================================================
// User selected 'Save Record'

if($submit == 'Save')        // User selected 'Save Record'
{
    $author_id = isset($_POST['author_id']) ? $_POST['author_id'] : 0;
    if(ctype_digit($author_id) AND $author_id > 0)    // clean -- make sure $id is a number and > 0
    {
        // === The following creates relational record(s) for *this* Author and book(s) ===
        // === update author to books record(s) require two steps

        // Step 1: Delete ALL records from the author_book table where the author is listed.
        // this deletes ALL author_book records for *this* Author
        $query = "DELETE FROM $author_book_table WHERE author_id= '$author_id' ";
        $comment =  "Could not delete $author_book_table record: $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__));

        // Step 2: Go through the POST book array and create new records for the
        // each of the books checked for *this* Author in the author_book table

        if(isset($_POST['book']))
        {
            foreach($_POST['book'] as $key => $book_id)
            {
                if(ctype_digit($book_id) AND $book_id > 0)    // clean -- make sure $key is a number and > 0
                {
                    $query = "INSERT INTO $author_book_table (author_id, book_id) VALUES ('$author_id', '$book_id') ";
                    $comment =  "Could not insert $author_book_table records: $query";
                    mysqli_query($con, $query) or die(report($comment, mysqli_error($con),__LINE__ ,__FILE__));
                }
            }
        }
    }
}

//==================================================================
// Pull in all Authors
//==================================================================

$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'];
    $author_first[$id] = htmlentities($row['first_name']);
    $author_last[$id] = htmlentities($row['last_name']);
}

//==================================================================
// Pull in all Books
//==================================================================

$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'];
    $book_title[$id] = htmlentities($row['title']);
    $book_isbn[$id] = htmlentities($row['isbn']);
}

//==================================================================
// Pull in all author to book records
//==================================================================

$query = "SELECT * FROM $author_book_table ";
$comment =  "Could not get $author_book_table records: $query";
$result = mysqli_query($con, $query) or die(report($comment, mysqli_error($con),__LINE__ ,__FILE__));

while ($row = mysqli_fetch_array($result))
{
    $authorID[] = $row['author_id'];
    $bookID[] = $row['book_id'];
}

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

// Now show data
?>

    <h2 class="center">Create Author to Book(s) Connection</h2>

    <form action="<?php echo($self); ?>" method="post" >
        <table class="full">
            <tr>
                <th colspan=4 class="header1">
                    ALL AUTHOR RECORDS (pick one)
                </th>
            </tr>
            <tr class="header2">
                <th>&nbsp;</th>
                <th>Author ID</th>
                <th>First Name</th>
                <th>Last Name</th>
            </tr>

            <?php
            $i = 0;

            // go through either of the author arrays (i.e., $author_first or $author_last)
            foreach($author_first as $key => $value )
            {
                ?>

                <tr class="row<?php echo(++$i &1);?>">
                    <td class="w5">
                        <input type="radio" name="author_id" value="<?php echo($key);?>" >
                    </td>
                    <td class="w5 center yellow">
                        <?php echo($key);?>
                    </td>
                    <td class="w30">
                        <?php echo($author_first[$key]);?>
                    </td>
                    <td class="w30">
                        <?php echo($author_last[$key]);?>
                    </td>

                </tr>

                <?php
            }
            ?>

        </table>

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

        <table class="full">
            <tr>
                <th colspan=4 class="header1">
                    ALL BOOK RECORDS (pick any)
                </th>
            </tr>
            <tr class="header2">
                <th>&nbsp;</th>
                <th>Book ID</th>
                <th>Title</th>
                <th>ISBN</th>
            </tr>

            <?php

            // go through either of the book arrays (i.e., $$book_title or $$book_isbn)
            foreach($book_title as $key => $value )
            {
                ?>

                <tr class="row<?php echo(++$i &1);?>">
                    <td class="w5">
                        <input type="checkbox" name="book[]" value="<?php echo($key);?>" >
                    </td>
                    <td class="w5 center yellow">
                        <?php echo($key);?>
                    </td>
                    <td class="w30">
                        <?php echo($book_title[$key]);?>
                    </td>
                    <td class="w30">
                        <?php echo($book_isbn[$key]);?>
                    </td>

                </tr>

                <?php
            }
            ?>

        </table>

        <br>
        <p>
            <input type="submit" name="submit" value="Save">
        </p>

        <br>

        <p class ="red">
            NOTE:
        </p>
        <p class ="blue">
            Clicking "Save" will connect the selected Author to the Book(s) checked.
            This will delete any previous connections the Author may have had.
            Also, clicking "Save" with a selected Author without any Book(s) checked will delete all "Author to Book"
            connections for that Author.
        </p>

        <hr>

        <table class="full">
            <tr>
                <th colspan=4 class="header1">
                    ALL AUTHOR TO BOOK ASSIGNMENTS
                </th>
            </tr>
            <tr class="header2">
                <th>Author ID</th>
                <th>Author Name</th>
                <th>Book ID</th>
                <th>Book Title : ISBN</th>
            </tr>


            <?php

            $filled = count($authorID);

            if($filled)    // are there any records?
            {

                foreach($authorID as $key => $value )
                {
                    $a = $authorID[$key];
                    $b = $bookID[$key];
                    ?>

                    <tr class="row<?php echo(++$i &1);?>">
                        <td class="w5 center yellow">
                            <?php echo($a);?>
                        </td>
                        <td class="w30">
                            <?php echo("$author_first[$a] $author_last[$a]");?>
                        </td>
                        <td class="w5 center yellow">
                            <?php echo($b);?>
                        </td>
                        <td class="w30">
                            <?php echo( $book_title[$b]);?>
                        </td>
                    </tr>

                    <?php
                }
            }
            else
            {
                echo('<tr><td>NO AUTHOR TO BOOK CONNECTIONS </td></tr>');
            }

            ?>

        </table>
        <br>
        <p>
            <input type="submit" name="submit" value="RESET TABLE" class="red bold right">
        </p>
        <br>
        <p class ="red">
            NOTE:
        </p>
        <p class ="blue">
            Clicking "RESET TABLE" will clear the entire "Author to Book" Table. In other words, there will be no connections between any Authors and Books!
        </p>

    </form>

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

---