DELETE statement Help

MjM8082 · September 15, 2011

Having trouble with my DELETE statement in my code. I'm not sure why it's not working, I've tried pretty much everything but I'm obviously missing someone. If someone could please take a look at my code and help me out, would appreciate it....

Here is my code.... I posted a little bit extra code because my DELETE statement is in the first set of PHP tags and my checkboxes and delete button that use the delete statement are lower down in the code.

<?php
require_once('database.php');
session_start();

if (isset($_POST['add_grade']))
{


	$query		= "INSERT INTO grades (student_id, grade_type, grade_name, grade_points) ";
	$query		.= "VALUES (:student_id, :grade_type, :grade_name, :grade_points) ";

	$statement	= $db->prepare($query);
	$statement->bindValue (':student_id', $_SESSION['student_id']);
	$statement->bindValue (':grade_type', $_POST['grade_type']);
	$statement->bindValue (':grade_name', $_POST['grade_name']);
	$statement->bindValue (':grade_points', $_POST['grade_point']);

	$statement->execute();

	$statement->closeCursor();


	if (isset($_POST['remove']))
{

	foreach($_POST['delete'] as $delete_id)
	{

	$query		= "DELETE FROM grades WHERE grade_id = $grade_id";
	mysqli_query($dbc, $query) or die ('can\'t delete user'); 

	}
}

}



?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>View Course Grades</title>
</head>

<body>

<table>
				<table border="1">

				<th>Grade Type</th>

                    <th>Grade Name</th>

				<th>Grade Points</th>

                    <th>Delete</th>



<?php





$student_name = $_SESSION['student_name'];
$student_id = $_SESSION['student_id'];
$query		= "SELECT * FROM grades WHERE student_id = :student_id ";

$statement	= $db->prepare($query);
$statement->bindValue (':student_id', $student_id);
$statement->execute();
$grades		= $statement->fetchAll();
$statement->closeCursor();


echo "<h1>Show Grades for $student_name </h1>";



foreach ($grades as $grade)
{

	echo $grade['grade_type'] . " " . $grade['grade_name']. " " . $grade['grade_points'];



	echo '<input type="checkbox" value="' .$grade['grade_id'] . '" name="delete[]" />'; 
	echo ' ' .$grade['grade_type'] .' '. $grade['grade_name'];
	echo '<br />';  


}


?>



<form name="grades" method="post" action="grades.php">

	<p>Grade Type<SELECT NAME="grade_type">
	<OPTION VALUE="Mid-Term">Mid-Term
	<OPTION VALUE="Final">Final
	<OPTION VALUE="Lab">Lab
	</SELECT>
	<br>

	<form name="grades" method="post" action="grades.php">

	<input type="checkbox" name="delete[]" value="delete" />
	<label for="delete"></label>






	Grade Name:<input type="text" name="grade_name" value=""><br />
	Grade Points:<input type="text" name="grade_point" value="">

	<input type="submit" name="add_grade" value="Add Grade">

	</form>

</table>

</body>

</html>

MasterACE14 · September 15, 2011

I can't see $grade_id being set anywhere before the query.

I believe the query should be using $delete_id

$query		= "DELETE FROM grades WHERE grade_id = $delete_id";

Psycho · September 16, 2011

MasterAce14 already identified the problem with your DELETE problem. You are creating a loop and defining $delete_id, but in the query you are using $grade_id. But, there is another issue as well. Here is your current code:

if (isset($_POST['remove']))
{
    foreach($_POST['delete'] as $delete_id)
    {
        $query = "DELETE FROM grades WHERE grade_id = $grade_id";
        mysqli_query($dbc, $query) or die ('can\'t delete user');
    }
}

1) First off, we know you are referencing the wrong variable.

2) Why do you do an isset check on 'remove' then use the post value for 'delete'? What if 'delete' doesn't exist or is not an array? You only need to check 'delete'.

3) You should never run queries in loops. They are a huge performance hog. You can delete ALL the records with a single query by using IN.

4) You are performing no validation of the id's being passes

Here is what I would do

if (isset($_POST['delete']) && is_array($_POST['delete']))
{
    //Convert values to ints to prevent injection and convert to comma separated string
    $delete_ids = implode(',' array_map($_POST['delete'], 'intval'));

    //Create ONE query to delete all selected records
    $query = "DELETE FROM grades WHERE grade_id IN ($delete_ids)";
    mysqli_query($dbc, $query) or die ('can\'t delete user'); 
}

Sign In

DELETE statement Help

Recommended Posts

MjM8082

Link to comment

Share on other sites

MasterACE14

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information