mySQL to PHP DropDown List with a delete button that removes entire record

Question

Hi Everyone. I would like to create a drop down list that has a bunch of library books that comes from a table called "library" in a mySQL database.

I was able to create an add book to the table section as displayed in the img. i am unable to come right with my drop down list.

the code i have for the delete section is :

<?php $result = mysqli_query($conn, "DELETE FROM library WHERE 'Book_ID' = '$_GET[Book_ID]'");?>
<?php $result = mysqli_query($mysqli, "SELECT * FROM library"); ?>
<table>
    <select>
        <option>
    <tr>
        <td>Book_ID</td>
        <td>Author</td>
        <td>Author_Age</td>
        <td>Author_Genre</td>
        <td>Genre</td>
        <td>Book</td>
        <td>Year</td>
        <td>Age_Group</td>
        <td>Author_ID</td>
    </tr>
    </option>
</select>



    <?php
        $i = 0;
        while($row = mysqli_fetch_array($result)) { 
    ?>
            <tr>
                <td><?php echo $row['Author']; ?></td>
                <td><?php echo $row['Author_Age']; ?></td>
                <td><?php echo $row['Author_Genre']; ?></td>
                <td><?php echo $row['Genre']; ?></td>
                <td><?php echo $row['Book']; ?></td>
                <td><?php echo $row['Year']; ?></td>
                <td><?php echo $row['Age_Group']; ?></td>
                <td><?php echo $row['Author_ID']; ?></td>
                <td><?php echo $row['Book_ID']; ?></td>
                <td><a href="deleteProcess.php?Book_ID=<?php echo $row["Book_ID"]; ?>">Delete</a></td>
                <?php echo "<td><a href=\"deleteProcess.php?id=".$row['Book_ID']."\">Delete</a></td>"; ?>
            </tr>
    <?php 
            $i++;
        }      
    ?>
</table>

my deleteProcess.php looks as follows:

    <?php 
include_once ('database.php');
$sql = "DELETE FROM library WHERE Book_ID ='" . $_GET["Book_ID"] . "'";
if(mysqli_query($conn, $sql)){
    echo "Record deleted successfully";
} else{
    echo "Error deleting record: " . mysqli_error($conn);
}
mysqli_close($conn);
?>

How would i do this? If anyone could point out errors or point me in the right direction. the things i have found online haven't worked.

Answer 1

Typically one would never use GET for delete operations like this - POST is more common however you can try something like this. Initially perform a test to check that there is a book_id within the querystring ( GET request data ) and then construct the Prepared Statement to mitigate the SQL injection threat.

<?php
    /************************
        deleteProcess.php
    */
    if( !empty( $_GET['Book_ID'] ) ){
    
        require 'database.php';
    
        $sql='delete from `library` where `book_id`=?';
        $stmt=$conn->prepare( $sql );
        $stmt->bind_param('s',$_GET['Book_ID']);
        $stmt->execute();
        $rows=$stmt->affected_rows;
        
        
        # After the delete operation, go back to previous page... change the uri!
        exit( header( 'Location: /path/to/previous/page.php?status=' . $rows ) );
    }
?>

The select menu cannot contain arbitrary HTML - the only permitted child elements are option and optgroup elements so it remains unclear if that was a typo or a misunderstanding. That said, to populate the dropdown list you can easily do that by iterating through the recordset as you do for final display purposes. To re-use the recordset later you probably need to rewind to the first record -which is shown here with data_seek(0)

As the intention for the select menu was unclear I popped the onchange event handler in to illustrate how it might be used... none of this is tested for errors/mistooks though ;-)

<?php
    
    $result=$conn->query('select * from library');
    
?>
    <select name='books' onchange='changehandler(event)'>
    <?php
    
        while( $row = $result->fetch_assoc() ) printf('<option value="%s">%s', $row['Book_ID'], $row['Book'] );
        
        #rewind recordset
        $result->data_seek(0);
    ?>
    </select>
    <script>
        const changehandler=function(e)=>{
            alert( ' Do interesting things..... ' + this.value + ' ' + this.options[ this.options.selectedIndex ].text )
        };
    </script>
<table>
    <tr>
        <td>Book_ID</td>
        <td>Author</td>
        <td>Author_Age</td>
        <td>Author_Genre</td>
        <td>Genre</td>
        <td>Book</td>
        <td>Year</td>
        <td>Age_Group</td>
        <td>Author_ID</td>
    </tr>

<?php
    while( $row = $result->fetch_assoc() ) { 
?>
        <tr>
            <td><?php echo $row['Author']; ?></td>
            <td><?php echo $row['Author_Age']; ?></td>
            <td><?php echo $row['Author_Genre']; ?></td>
            <td><?php echo $row['Genre']; ?></td>
            <td><?php echo $row['Book']; ?></td>
            <td><?php echo $row['Year']; ?></td>
            <td><?php echo $row['Age_Group']; ?></td>
            <td><?php echo $row['Author_ID']; ?></td>
            <td><?php echo $row['Book_ID']; ?></td>
            <td>
                <a href="deleteProcess.php?Book_ID=<?php echo $row["Book_ID"]; ?>">Delete</a>
            </td>
        </tr>
<?php
    }      
?>
</table>