Hello,

Despite much searching, I've hit a wall with trying to get a varying number of records inserted into a database.

Here's what I'm trying to do.....

I'm building an online sign on site for races at a sailing club. For the sign on page, I want to make it possible for racers to sign on for either one or all the races happening on a day. Here's the form.....

<form action="" method="post" name="form1" id="form1">
  <table align="center">
          <tr valign="baseline">
            <td nowrap="nowrap" align="right">Helm:</td>
            <td><select name="helm">
              <option value="">Please Select....</option>
              <?php
do {  
?>
              <option value="<?php echo $row_sailors['name']?>"><?php echo $row_sailors['name']?></option>
              <?php
} while ($row_sailors = mysql_fetch_assoc($sailors));
  $rows = mysql_num_rows($sailors);
  if($rows > 0) {
      mysql_data_seek($sailors, 0);
	  $row_sailors = mysql_fetch_assoc($sailors);
  }
?>
            </select></td>
          </tr>
          <tr> </tr>
          <tr valign="baseline">
            <td nowrap="nowrap" align="right">Crew:</td>
            <td><select name="crew">
              <option value="">Please Select....</option>
              <?php
do {  
?>
              <option value="<?php echo $row_crew['name']?>"><?php echo $row_crew['name']?></option>
              <?php
} while ($row_crew = mysql_fetch_assoc($crew));
  $rows = mysql_num_rows($crew);
  if($rows > 0) {
      mysql_data_seek($crew, 0);
	  $row_crew = mysql_fetch_assoc($crew);
  }
?>
            </select></td>
          </tr>
          <tr> </tr>
          <tr valign="baseline">
            <td nowrap="nowrap" align="right">Boat:</td>
            <td><select name="boat">
              <option value="">Please Select....</option>
              <?php
do {  
?>
              <option value="<?php echo $row_boats['boat_type']?>"><?php echo $row_boats['boat_type']?></option>
              <?php
} while ($row_boats = mysql_fetch_assoc($boats));
  $rows = mysql_num_rows($boats);
  if($rows > 0) {
      mysql_data_seek($boats, 0);
	  $row_boats = mysql_fetch_assoc($boats);
  }
?>
            </select></td>
          </tr>
          <tr> </tr>
          <tr valign="baseline">
            <td nowrap="nowrap" align="right">Sail Number:</td>
            <td><input type="text" name="sailnumber" value="" size="20" /></td></tr>
            
      <tr valign="baseline">
            <td nowrap="nowrap" align="right">&nbsp;</td>
            <td><input type="submit" value="Sign On" /></td>
          </tr>
        </table>
        <input name="racecrewid" type="hidden" value="<?php echo $_SESSION['raceteam']; ?>"  />
    <input type="hidden" name="raceid" value="<?php echo $row_racestoday['raceunique']; ?>" />
        <input type="hidden" name="MM_insert" value="form1" />
      </form>

Now the bit I'm stuck on - when there is more than one race on a day, I can't get the INSERT INTO query to add a record for each race occurence - for example, there are 3 races on any given day, the racers want to sign on for all 3 races in one hit, therefore the code below loops 3 times with the crew info but changes the race id. I haven't yet added the option for people to sign on for one or all races - I'm trying to get this bit to work first!

Here's the insert code

<? 
if (isset($_POST['submit']));{
	   do {
$raceid = $_POST['raceid']; 
$steerer =  $_POST['helm']; 
$swimmer = $_POST['crew'];
$boatywoaty = $_POST['boat'];
$knowtheboat = $_POST['sailnumber'];
$todaysteam = $_POST['racecrewid'];


$query = "INSERT INTO tblcompetitors (helm, crew, boat, sailnumber, raceid, raceteamid) VALUES('$steerer','$swimmer', '$boatywoaty', '$knowtheboat', '$raceid', '$todaysteam')"; 
mysql_select_db($database_sailingdb, $sailingdb);
mysql_query($query) or die(mysql_error("still not working grrr!!")); 


} while ($row_racestoday = mysql_fetch_assoc($racestoday)); 

}
?>

As far as I can tell, it isn't looping. Having tried various for / foreach loops but just can't get it to work. It inserts the correct details once but leaves the raceid column blank.

Any help gratefully received!

Thanks in advance
Dave

$raceid = $_POST['raceid'];

For multiple selects, this might actually return an array. so
you could do something like this . I think...

