CRUD Records in Books Table
Copyright© 2025 Tedd's stuff
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">
</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"> <-- 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');
?>