Page 1 of 1

Multiple insert problem with this SQL - help please

Posted: Wed Dec 15, 2004 7:50 am
by rp
Hi folks

I have this sql query

Code: Select all

<?php
$capacity = $_POST['capacity'];
for ($i=0; $i <count($capacity); $i++) 
if (isset($capacity[$i]))
{
$style = $_POST['style'];
for ($x=0; $x <count($style); $x++) 
if (isset($style[$x]))
{
$sqlinsertroomcap = "INSERT INTO venuecapacity (venueID,roomID,styleID,capacity) VALUES ('$venueID ','$room','$style[$x]','$capacity[$i]')";
$rsinsertroomcap = mysql_query($sqlinsertroomcap);
}
else
{
}
}
else
{
}
?>
which multiple inserts the info to the DB, I only want it once.

the variables come from a "dynamic" form - code extract

Code: Select all

<?php
$sqlstyle = "SELECT * FROM roomstyles";
$rsstyle = mysql_query($sqlstyle);
while ($row = mysql_fetch_array($rsstyle, MYSQL_ASSOC))
{
echo "<div id='col20'>";
echo "<input name='style[]' type='checkbox' value='{$row['roomstyleID']}'> ";
echo $row['roomstyle'];
echo "</div>";
echo "<div id='col25'>";
echo " - Capacity ";
echo "<input name='capacity[]' type='text' size='5'>";
echo "</div>";
echo "<br>";
} 

?>
Idea is that the user can select the "style" - style[] AND also enter a capacity - capacity[]

But obviously I only want the info entered once into the DB not the "double" - or more dependant upon the number of "style" rows.

Any help please - thanks

Posted: Wed Dec 15, 2004 8:15 am
by timvw
well, if you had done some datanormalization....

you would have noticed that capacity and style are repeating groups (more than one value per venuecapacity item is possible) so you would have separated them (created their own table)

then now you would only have to insert into the venuecapcaty;
and then insert all the capacity, venueID in the capacity table
style, venueID in the style table