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.
CodePudding user response:
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>
CodePudding user response:
I think You're using 2 Parameter in the deleteProcess.php
- First Parameter in the URL is
Book_ID
in<td><a href="deleteProcess.php?Book_ID=<?php echo $row["Book_ID"]; ?>">Delete</a></td>
Second Parameter in the URL is id
in <?php echo "<td><a href=\"deleteProcess.php?id=".$row['Book_ID']."\">Delete</a></td>"; ?>
So, can you please say why you're using their 2 lines separately?