Create Author to Book(s) Connection
Copyright© 2025 Tedd's stuff
CODE FOLLOWS
<?php
//==================================================================
// author-book.php -- Author to Book(s) Connection 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']);
$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/header.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> </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">
</div>
<table class="full">
<tr>
<th colspan=4 class="header1">
ALL BOOK RECORDS (pick any)
</th>
</tr>
<tr class="header2">
<th> </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/footer.php');
?>