Inserting many rows in PHP

phprooky

Hi everybody,
I have the following form and I need help from you to insert all this data at the same time into mysql database:

<form method="post" action="insert.php">
  <table width="594" border="0">
    <tr>
      <td colspan="4">Results</td>
    </tr>
    <tr>
      <td width="144">Language</td>
      <td width="144">Room</td>
      <td width="144">Name</td>
      <td width="144">Voucher</td>
    </tr>
    <tr>
      <td><input name="language[0]" type="text" id="languaje[0]" /></td>
      <td><input name="room[0]" type="text" id="room[0]" /></td>
      <td><input name="name[0]" type="text" id="name[0]" /></td>
      <td><input name="voucher[0]" type="text" id="voucher[0]" /></td>
    </tr>
    <tr>
      <td><input name="language[1]" type="text" id="language[1]" /></td>
      <td><input name="room[1]" type="text" id="room[1]" /></td>
      <td><input name="name[1]" type="text" id="name[1]" /></td>
      <td><input name="voucher[1]" type="text" id="voucher[1]" /></td>
    </tr>
    <tr>
      <td><input name="language[2]" type="text" id="language[2]" /></td>
      <td><input name="room[2]" type="text" id="room[2]" /></td>
      <td><input name="name[2]" type="text" id="name[2]" /></td>
      <td><input name="voucher[2]" type="text" id="voucher[2]" /></td>
    </tr>
  </table>
</form>

sneakyimp

Your form could be better constructed. As it is currently constructed, the page that this form submits to (insert.php) must somehow guess how many records there are. You could add a hidden input to your form that says there are 3 records, or the insert.php page could check to see how big one of the four arrays is:

$num_records = count($_POST['language']); // or you could check room, name, voucher

Once you know how many records there are, you can loop and create a mysql statement for each record:
EDIT: updated this code with a little more detail

$num_records = count($_POST['language']); // this assumes that  room, name, voucher all have the same number of items and the items much up in a one-to-one fashion

// connect to the db
mysql_connect  ('my.mysql.server.mydomain.com', 'username', 'password')
  or die ('could not connect!');
mysql_select_db('my_database')
  or die ('could not select db');


for($i=0; $i<$num_records; $i++) {
  $sql = "INSERT INTO some_table (language, room, name, voucher) VALUES (
    '" . mysql_real_escape_string($_POST['language'][$i]) . "',
    '" . mysql_real_escape_string($_POST['room'][$i]) . "',
    '" . mysql_real_escape_string($_POST['name'][$i]) . "',
    '" . mysql_real_escape_string($_POST['voucher'][$i]) . "'
  )";
  // do the query
  mysql_query($sql)
    or die('query failed:' . mysql_error());
}

IMPORTANT: The code I just wrote there doesn't do any validation on your data so some mean-spirited visitor might insert all kinds of bogus garbage in your database. You should really check all the values to make sure they are ok. Something like this before you start inserting anything:

$valid_languages = array('english', 'french', 'spanish');

foreach($_POST['language'] as $lang) {
  // this comparison is case-sensitive so while english works, ENGLISH will not
  if (!in_array($lang, $valid_languages)) {
    die($lang . ' IS NOT A VALID LANGUAGE');
  }
}

You should always validate input from a form or you are just asking for trouble from the h4xorz

phprooky

Hi sneakyimp and thanks a lot for your quick reply,
Do you think that using check boxes will solve the problem to know how many records there are?

sneakyimp

I'm not sure what you mean.

phprooky

Never mind, I think I almost have what I want with the help that you are giving me. Do you think is possible to have another insert before, just to insert one row?

sneakyimp

Inserting one row is easy...just take the code inside that loop and copy it to a spot before the loop.

phprooky

Hi and thanks again for your reply,
One more last question is: The insert where I record one row, I will be inserting all the data in a different table and the second insert with many rows in another table, so I would like to know how can I add something like an id to make a join between the two tables.
Can you help me with this last question please?

sneakyimp

Read about the insert syntax for mysql:
http://dev.mysql.com/doc/refman/5.0/en/insert.html

you might also find this useful: [man]mysql_insert_id[/man]

If you insert a record into a table that has an auto-increment field, that function gives you the value of the last id you inserted.

phprooky

Hi again,
I have read the articule that you just sent me, but I'm still a little bit confuse. Let's say that I add an auto-increment field to both tables to make a join select later. If I do that, that means that ech row inserted when I insert many rows at the same time will have a different id number? Is that what do you mean?

phprooky

I'm very confuse now because every time I insert only 2 rows, an empty row is inserted too. Is there a way to control this? Should I delete the auto-increment field? How can I make a join later with both tables to display the information inserted in the tables?

sneakyimp

If you insert many rows at a time like this:

INSERT INTO orders (customer_cust_id, orderdatetime, message, taxrate, shippingprice)
SELECT '1', NOW(), null, taxrate, shippingprice FROM customer

then you are not going to be able to use [man]mysql_insert_id[/man] in any meaningful way because it would probably just return the id of the last record that was inserted.

If you do a JOIN on a table, then it usually assumes that records already exist and that there is some field common to both tables that is meant to link them together. Something like this:

SELECT * FROM table1
LEFT JOIN table2
ON table2.table_1_id=table1.id

That is to say, you specifically defined a field in table2 called 'table_1_id' and the whole point of that field is to connect records in table2 to records in table1. I would imagine that table1.id is an autoincrement field. table2.table_1_id IS NOT.

Sounds to me like you want to insert a record in table1, remember the id that was created, and then insert some records in table 2 that use that id, right? If so, then read the docs on [man]mysql_insert_id[/man]. Check the examples. I'm not really sure how to explain it more clearly.

