deleting multiple entries?

[tjhilder]

Hi,

 

I want to delete multiple records via a form, but I'm not sure how I should go about this.

 

if someone could show me how I would edit this code:

        while ($row = mysql_fetch_array ($r)) {
            echo "\n<tr>\n<td class=\"commentid-number\">{$row['id']}</td>\n<td class=\"commentid-user\">{$row['user']}</td><td class=\"commentid-date\">{$row['date']}</td>\n<td class=\"commentid-edit\"><a href=\"$url?show=edit&id={$row['id']}\">click</a></td>\n<td class=\"commentid-delete\"><a href=\"$url?show=delete&id={$row['id']}\">click</a></td>\n</tr>";
        }

so that instead of

<a href=\"$url?show=delete&id={$row['id']}\">click</a>

it would be

<input type="checkbox" name="id" value="{$row['id']}">

 

I think that part is right (if not, please tell me), but I don't know how I would go about it with the query.

 

can someone help me with this?

 

thanks in advanced.

 

--

TJ

[tjhilder]

anyone able to help?

[ryanlwh]

name the checkboxes like this

<input type="checkbox" name="id[]" value="{$row['id']}">

 

this would produce

echo $_POST['id'][0]; //first id
echo $_POST['id'][1]; //second id

etc

[tjhilder]

ah, thanks for reply.

 

does that mean that if I use this mysql query:

$query = "DELETE FROM news WHERE news_id={$_POST['id']}

that it will delete all the id's or is there more code needed?

 

Edit: I thought I'd try it, but it didn't work (as I expected) got this error:

Unknown column 'Array' in 'where clause'. The query was DELETE FROM news WHERE news_id=Array.

[ryanlwh]

you need to loop through the array

foreach ($_POST['id'] as $v)
{
  $query = "DELETE FROM news WHERE news_id='$v'";
  mysql_query($query);
}

[tjhilder]

I have my code like this now, but it turned up an error.

    if (isset ($_POST['submit'])) { // Handle the form.
    
        // Define the query.
        foreach ($_POST['id'] as $v)
        {
            $query = "DELETE FROM news WHERE news_id='$v'";
            $r = mysql_query ($query); // Execute the query.
        }
        // Report on the result.
        if (mysql_affected_rows() == 1) {
            echo "\t\t\t\t\t<div class=\"news-box\">\n\t\t\t\t\t\t<div class=\"news-title\">\n\t\t\t\t\t\t\t:: Success\n\t\t\t\t\t\t</div>\n\t\t\t\t\t\t<div class=\"news-article\">\n\t\t\t\t\t\t\tThe Article has been deleted.<br /><br /><a href=\"$url?show=list\">Back</a>\n\t\t\t\t\t\t</div>\n\t\t\t\t\t</div>\n";
        } else {
            echo "\t\t\t\t\t<div class=\"news-box\">\n\t\t\t\t\t\t<div class=\"news-title\">\n\t\t\t\t\t\t\t:: Error\n\t\t\t\t\t\t</div>\n\t\t\t\t\t\t<div class=\"news-article\">\n\t\t\t\t\t\t\tMySQL Error Information: <b>" . mysql_error() . "</b>. The query was $query.\n\t\t\t\t\t\t</div>\n\t\t\t\t\t</div>\n";
        }

    }

 MySQL Error Information: . The query was DELETE FROM news WHERE news_id=''.

I have the code like this in another part of my code:

<input type="checkbox" name="id[]" value="{$row['id']}">

[ryanlwh]

you should put that if/else clause inside the loop.

[tjhilder]

Ok I did as you said:

        // Define the query.
        foreach ($_POST['id'] as $v)
        {
            $query = "DELETE FROM news WHERE news_id='$v'";
            $r = mysql_query ($query); // Execute the query.
            // Report on the result.
            if (mysql_affected_rows() >= 1) {
                echo "\t\t\t\t\t<div class=\"news-box\">\n\t\t\t\t\t\t<div class=\"news-title\">\n\t\t\t\t\t\t\t:: Success\n\t\t\t\t\t\t</div>\n\t\t\t\t\t\t<div class=\"news-article\">\n\t\t\t\t\t\t\tThe Article has been deleted.<br /><br /><a href=\"$url?show=list\">Back</a>\n\t\t\t\t\t\t</div>\n\t\t\t\t\t</div>\n";
            } else {
                echo "\t\t\t\t\t<div class=\"news-box\">\n\t\t\t\t\t\t<div class=\"news-title\">\n\t\t\t\t\t\t\t:: Error\n\t\t\t\t\t\t</div>\n\t\t\t\t\t\t<div class=\"news-article\">\n\t\t\t\t\t\t\tMySQL Error Information: <b>" . mysql_error() . "</b>. The query was $query.\n\t\t\t\t\t\t</div>\n\t\t\t\t\t</div>\n";
            }
        }

now I get 2 error messages (probably cos I was trying to delete 2 things at once):

MySQL Error Information: . The query was DELETE FROM news WHERE news_id=''.

[ryanlwh]

it's weird that mysql_error() was empty. View source on the form page, see if the checkboxes have values, and make sure you don't have something named id elsewhere.

[tjhilder]

Ah! it's working now! thanks for your help.

 

I found that it was trying to get 'id' from the mysql table but it's not id, it's news_id so I changed that, I also changed the foreach () with id to article_id (just in case it mixed with something else) wasn't and id[] to article_id[] for same reason.

 

although one thing I do want to fix now is the multiple messages that I get, because the mysql_affected_rows() is in the foreach, is there a way around this?

 

EDIT: I do believe that I might have solved it, although if you have a better idea then mine i'd like it

 

--------------

 

changed 
echo "error"; or echo "success";
for
$information = "error"; or $information = "success";
--------------

then did an echo $information; outside the foreach statement (so if nothing there it just remains blank.)

[ryanlwh]

or you could put those success/error messages into an array (or concatenate into a string), and print the array or string after the loop. There really is no "best" way.