Inserting multiple records

[blackbox]

Hi all,

Mayu I start wishing you all a peaceful New Year.

I am trying to write a query that will insert multiple rows of data in to a table. The number of records to insert is held in a variable along with the starting number. i.e

$TotalRooms = 300
$FirstRoomNo = 100

What I want to happen is the first record inserted has a "Room" number of 100 followed by another 199 records each one increments the "Room" number by 1 until all the record have been inserted.

After playing around with ways of trying to do this I now think I need some more eyes and help.

This is my starting code:

$insertSQL = sprintf("INSERT INTO ".$_SESSION['newvalid']." (RecordID, LocID, Room, OutOfService) VALUES (%s, %s, %s, %s)",
                       GetSQLValueString($_POST['RecordID'], "int"),
                       GetSQLValueString($_POST['LocID'] = $_SESSION['newlocid'], "text"),
                       GetSQLValueString($_POST['Room'] = $_SESSION['newroomno'], "text"),
                       GetSQLValueString($_POST['OutOfService'] = 0, "text"));

mysql_select_db($database_Zaint, $Zaint);
  $Result1 = mysql_query($insertSQL, $Zaint) or die(mysql_error());

Many thanks in advance.

[Bonesnap]

If you want a value to increment then you're going to have to run a loop at some point. You can either run a loop and insert your record on each iteration, or use the loop to construct your SQL statement to insert multiple records and then run that upon the completion of the loop. I recommend the second option as it seems more efficient to me.

[sneakyimp]

this code will loop from 100 to 300:

for($i=100; $i<=300; $i++) {
  echo "current room is $i\n";
}

[NogDog]

If doing an insert on each loop iteration, this would be a good reason to use the mysqli or PDO database extensions, so that you could define a prepared statement with bound parameters, then just execute that statement iteratively by updating the bound variables in a loop and then executing the prepared statement.