<? 
if (isset($_POST['submit']));{
	$raceid = $_POST['raceid']; 
$steerer =  $_POST['helm']; 
$swimmer = $_POST['crew'];
$boatywoaty = $_POST['boat'];
$knowtheboat = $_POST['sailnumber'];
$todaysteam = $_POST['racecrewid'];

  foreach($r_id in $raceid) 
 {

$query = "INSERT INTO tblcompetitors (helm, crew, boat, sailnumber, raceid, raceteamid) VALUES('$steerer','$swimmer', '$boatywoaty', '$knowtheboat', '$r_id', '$todaysteam')"; 
mysql_select_db($database_sailingdb, $sailingdb);
mysql_query($query) or die(mysql_error("still not working grrr!!")); 
$row_racestoday = mysql_fetch_assoc($racestoday);
}
?>

I think this would work. However I am not pretty sure though.

Member Avatar for diafol

Don't loop an INSERT sql query. Just build up a values string, e.g.:

INSERT INTO table (field1, field2, field3) VALUES (('$val1','$val2','$val3'),('$val7','$val8','$val9'),('$val21','$val22','$val23'))

so you can query just once.

Hi guys, thanks for the responses, Sky Diploma, your answer was the route I was following but kept going round in cicles (unfortunately I couldn't get the loop to do the same!!). I've had a fiddle with your suggestions but can't get it to go.

Ardav, your answer seems to be the way forwards but I do have a few questions. Firstly, the way you have set it out above, there doesn't seem to be any flexibility in the number of inputs - the way this site works, there could only be one input per person but on other days it could be up to five or six inputs (dependant on the number of races). Secondly, I don't seem to be able to get the values into strings.

This is where I'm at (few adjustments to the form to allow people to choose which races they sign on for).

<form action="" method="post" name="form1" id="form1">
  
  <table align="center"><tr>
              
              <td> Sign on for <?php echo $row_racestoday['seriesname']; ?>, </td> 
              <td align="left"><?php do { ?>
                  <?php echo $row_racestoday['raceynumber']; ?><input name="whichrace[]" type="checkbox" value="<?php echo $row_racestoday['raceunique']; ?>"/>
                  <?php } while ($row_racestoday = mysql_fetch_assoc($racestoday)); ?></td>
                
           </tr>
          <tr valign="baseline">
            <td nowrap="nowrap" align="right" >Helm:</td>
            <td colspan="<?php echo $_GET['races'];?>" ><input type="text" name="helm"></td>
          </tr>
         
          <tr valign="baseline">
            <td nowrap="nowrap" align="right">Crew:</td>
            <td colspan="<?php echo $_GET['races'];?>"><input type="text" name="crew"></td>
          </tr>
          
          <tr valign="baseline">
            <td nowrap="nowrap" align="right">Boat:</td>
            <td colspan="<?php echo $_GET['races'];?>"><input type="text" name="boat"></td>
              
          </tr>
          
          <tr valign="baseline">
            <td nowrap="nowrap" align="right">Sail Number:</td>
            <td colspan="<?php echo $_GET['races'];?>"><input type="text" name="sailnumber" value="" size="20" /></td></tr>
         
            
           
      <tr valign="baseline">
            <td nowrap="nowrap" align="right">&nbsp;</td>
            <td colspan="<?php echo $_GET['races'];?>"><input type="submit" value="Sign On" /></td>
          </tr>
        </table>
        <input name="racecrewid" type="hidden" value="<?php echo $_SESSION['raceteam']; ?>"  />
       
      </form>

And then I'm trying this method to get the details into the db, alas I can't get it to work!!

<?php
   $raceid = $_POST['whichrace'];
$steerer =  $_POST['helm'];
$swimmer = $_POST['crew'];
$boatywoaty = $_POST['boat'];
$knowtheboat = $_POST['sailnumber'];
$todaysteam = $_POST['racecrewid'];

if (isset($_POST['submit']));{

$racypeople=array('helm'=>'$steerer','crew'=>'$swimmer','boat'=>'$boatywoaty','sailnumber'=>'$knowtheboat','raceid'=>'$raceid','raceteamid'=>'$todaysteam');


foreach ($racypeople as $rppp);{ echo $rppp; }

$query= sprintf('INSERT INTO %s (%s) VALUES ("%s")', 'tblcompetitors', implode(', ', array_map('mysql_escape_string', array_keys($racypeople))), implode('", "',array_map('mysql_escape_string', $racypeople)));
   mysql_select_db($database_sailingdb, $sailingdb);
   mysql_query($query) or die(mysql_error("still not working grrr!!")); 
   
   }
   

 
  ?>

Any ideas???

Many thanks

Dave

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.