Problem with inserting multiple rows into MySQL DB

[Ed]

Hello,

I have been trying to insert multiple rows from a web form into a MySQL DB.
I have a dynamic table on one page that sets up the arrays and posts the form to another page which does the MySQL bit.

Here is the the page with the form.

<form id="form1" name="form1" method="post" action="reportGameConfirmAdmin.php">
     <table border="1">
       <tr align="center" class="center-title-bar">
         <td colspan="13" bgcolor="#0d2f0b" class="big"><span class="title">All Games for <?php echo $row_ref['First']; ?> <?php echo $row_ref['Last']; ?></span></td>
       </tr>
       <tr align="center" class="center-title-bar">
         <td class="subtitle">Ref ID</td>
         <td class="subtitle">GameNumber</td>
         <td class="subtitle">Ref Type</td>
         <td class="subtitle">Date</td>
         <td class="subtitle">Time</td>
         <td class="subtitle">Field Code</td>
         <td class="subtitle">Home</td>
         <td class="subtitle">Away</td>
         <td class="subtitle">League</td>
         <td class="subtitle">Date Added</td>
         <td class="subtitle">Club</td>
         <td class="subtitle">Report</td>
         <td class="subtitle">Pay</td>
         <td class="subtitle"><input type="submit" name="insert" id="insert" value="Submit" /></td>
       </tr>
       <?php do { ?>
       <?php if ($row_games['Reported'] != "TRUE") { ?>
       <tr class="sidebar">
         <td><?php echo $row_games['RefID']; ?></td>
          <td><?php echo $row_games['GameNumber']; ?></td>
         <td><?php echo $row_games['RefType']; ?></td>
         <td><a href="../gamesSchedule.php?Date=<?php echo $row_games['Date']; ?> & Time=<?php echo $row_games['Time']; ?> & FieldCode=<?php echo $row_games['FieldCode']; ?>"><?php echo $row_games['Date']; ?></a></td>
         <td><?php echo $row_games['Time']; ?></td>
         <td><a href="#" onmouseover="MM_openBrWindow('../fieldCode.php?Code=<?php echo $row_games['FieldCode']; ?>','FieldCode','toolbar=yes,status=yes,scrollbars=yes,width=400,height=400')"><?php echo $row_games['FieldCode']; ?></a></td>
         <td><?php echo $row_games['Home']; ?></td>
         <td><?php echo $row_games['Away']; ?></td>
         <td><?php echo $row_games['League']; ?></td>
         <td><?php echo $row_games['DateAdded']; ?></td>
         <td><?php echo $row_games['Club']; ?></td>
         <td align="center" valign="middle"><a href="../reportGameConfirm.php?ID=<?php echo $row_games['ID']; ?>">Report</a></td>
         <td align="center" valign="middle"><?php $type = $row_games['RefType']; echo $row_pay["$type"]; ?></td>
         <td align="center" valign="middle"><label for="checkbox"></label>
           <input type="checkbox" name="checkbox[]" value="<?php echo $row_games['GameNumber']; ?>" />
           <input name="ID[]" type="hidden" value="" />
           <input name="GameNumber[]" type="hidden" value="<?php echo $row_games['GameNumber']; ?>" />
           <input name="RefType[]" type="hidden" value="<?php echo $row_games['RefType']; ?>" />
           <input name="RefID[]" type="hidden" value="<?php echo $row_games['RefID']; ?>" />
           <input name="First[]" type="hidden" value="<?php echo $row_ref['First']; ?>" />
           <input name="Last[]" type="hidden" value="<?php echo $row_ref['Last']; ?>" />
           <input name="Date[]" type="hidden" value="<?php echo $row_games['Date']; ?>" />
           <input name="Time[]" type="hidden" value="<?php echo $row_games['Time']; ?>" />
           <input name="FieldCode[]" type="hidden" value="<?php echo $row_games['FieldCode']; ?>" />
           <input name="Home[]" type="hidden" value="<?php echo $row_games['Home']; ?>" />
           <input name="Away[]" type="hidden" value="<?php echo $row_games['Away']; ?>" />
           <input name="League[]" type="hidden" value="<?php echo $row_games['League']; ?>" />
           <input name="DateAdded[]" type="hidden" value="<?php echo $row_games['DateAdded']; ?>" />
           <input name="club[]" type="hidden" value="<?php echo $row_games['Club']; ?>" />
           <input name="comments[]" type="hidden" value="Admin Entry" />
           <input name="pay[]" type="hidden" value="<?php $type = $row_games['RefType']; echo $row_pay["$type"]; ?>" />
           </td>
           </tr>
       </tr>
       <?php } ?>
       <?php } while ($row_games = mysql_fetch_assoc($games)); ?>
     </form></table>

And here is the page the receives the form info and does the insert into the DB.

