Multiple Insert into one table

Muses

To sum this up, this table is for race level charts (there are 16 races each with 60 levels)

table: race_level
racelevel_id, race_id, level, mod, vitality, health_point, act_dice, experience

this is my insert statement

if(isset($_POST['submit'])){// handle the form
// Connect to the database.
require_once ('inc/connect.php');
        // Query the database.    

// The insert condition.
$query = "INSERT INTO race_level (race_id, level, mod, vitality, health_point, act_dice, experience) VALUES ('$race_id', '$level', '$mod', '$vitality', '$health_point', '$act_dice','$experience')"; 

        $result = @mysql_query ($query); // Run the query.

        if ($result) { // If it ran OK.

            // Send an email, if desired.
            echo '<p>Thank you for adding this registration!</p><p><a href=add_race_level.php target=_self>Back</a></p>
';
                exit();                

        } else { // If it did not run OK.
            // Send a message to the error log, if desired.
            echo '<p>Your information could not be registered due to a system error. We apologize for any inconvenience.</p>'; 
        }        

    mysql_close(); // Close the database connection.

} // End of the main Submit conditional.

Now I semi get the jest of
INSERT INTO table
(field1, field2)
VALUES
('value1','value2'),
('value3','value4'),
('value5','value6')

by following that example would it be:

 $query = "INSERT INTO race_level 
  (race_id, level, mod, vitality, health_point, act_dice, experience)
VALUES
  ('$race_id', '$level', '$mod', '$vitality', '$health_point', '$act_dice','$experience'),
  ('$race_id', '$level', '$mod', '$vitality', '$health_point', '$act_dice','$experience'),
  ('$race_id', '$level', '$mod', '$vitality', '$health_point', '$act_dice','$experience')

and if so do I have to repeat this 60 times as their are 60 levels for each race and I would like to add each races levels in one go?

Thanks ahead of time for any help or links to help 🙂

sarahk

It sounds likely but you can build you query like this

$query = "INSERT INTO race_level  

  (race_id, level, mod, vitality, health_point, act_dice, experience) 
VALUES ";
$join = '';
for ($i = 0; $i < 60; $i++)
{
  $query .= "{$join}('{$race_id}', '{$i}', '{$mod}', '{$vitality}', '{$health_point}', '{$act_dice}','{$experience}')\n"; 
  $join = ', '; 
}

which is a bit easier, especially if you decide to change something 🙂

Sarah

Muses

Not sure if I implented it into my code wrong or not but I received the error "Your information could not be registered due to a system error. We apologize for any inconvenience."


if(isset($_POST['submit'])){// handle the form
// Connect to the database.
require_once ('inc/connect.php');
        // Query the database.    

// The insert condition.
$query = "INSERT INTO race_level  

  (race_id, level, mod, vitality, health_point, act_dice, experience)
VALUES ";
$join = '';
for ($i = 0; $i < 60; $i++)
{
  $query .= "{$join}('{$race_id}', '{$i}', '{$mod}', '{$vitality}', '{$health_point}', '{$act_dice}','{$experience}')\n"; 
  $join = ', '; 
}

        $result = @mysql_query ($query); // Run the query.

        if ($result) { // If it ran OK.

            // Send an email, if desired.
            echo '<p>Thank you for adding this registration!</p><p><a href=add_race_level.php target=_self>Back</a></p>
';
                exit();                

        } else { // If it did not run OK.
            // Send a message to the error log, if desired.
            echo '<p>Your information could not be registered due to a system error. We apologize for any inconvenience.</p>'; 
        }        

    mysql_close(); // Close the database connection.

} // End of the main Submit conditional.

sarahk

echo out the query and then put it into mysql manually and you'll see what the error is.

Alternatively echo out mysql_error() at the end of your custom message.

Personally I use

$result = mysql_query($sql) or die(mysql_error(). "<br>\n{$sql}");

and that gives me what I need

Because I debug I'm confident that real users won't see the message, just me during development 🙂

Sarah

Muses

Okay it inserted problem is it inserted the same level information 60 times. Not every level is the same as points, experience and such increases with the level.

As it appears:
0 +2 18 9 1d20 0
1 +2 18 9 1d20 0
2 +2 18 9 1d20 0
3 +2 18 9 1d20 0
4 +2 18 9 1d20 0

As it should look

1 +2 17 8 1d20 0
2 - 19 9 1d20 200
3 - 20 9 1d20 500
4 +2 22 9 1d20 800

sarahk

Ok.

Depending on the relationship between points and level etc you can either

put the maths into the for() loop and have it update
code it manually
work from an array which is only slightly better than manual

Sarah

Muses

I may have to do this manually one by one via a simple form.

As you can see from just the snippet of the level chart

Vitality goes up by 2, then 1 (17, 19, 20, 22, 23, etc)

HP goes up by the same practice but some levels the HP do not change.

Experience starts in increments of 200, 500, 800 (goes up by 300 each) then at 800 it goes up to 1200, from 1200 to 2400, 2400 to 4800, 4800 to 9600, 9600 to 14400.

So as you can see the numerical pattern isnt the same.

I do thank you for all your help.