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

Member Avatar
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.

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?

Member Avatar
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??