[RESOLVED] Insert multiple rows and update too...

[blueleafprint]

Hi there,

I've got to a sticking point and had a look around on here for an answer, but can't find what I need....

I have got multiple checkboxes on the previous page which I then add separate rows into the database using:

<? $how_many = count($regions); 
	if ($how_many>0) { 
        } 
        for ($i=0; $i<$how_many; $i++) { 
		$query="INSERT INTO registered (date, GROUP_ID, REGION_ID, email) VALUES (NOW(), '{$_POST['group']}', '$regions[$i]','{$_POST['email']}')"; 
		mysql_query ($query);
		}
}
?>

I have also got it adding to a separate table using:

<? $query2= "INSERT INTO companys (company, contact, address, phone, email, date) VALUES ( '$company', '$contact', '$address', '$phone', '$email', NOW())"; 
   mysql_query ($query2);
?>

But I need it to update a third table which holds a number from 0-5 against 'REGION_ID' and 'GROUP_ID', and need it to increase this number by 1 if the region has been chosen.

Sorry its so long, but didn't know how much detail you need 🙂

Thanks,
George

[bpat1434]

So you want to UPDATE table and SET column to column + 1 WHERE the REGION_ID is IN () some set of ids ?

<?php

$query = "UPDATE `table`
SET `column` = `column` + 1
WHERE `REGION_ID` IN (".implode("'", $regions).")";

Hope that helps.

[laserlight]

Note that bpat1434's example is missing the first and last single quotes, and you should properly escape the elements of $regions before the implode() call.

[blueleafprint]

Hi BPat and Laserlight,

Thanks for your replies, but when I try adding this into the page I get
"Warning: implode() [function.implode]: Bad arguments. in"

on the line with

WHERE `REGION_ID` IN (".implode("'", '$regions').")";

[laserlight]

You should not be quoting the $regions variable. Why not post more of your code?

[blueleafprint]

Hi Laserlight...

I have got it to work 🙂

I am now using

<? $how_many2 = count($regions); 
	if ($how_many2>0) { 
        } 
        for ($i=0; $i<$how_many2; $i++) { 
		$query2 = "UPDATE `total_spaces` SET `SPACES` = `SPACES` + 1 WHERE `REGION_ID`= '$regions[$i]'";
		mysql_query ($query2);
		}
?>

Thanks for your help.

[laserlight]

The problem with that is that you are running $how_many2, which can be excessive. As suggested by bpat1434, it would be better to do something like this:

// Escape the elements of a copy of $regions for input to the database.
$regions_list = $regions;
foreach ($regions_list as $key => $value) {
    // This assumes that the elements are strings rather than integer ids.
    $regions_list[$key] = mysql_real_escape_string($value);
}
$regions_list = "'" . implode("'", $regions_list) . "'";

$query = "UPDATE `total_spaces` SET `SPACES` = `SPACES` + 1 WHERE `REGION_ID` IN ($regions_list)";
mysql_query($query);