[SOLVED] array help

[john32]

Hi,

 

I am a complete PHP/MySQL newbie and was wondering if someone could point me in the right direction please?

 

I am creating a small 'news items' list and I'm wondering how to make items 'archived'. For this basic example my 'news' table has the following fields:

 

id (tinyint)

title (varchar(255))

archived (char(1))

 

where 'archived' is 1 or 0.

 

My page would display each news title followed by a checkbox so the user can tick it to be archived if required. So after doing an sql select my code would be:

$sql = "SELECT * FROM news";
    $items = mysql_query($sql);
    $numrows = mysql_num_rows($items);
    while ($item = mysql_fetch_array($items)) {
        $n_id = $item["id"];
        $n_title = $item["title"];
        $n_archive = $item["archive"];
        if ($archive == '1') {
            $checked = 'checked="checked" ';
        } else {
            $checked = '';
        }
        echo($n_title . "<input type=\"checkbox\" name=\"archived[]\" value=\"".$n_id."\" ".$checked." /> );
    }

 

When clicking the submit button I need to update the table where those that have been ticked = 1 and those that are unticked = 0.

 

This is where I start to fall over as a complete newbie! I was thinking that it would be something relatively simple like:

for($i=0;$i<$numrows;$i++){
    $sql="UPDATE news SET archived='1' WHERE id = $archived[$i]";
}

 

but this doesn't seem to be working ... and also obviously doesn't set the 'archive' field to 0 if the checkbox has been unticked.

 

Any pointers would be very, very much appreciated. It wouldn't surprise me if I'm going completely the wrong way about it.

 

Thanks for your time and help,

 

John32

 

 

 

[premiso]

We need to see more code around the

 

for($i=0;$i<$numrows;$i++){
    $sql="UPDATE news SET archived='1' WHERE id = $archived[$i]";
}

 

Just from that, I do not see where you are calling mysql_query to update $sql in the database...

[Maq]

You're not executing the query, you're just assigning it to a string...

 

mysql_query("UPDATE news SET archived='1' WHERE id = $archived[$i]");

[john32]

Sorry, my apologies, I was trying to snip out some of the code that I thought would be a given...

for($i=0;$i<$numrows;$i++){
    $sql="UPDATE news SET archived='1' WHERE id = $archived[$i]";
    if (!@mysql_query($sql)) {
        exit();
    }
}

[GingerRobot]

There's actually a much neater shortcut here. You don't need to loop through your array and execute a separate query for each item. Because you've given the checkboxes a value corresponding to the row's ID, you can do the following:

 

$ids = implode(',',$_POST['archived'];
$sql = "UPDATE news SET archived='1' WHERE id IN ($ids)";
mysql_query($sql) or trigger_error(mysql_error(),E_USER_ERROR);

$sql = "UPDATE news SET archived='0' WHERE id NOT IN ($ids)";
mysql_query($sql) or trigger_error(mysql_error(),E_USER_ERROR);

 

How does it work? Well, the implode function creates a string from the array using the delimiter to separate each element - in this case, a comma. The IN clause in the mysql query allows you to specify a comma delimited list of possible values.

 

We perform a second query using NOT IN to take care of any items which have been un-archived.

 

Also, notice how the query has been performed. In your previous code, you were suppressing the error with the @ symbol. I've explicitly made it so that errors are shown - it'll make it much easier to find any problems.

[john32]

Thanks for your help ... I'll give it a go.

Much appreciated.

 

John32

[GingerRobot]

Oh, you should note that the above code isn't very secure. The values of the checkbox aren't verified, so they may not be integers. If this is anywhere live, you should make sure all the values in that array are integers before you execute the query.

[john32]

Perfect!! And what's more ... I understand it!!

There's hope for me yet.

 

Much appreciated GingerRobot.