Multiple insert problem with this SQL - help please

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
rp
Forum Newbie
Posts: 21
Joined: Wed Nov 24, 2004 5:49 am

Multiple insert problem with this SQL - help please

Post 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
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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
Post Reply