if ($_POST['insert']) {
        $checkbox = $_POST['checkbox'];
        $count = count($_POST['checkbox']);
        echo $count;
        //for($i=0; $i < $count; $i++){
        foreach ($_POST['checkbox'] as $row => $GameNumber) {
            //$ID = $_POST['ID'][$row];
            $GameNumber = $GameNumber;
            $Date = $_POST['Date'][$row];
            $Time = $_POST['Time'][$row];
            $RefID = $_POST['RefID'][$row];
            $First = $_POST['First'][$row];
            $Last = $_POST['Last'][$row];
            $RefType = $_POST['RefType'][$row];
            $League = $_POST['League'][$row];
            $Home = $_POST['Home'][$row];
            $Away = $_POST['Away'][$row];
            $FieldCode = $_POST['FieldCode'][$row];//<?php $type = $row_GameConfirm['RefType']; echo $row_pay["$type"];
            $type = $row_GameConfirm['RefType'][$row];
            $PaymentAmount = $_POST['pay'][$row];
            $club = $_POST['club'][$row];
            $comments = $_POST['comments'][$row];

            //$parts = explode('/', $_POST['Date'][$row]);
            //$Date = "$parts[2]-$parts[0]-$parts[1]";

             $insertSQL = "INSERT INTO gamesConfirm (ID, GameNumber, `date`, `time`, refID, First, Last, `refType`, league, home, away, `FieldCode`, `paymentAmount`, `club`, comments) VALUES ('$ID', '$GameNumber', '$Date', '$Time', '$RefID', '$First', '$Last', '$RefType', '$League', '$Home', '$Away', '$FieldCode', '$PaymentAmount', '$club', '$comments')";
            echo "Pay amount: $PaymentAmount -><em>Updated!</em><br />";
            $result = mysql_query($insertSQL) or die ("Error in query: $insertSQL");
            echo "GameNumber: $GameNumber -><em>Updated!</em><br />";
            echo "RefType: $RefType -><em>Updated!</em><br />";

            $updateSQL = "UPDATE games SET Reported='TRUE' WHERE ID='$ID'";
            $result1 = mysql_query($updateSQL) or die ("Error in query: $insertSQL");
            echo "Update GameNumber: $GameNumber -><em>Updated!</em><br />";
        }
    }

    if($result) {
        echo "<meta http-equiv=\"refresh\" content=\"0;URL=gamesAdmin.php\">";
    }

My code is only sending a single row into the DB. I think my foreach is broken.

Any help on this would be greatly appreciated.

Thanks,

Ed

[diafol]

Don't use an INSERT query inside a foreach loop. Build the query (VALUES syntax) in the loop and run ONE query.

INSERT field1, field2... INTO table VALUES (value1a, value2a...),(value1b, value2b...),(value1c, value2c)

Your loop

$vals = array();
foreach($array as $row)
{
    $vals[] = "('{$row['field1']}','{$row['field2']}')";
}

$query = "INSERT field1, field2... INTO table VALUES " . implode(",", $vals);

Just a suggestion. Few ways to do this though - string concatenation for example.

[Ed]

Thanks diafol.
I suppose the array would look like this ('{$_POST['checkbox']}','{$_POST['Date']}')"; and so on for all my hidden form values?
Does everything look OK in the form that is posting to this array?

[diafol]

There are issues with your code.

foreach ($_POST['checkbox'] as $row => $GameNumber) {
            //$ID = $_POST['ID'][$row];
            $GameNumber = $GameNumber;  //What's this for?
            $Date = $_POST['Date'][$row];
            $Time = $_POST['Time'][$row];
            $RefID = $_POST['RefID'][$row];

You're also inserting unsanitized data to the query - very dangerous (SQL injection). It should be cleaned with mysql_real_escape_string().

INSERT INTO gamesConfirm (ID, GameNumber, `date`, `time`, refID, First, Last, `refType`, league, home, away, `FieldCode`, `paymentAmount`, `club`, comments) VALUES ('$ID', '$GameNumber', '$Date', '$Time', '$RefID', '$First', '$Last', '$RefType', '$League', '$Home', '$Away', '$FieldCode', '$PaymentAmount', '$club', '$comments')

For this you need an $ID, which you've commented out :(

You don't need to include the ID - or if you do, set it to NULL (no quotes around it)

INSERT INTO gamesConfirm (GameNumber, `date`, `time`, refID, First, Last, `refType`, league, home, away, `FieldCode`, `paymentAmount`, `club`, comments) VALUES ('$GameNumber', '$Date', '$Time', '$RefID', '$First', '$Last', '$RefType', '$League', '$Home', '$Away', '$FieldCode', '$PaymentAmount', '$club', '$comments')

Should work.

I suppose the array would look like this ('{$_POST['checkbox']}','{$_POST['Date']}')"; and so on for all my hidden form values?

No idea - head's shot. I'll be back tomorrow. In the meantime, anybody else??