phprooky

Ok, now I think I'm understanding a little more. So what about the empty rows, how can I control that? For example: If I leave a blank row, how can I avoid inserting this blank row into the database?

sneakyimp

I have no idea what your code is doing and I have little desire to troubleshoot it. You should try to read it and understand where the rows are getting inserted and study that. Maybe you'll figure it out.

phprooky

I'm using the same code you sent me.

sneakyimp

what code would that be?

phprooky

This is the code, but you said that this works only if all the rows are filled up and when you leave an empty row that line is also inserted in my database with empty values. I would like to skip those empty rows.

$num_records = count($_POST['language']); // this assumes that  room, name, voucher all have the same number of items and the items much up in a one-to-one fashion 

// connect to the db 
mysql_connect  ('my.mysql.server.mydomain.com', 'username', 'password') 
  or die ('could not connect!'); 
mysql_select_db('my_database') 
  or die ('could not select db'); 

$valid_languages = array('english', 'french', 'spanish'); 

foreach($_POST['language'] as $lang) { 
  // this comparison is case-sensitive so while english works, ENGLISH will not 
  if (!in_array($lang, $valid_languages)) { 
    die($lang . ' IS NOT A VALID LANGUAGE'); 
  } 
}

for($i=0; $i<$num_records; $i++) { 
  $sql = "INSERT INTO some_table (language, room, name, voucher) VALUES ( 
    '" . mysql_real_escape_string($_POST['language'][$i]) . "', 
    '" . mysql_real_escape_string($_POST['room'][$i]) . "', 
    '" . mysql_real_escape_string($_POST['name'][$i]) . "', 
    '" . mysql_real_escape_string($_POST['voucher'][$i]) . "' 
  )"; 
  // do the query 
  mysql_query($sql) 
    or die('query failed:' . mysql_error()); 
}

sneakyimp

This is why I suggested that you restructure your form with input names like [records][language][] instead of [language][$i]. If you do that, then you can use a [man]foreach[/man] loop instead of a for loop.

As your form is currently constructed, you need to put your db insert code inside an IF statement that checks to see if the row is empty before trying to insert. you can either check one field or check all of them.

phprooky

Hi again sneakyimp,
I did what you suggested and now I'm using the ForEach loop, but I'm having a little problem when I insert my second or third line. Is not inserting the value in the first column 2nd line and if I add type 3 lines it inserts the value of my 1st column-3rd line in my second line.
Here are my codes:
1) Form

<form method="post" action="insert2.php">
<?php for($i = 0; $i < $num_segments; $i++) {?>
  <table width="251" border="0" cellspacing="1" cellpadding="1">
    <tr>
      <td width="148"><input name="language[]"></td>
      <td width="52"><input name="room[]"></td>
      <td width="19"><input name="name[]"></td>
      <td width="19"><input name="voucher[]"></td>
    </tr>
    <tr>
      <td><input name="language[]"></td>
      <td><input name="room[]"></td>
      <td><input name="name[]"></td>
      <td><input name="voucher[]"></td>
    </tr>
    <tr>
      <td><input name="language[]"></td>
      <td><input name="room[]"></td>
      <td><input name="namne[]"></td>
      <td><input name="voucher[]"></td>
    </tr>
  </table>

  <?php } //EndFor formfields for number of segments needed?>
  <input type="submit" name="Submit" value="Entrada Datos" />
</form>

2) PHP Code

<?PHP
// connect to the db 
$con = mysql_connect ('HOST', 'USERNAME', 'PASSWORD');
if (!$con)
  { 
  die ('Could not connect: ' .mysql_error()); 
  }
mysql_select_db('MYDATABASE', $con);

foreach($_POST['language'] as $idx => $val) {
$language = $val;
$language = $_POST['language'][$idx];
$room = $_POST['room'][$idx];
$name = $_POST['name'][$idx];
$voucher = $_POST['voucher'][$idx];
  $sql = "INSERT INTO mytable (language, room, name, voucher) VALUES ( 
    '$language', '$room', '$name', '$voucher')"; 
  $result = mysql_query($sql);
} //EndForEach segment
echo "1 record added";
?>

sneakyimp

You obviously didn't really read the documentation on [man]foreach[/man]. You need to actually think about this rather than just going through the motions.

You have to change the name on all your inputs so they look like this:

    <tr>
      <td><input name="records[1][language]"></td>
      <td><input name="records[1][room]"></td>
      <td><input name="records[1][name]"></td>
      <td><input name="records[1][voucher]"></td>
    </tr>
    <tr>
      <td><input name="records[2][language]"></td>
      <td><input name="records[2][room]"></td>
      <td><input name="records[2][name]"></td>
      <td><input name="records[2][voucher]"></td>
    </tr>
   ETC.

Then your foreach loop would look something like this:

foreach($_POST['records'] as $record) {
  $language = $record['language'];
  $room = $record['room'];
  $name = $record['name'];
  $voucher = $record['voucher'];
  $sql = "INSERT INTO mytable (language, room, name, voucher) VALUES (
    '$language', '$room', '$name', '$voucher')";
  $result = mysql_query($sql);
}

If you have a problem with any empty values in your $_POST array, then your form probably has errors and needs to be looked at more closely.

Programming is detail-oriented work. You need to start focusing a bit harder.

phprooky

Thanks a lot master sneakyimp for teaching me. I'm doing my best, but is just that I'm a newby and I really love PHP, please understand and forgive me. I'm working real hard to learn as fast as I can. I will never dissapoint you.
If I still having problems, I'll let you know and thanks again for helping me.