CRUD Records in Authors Table
Copyright© 2024 Tedd's stuff
CODE FOLLOWS
<?php
//==================================================================
// authors.php (CRUD) Authors 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';
$author_book_table = 'author_book';
// author arrays
$first_names = array();
$last_names = array();
$first_name = '';
$last_name = '';
$rec_id = 0;
$result = '';
$comment = '';
$submit = '';
$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 author record
if ($submit == 'Save Record')
{
$rec_id = isset($_POST['rec_id']) ? $_POST['rec_id'] : 0;
$first_name = isset($_POST['first_name']) ? $_POST['first_name'] : '';
$last_name = isset($_POST['last_name']) ? $_POST['last_name'] : '';
// clean data for db entry
$first_name = mysqli_real_escape_string($con, $first_name);
$last_name = mysqli_real_escape_string($con, $last_name);
if (ctype_digit($rec_id) and $rec_id > 0) // clean -- make sure $rec_id is a number
{
$query = "UPDATE $author_table SET first_name='$first_name', last_name='$last_name' WHERE id='$rec_id' ";
$comment = "Could not save $author_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 author table
if ($submit == 'Add Record')
{
$first_name = isset($_POST['first_name']) ? $_POST['first_name'] : '';
$last_name = isset($_POST['last_name']) ? $_POST['last_name'] : '';
// clean data
$first_name = mysqli_real_escape_string($con, $first_name);
$last_name = mysqli_real_escape_string($con, $last_name);
$query = "INSERT INTO $author_table (first_name, last_name) VALUES ('$first_name', '$last_name') ";
$comment = "Could not insert $author_table records: $query";
mysqli_query($con, $query) or die(report($comment, mysqli_error($con), __LINE__, __FILE__));
}
//==================================================================
// If User selected 'Delete record'
// then must Delete the Author AND any reference to the Author 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 all records where the author is listed
$query = "DELETE FROM $author_book_table WHERE author_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__));
// delete the author
$query = "DELETE FROM $author_table WHERE id = '$rec_id' ";
$comment = "Could not delete $author_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_table ";
$comment = "Could not optmize $author_table records: $query";
mysqli_query($con, $query) or die(report($comment, mysqli_error($con), __LINE__, __FILE__));
}
}
//==================================================================
// At this point we have finished altering all table(s)
// Now read back in all the Author records from the current table
//==================================================================
$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'];
$first_names[$id] = htmlentities($row['first_name']);
$last_names[$id] = htmlentities($row['last_name']);
// set current record to the last record from ALL records
// can be overridden by 'Select Record' user selection
$rec_id = $id;
$first_name = $first_names[$id];
$last_name = $last_names[$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 $author_table WHERE id = '$rec_id' ";
$comment = "Could not get a specific $author_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
$first_name = htmlentities($row['first_name']);
$last_name = htmlentities($row['last_name']);
}
include('includes/close-db.php'); //====== close dB
include('includes/header.php');
// Now show data
?>
<h2 class="center">CRUD Records in Authors 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>First Name</th>
<th>Second Name</th>
</tr>
<?php
$i = 0;
foreach ($first_names 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($first_names[$key]); ?>
</td>
<td class="w30">
<?php echo($last_names[$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">
First Name:
</td>
<td class="left">
<input type="text" size="60" name="first_name" value="<?php echo($first_name); ?>">
</td>
</tr>
<tr>
<td class="right">
Last Name:
</td>
<td class="left">
<input type="text" size="60" name="last_name" value="<?php echo($last_name); ?>">
</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');
?>