How do i build an insert using a loop?

bululu

Hi, I have a somewhat complex form. My needs are unique. The form will be used on a School intranet. I used a table to lay it out as css was not cut for it. So it produces php variable like so:
$fname1, lname1, $eng1,$mat1,$ph1,$che1,$bio1,$geo1,$his1,$fn1
$fname2, lname2, $eng2,$mat2,$ph2,$che2,$bio2,$geo2,$his2,$fn2
$fname3, lname3, $eng3,$mat3,$ph3,$che3,$bio3,$geo3,$his3,$fn3
...
up to as many as 40 records depending on the number os students.

So the form is working well and i am able to insert data into mysql1 extension. The way I am doing it is time consuming because I have it as follows;
$query = "INSERT INTO pupils_records (date, fname1, lname2, exam_no1, eng, bio, che,...)
VALUES (NOW(),'$fname1', '$lname1', '$exam_no1', '$eng1','$bio1, '$che1', ...), (NOW(),'$fname2', '$lname2', '$exam_no2', '$eng2','$bio2, '$che2', ...), ... " ;

Now I want to use a loop so that I can pack those students records as arrays, each student record as one array, then pack the arrays in one array and then loop through the master array to build an insert query. I would use conditionals to make sure an array is only set of it has key values since the number of students will vary and the loop would use count to ascertain how many rows are available for the query.

Is this even possible? I have gone as far as capturing my variables into arrays, but I am having a hard time creating an insert using a loop. May be my theory is misguided, is there a better way to do this and still avoid repeating my self on the query?

I will appreciate your help, and thank you in advance.

dagon

change the form names so you have
name="fname[]", name="lname[]" etc then you have easy arrays to work with

bululu

Thank you for your help. My problem is on looping an array.

Capturing data into arrays is not my problem. That method where i use fname[], lname[] etc will yield a string of names which i will then explode with php. I am comfortable with my method where i capture variable separately.

My question is how to use a loop on an array, I can adapt a general idea of a loop used to insert data into mysql. My fiorm is working as expected.

bululu

I thought my method for the form was good, it turns out Dagon's suggestion is the way to go. I have changed the form into fname[], lname[] etc and I have easier arrays to work with. Now I still need help inserting the whole thing into MSQL.

Derokorian

<?php
$sql = "INSERT INTO pupils_records (date, fname1, lname2, exam_no1, eng, bio, che,...) VALUES ";
foreach($masterarray as $subarray) {
   $sql .= "(NOW( )";
   foreach($subarray as $value) {
      $sql .= ", $value";
   }
   $sql .= "), ";
}
$sql = substr($sql,0,-2); //removes extraneous , at end.
?>

Something like that should work.

bululu

I have not been able to do any programing today, but I analysed the your logic and I am convinced it will work. I was only ending on the outer foreach so I was inserting arrays into the Database. The inner foreach seems to break the up the subarrays making their elements available for insertion.

I will confirm shortly after I implement it in case it will help someone else as I see so many people looking to do this sort of thing.

Thank you for taking time to help me. You are simply a PHP Wizard!🆒

bululu

The Solution suggested is;

<?php
$sql = "INSERT INTO pupils_records (date, fname1, lname2, exam_no1, eng, bio, che,...) VALUES ";
foreach($masterarray as $subarray)
Echo $subarray;
$sql .= "(NOW( )";
foreach($subarray as $value) {
$sql .= ", $value";
}
$sql .= "), ";
}
$sql = substr($sql,0,-2); //removes extraneous , at end.
?>

INSERT INTO pupils_records (date, fname, lname) VALUES (NOW( ), 'Anne', 'Joice'), (NOW( ), 'Willams', 'Robert')

The query works but not as expected because it inserts first names in the first row and last names in the second column. The desired result is to insert first names in first name column and last names in last name column and so on since there will be more columns. I am trying to manipulate the inner array to be able to do that but so far no luck.

More help will be deeply appreciated.

Derokorian

ok I Thought you're arrays were such that:

(fname, lname, etc)
(fname, lname, etc)
(fname, lname, etc) but you're saying you got

(fname,fname,fname,etc)
(lname,lname,lname,etc) yes?

So the master array is->
(array(fname),array(lname))?

bululu

Yes. I have a form that captures input from users. It has 1 columns. Each column can capture up to 50 entries eg fname[], lname[], exam_no[] etc. So then i pack these arrays as;
master-array=array($fname,$lname,exam_no ...)
The databae table has columns like fname, lname, exam_no etc. So the trouble is how to create an insert query that inserts all $fname elements into fname column and $lname array elements into next column which is lname and so on. All solution i come across build quries that insert data by row not by column, and the do it in such as way that array elements are inserted horizontaly such that in this case i end up with all first names in the top rows and last names follow by row until i reach the last array in the multi-dimentional arry.

Perhaps i attempting to do something not possible or that mayrequire unorthodox programming methods! Let me hear from experts if i am attempting to wal on a thin rop!😕

Derokorian

<?php
//untested
$entries = array(); 
$entry_count = count($fname); // Or a key field, so as not to insert 50 records when there are only 26 students

for($i=0;$i<=$entry_count;$i++) {
   $entries[$i] = array(
         $fname[$i],
         $lname[$i],
         $exam_no[$i]
      );
}

$sql = "INSERT INTO pupils_records (date, fname1, lname2, exam_no1, eng, bio, che,...) VALUES ";

foreach($entries as $entry) {
   $sql .= "(NOW( )";
   foreach($entry as $value) {
      $sql .= ", $value";
   }
   $sql .= "), ";
}
$sql = substr($sql,0,-2); //removes extraneous , at end.

You could alternatively build it in the initial loop:


$sql = "INSERT INTO pupils_records (date, fname1, lname2, exam_no1, eng, bio, che,...) VALUES ";
$entry_count = count($fname);
for($i=0;$i<=$entry_count;$i++) {
   $sql .= "(NOW( ),'{$fname[$i]}','{$lname[$i]}',...), \n";
}

$sql = substr($sql,0,-2); //removes extraneous , at end.

Remember this is just pseudo code, so there may be some syntax errors.

bululu

Thanks so much for your effort, i will post back as soon as i finish implementing the code. 🙂

bululu

$sql = "INSERT INTO pupils_records (date, fname1, lname2, exam_no1, eng, bio, che,...) VALUES ";
$entry_count = count($fname);
for($i=0;$i<=$entry_count;$i++) {
$sql .= "(NOW( ),'{$fname[$i]}','{$lname[$i]}',...), \n";
}

$sql = substr($sql,0,-2); //removes extraneous , at end.

Went with option 2 suggested by Derokorian, i did not try the first one as i thought the second looked more compact and with less code. It worked flawlessly with one little adjustment, removing ANY white space just before the \n on the query.

$sql .= "(NOW( ),'{$fname[$i]}','{$lname[$i]}',...), \n";
to
$sql .= "(NOW( ),'{$fname[$i]}','{$lname[$i]}',...),\n";

Hope this helps someone else as well because you are able to excute a multi insert query onto a MySQL DB, and solutions else where involve mountains of code and endless concatinations that will concatinate your brain to your foot!

Is is by far the best easy to modify solution involving a mult-insert loop.

bradgrafelman

Did you make sure to sanitize all of the data (e.g. with [man]mysql_real_escape_string/man for string data) before placing it into the SQL query string?

bululu

Yes, I used that in addition to other functions that nuetralise scripts