CODEWITHSUNDEEP
phpmysqlforms
John Doe
John Doe

Reputation: 158

PHP + MySQL Issue - where did I go so wrong?

Here's the dig. I've looked over and over my code until my mind was numb and still can't figure out where the issue is.

I'm working with a delete feature that works on the backend of a CRM-like application. There is a user list with checkboxes by each registered user inside of a form, and a submit button to run the code to delete the selected users from the database by their ID.

The checkboxes are generated in a foreach loop that populates the table using an array returned from a select query. Each checkbox line for each user is as such:

<input type="checkbox" name="checked[]" value="<?php echo ($userfromforeach['id']); ?>">

The submit button for the form has (name="deleteusers") included.

And the actual delete code for the form submission is as follows:

// Delete users by selected as posted by form
if(isset($_POST['deleteusers']))
{
    foreach($_POST['checked'] as $user)
    {
        $query = "
            DELETE
            FROM $usertable
            WHERE id = $user
        ";

        try
        {
            // These two statements run the query against your database table.
            $stmt = $db->prepare($query);
            $stmt->execute();
        }
        catch(PDOException $ex)
        {
            die("MySQL execution error, please contact technical support.");
        }

    }
    // $_POST['checked'] = NULL;
    header("Refresh:0");
}

For some reason unknown to me, everything seems to be firing correctly however no records are being deleted from the database. No MySQL error is returned, and when I print_r out the $_POST['checked'] variable it shows as would be expected:

Array ( [0] => 122 [1] => 115 )

^ The values to each key being the user's ID.

Any thoughts? Have I just knuckle-headed up a problem in my own head by missing something entirely elementary? Thanks very much for your help.

Upvotes: 1

Views: 109

Answers (4)

John Doe
John Doe

Reputation: 158

Well I'm an idiot.

After much digging it turns out that this issue is that my submit button in bootstrap's modal popup box, and apparently even though by my understanding, modal only essentially hides and shows a div in the page, any post data from the modal div won't get passed back to the main page. >:[ As such, the only solution I can think of is to set the $_POST['deleteusers'] variable from javascript on click of the delete button... What a pain in the sitter...

In any case, thank you all for your help, I will mark The Bigbyte Number's answer because he covered two other very important considerations - using prepared statements correctly and how to streamline the process into one single query, saving resources and theoretically CPU time as well.

Upvotes: 1

Ashish Nayyar
Ashish Nayyar

Reputation: 560

Why running delete query in a loop, what if user selects 200 records? You will fire query 200 times?

I recommend you write your PHP login in such a way that it satisfying the following queries

DELETE from tablename WHERE id IN (1,2,3,...,200);

Use BETWEEN if you have consecutive IDs :

DELETE from tablename WHERE id BETWEEN 1 AND 200;

Limiting only for some IDs :

DELETE from tablename WHERE id BETWEEN 1 AND 200 AND id<>47;

This way the query will be fired only once, provided the input for the MySQL query (IDs) are pre-generated via PHP loop.

Upvotes: 2

Naisa purushotham
Naisa purushotham

Reputation: 913

if(isset($_POST['deleteusers'])) <-- submit button with the name "deleteusers"
    {   

        foreach($_POST['checked'] as $user)
        {
            $query = "DELETE FROM $usertable WHERE id = :user";     
            try
            {
                $stmt = $db->prepare($query);
                $stmt->bindParam(':user', $user);
                $stmt->execute();
            }
            catch(PDOException $ex)
            {
                die("MySQL error, blah blah blah...");
            }
        }
    }

and you can run this one in one statement using this query

$query = "DELETE FROM $usertable WHERE id IN (:user)";

$stmt->bindParam(':user',implode(",",$_POST['deleteusers']));

Upvotes: 1

Rahul M
Rahul M

Reputation: 1509

When you loop over something with for/foreach functions. You've to wrap the whole repeating logic with-in curly braces. Otherwise ONLY ONE SINGLE STATEMENT WILL BE EXECUTED which in your case is assigning a string to the $query variable. And that's it. Though there are still a number of improvements to be made. But to solve this particular issue, your foreach should look like following:

foreach($_POST['checked'] as $user)
{
    $query = "DELETE FROM $usertable WHERE id = $user";
    try
    {
        $stmt = $db->prepare($query);
        $stmt->execute();
    }
    catch(PDOException $ex)
    {
        die("MySQL error, blah blah blah...");
    }
}

Upvotes: 3

Related Questions