[Resolved] Inserting multidimensional array into MySQL table

Tracker

Hi PHP people, i've stumbled into something that my current level of MySQL knowledge doesnt allow me to resolve. Perhaps someone can throw in an idea or two. I'll start from afar...
Project involves developing electronic journal for school.
For this question one table from the database is used:

'marks' table with these fields:

id, date, studentid, subjectNo, mark

You select date range and subject No from a form before displaying this one.

Outputting it is pretty easy,
but when you try filling it with values using a reallife school journal like form is where it gets tricky.
So this input form after generation looks roughly like this:
a table with alot of small input fields containing marks and a submit button.

--------------------Subject No.1(Mathematics)------------

------------15.05 16.05 17.05 18.05 19.05 20.50----

student1 |[-] [-] [5] [-] [-] [5]
student2 |[-] [4] [-] [-] [-] [3]
student3 |[1] [5] [3] [-] [-] [3]

.........etc

['Save to database' button]

Marks displayed in [] brackets have the following code:

echo "<td align=\"center\"><input name=\"". $i . ":"  .  $j . "\" type=\"text\" value=\"". $curMark ."\" size=\"1\" maxlength=\"1\" onFocus=\"select();\"></input></td>";

so basically after gathering data from form on output i have a
3d array with students and their marks.

student1[date][mark]
[date][mark]
[date][mark]
..............etc

student2[date][mark]
[date][mark]
[date][mark]
..............etc

student3[date][mark]
[date][mark]
[date][mark]
..............etc

And now, to my question:
To put all these values back into database, i will need do an iteraction cycle through all that array and do a

        if($array[value] != "-") // not empty
        mysql_query("REPLACE INTO 'marks' VALUES etc, etc ");

for each existing mark input field.
As i understand MySQL databases, it usually operates row by row, but seeing as each mark is stored on its separate row, my code produces a wild amount of queries.
Is there a way to put the whole array into database, or at least decrease the amount of SQL queries?

Pig

Your structure is not properly normalized. You need a seperate table for the relationships.

http://www.webhead.cc/main.php?inv=63

Tracker

Could you point out where is the excess data in this table?

The idea here of storing marks in one table is such that to relate mark to a certain student all fields are needed

It follows the logic that one student can have one mark for one subject in one day, so to retrieve the mark for the student for a certain day, script uses the values of

date, studentID and subjectNo to retrieve one mark, and should you remove one of the three columns from the table you will become unable to correctly identify student's mark

I see no errors in normalization

Pig

My apologies, I misread your explanation.

To make sure I understand, you have an array like this:

+-------+-----------+------+------+
| _POST |           |      |      | 
+-------+-----------+------+------+
|       | student_1 |      |      | 
+-------+-----------+------+------+
|       |           | date | mark | 
+-------+-----------+------+------+
|       |           | date | mark | 
+-------+-----------+------+------+
|       |           | ...  | ...  | 
+-------+-----------+------+------+
|       | student_2 |      |      | 
+-------+-----------+------+------+
|       |           | date | mark | 
+-------+-----------+------+------+
|       |           | date | mark | 
+-------+-----------+------+------+
|       |           | ...  | ...  | 
+-------+-----------+------+------+

and you want to be able to loop through it and insert each mark in the table?

I would so something like this:

foreach( $_POST AS $student)
{
     if( $student != "submit" && $student != "subject" ) //add any field names that are not part of the student array
     {
          foreach($student AS $date => $mark)
          {
               mysql_query("INSERT INTO marks (date, studentid, subjectNo, mark) VALUES ('$date', '$student', '" . $_POST['subject'] . "', $mark");

      }
 }
}

Tracker

Exactly, thanks for taking time to get into my explanation

Originally posted by Pig
I would so something like this:

foreach( $_POST AS $student)
{
     if( $student != "submit" && $student != "subject" ) //add any field names that are not part of the student array
     {
          foreach($student AS $date => $mark)
          {
               mysql_query("INSERT INTO marks (date, studentid, subjectNo, mark) VALUES ('$date', '$student', '" . $_POST['subject'] . "', $mark");

      }
 }
}

Yes, this should work - but it will still do a mysql_query for every mark row inserted, and that's too large a load for one form =\,
so what i was originally wondering about was if there is a way to insert data in mysql table not row by row but in larger chunks

Pig

No. MySQL only allows one insert at time. Why is it too much? I run updates that affect 100's of rows at one time.

Tracker

Yes, this will have 100's too with a large amount of students - doesn't that large amount of queries significally affect speed in a multi-user enviroinment?
Well, i suppose i'll have to stick with this way of doing it it anyway, 10x for clearing this up

Pig

I don't think you'll see any kind of noticable slow down. If you find the contrary, you could instead write the queries to a file, and at the end of the day run the entire file of updates and then truncate the file. GL.

bubblenut

To change pig's code so that it only actually updates the database every 10 rows try this

$sql_tpl="INSERT INTO marks (date, studentid, SubjectNo, mark) %s";
$i=0; $insert=array();
foreach( $_POST AS $student)
{
     if( $student != "submit" && $student != "subject" ) //add any field names that are not part of the student array
     {
          foreach($student AS $date => $mark)
          {
             $insert[]="('$date','$student','".$_POST['subject']."',$mark')";
             if($i++==10) {
               mysql_query(sprintf($sql_tpl, implode(',',$insert));
               $i=0; $insert=array();
             }
          }
     }
}
if(count($insert)>0) {
  mysql_query(sprintf($sql_tpl, implode(',',$insert));
}

HTH
Bubble

NB.
You won't be able to do as many inserts in one go as you will with pig's file method as there is a limit on how much you can send MySQL in this fasion. It's pretty high but it does mean you're not going to be able to insert, like, 200 at a time, it'll choke.