[RESOLVED] HELP NEEDED: Insert multidimensional array into mysql as records

Joseph_Sliker

Perhaps I did not phrase or title my initial post properly....or maybe the problem or approach I am trying is way off base. Let me ask again.

Hi I am very new at this but am stuck with what may be a fairly simple problem and I really need a clue.

I have constructed an html form that has multiple rows: one row for each student. Each row starts with the values of the student name and ID number the user is to enter a test date and the scores on several tests. Let's say the data fields for each record are Name, ID, Testdate, Score1, Score2, Score3, Score4. The Name and ID fields get filled in by a query run from a previous page, so the user only has to give the test date and the test score data.

The form is constructed such that it contains all the rows and therefore has only a single "submit" button at the end so that the teacher can submit the whole class to the database all at once.

I have figured out how to pass the form data to a subsequent php script using a multidimensional array:

$datarecords = array ($HTTP_POST_VARS['Name'], $HTTP_POST_VARS['ID'], $HTTP_POST_VARS['TESTDATE'], $HTTP_POST_VARS['Score1'], $HTTP_POST_VARS['Score2'], $HTTP_POST_VARS['Score3'], $HTTP_POST_VARS['Score4']);

Here is where I get stuck:

I want to pass each row of this form as separate records to a mysql database. I understand how to do this with single-record forms, but I am stumped about how to go about parsing this multidimensional array described above and formatting the output for mysql INSERT. I suppose it has something to do with nested "for" or "foreach" looping or something like that but I am really having trouble.

The closest I have gotten is the line:
{for ($n = 0; $n < 4; $n++) {for ($m = 0; $m < count($datarecords); $m++) echo ($datarecords[$m][$n]);}echo "<br>\n";}

...this at least sets the values in the proper order, but I don't understand how to fit in the quotes and commas and parentheses and stuff that will be needed to send the data on to my mysql table.

Anybody? (Thanks in advance!)

(note: the "$n<4;" value in the previous statement is temporary....that value needs to be dynamic because the number of rows/records from the original form is going to vary depending on the classroom size).😕

Joseph_Sliker

Anybody?

cgraz

I was playing around with this; not sure if this is what you were looking for.

I built a simple form -- array.php --

<form method="post" action="array2.php">

<select name="datarecords[]" multiple>
<option value=field1>Field 1</option>
<option value=field2>Field 2</option>
<option value=field3>Field 3</option>
<option value=field4>Field 4</option>
</select>

<br><br><input type="submit" value="submit">

-- array2.php --

<?

$fieldnames = "(";
$values = "(";

   foreach($datarecords as $key => $value) {
         if ($fieldnames == "(") {
             $fieldnames .= "$key";
         } else {
             $fieldnames .= ", $key";
         }

     if ($values == "(") {
         $values .= "'$value'";
     } else {
         $values .= ", '$value'";
     }

   }

$fieldnames .= ")";
$values .= ")";

$query = "INSERT INTO table_name $fieldnames VALUES $values";
print $query;

?>

When I selected field1, field3, and field3, this printed

INSERT INTO table_name (0, 1, 2) VALUES ('field1', 'field3', 'field4')

The array is always going to return 0, 1, and 2 if you select 3 items, so I don't know how that would help you because if i select field1 field2 and field3, it would always be 0, 1, 2.

But I don't really understand your post. You have a form with multiple pages, and you pass the values along until the final page where there is one submit button?

What I would suggest is using hidden inputs like shown below.

Then, use a foreach loop that would look something like this

<?

$fieldnames = "(";
$values = "(";

   foreach($_POST as $key => $value) {
         if ($fieldnames == "(") {
             $fieldnames .= "$key";
         } else {
             $fieldnames .= ", $key";
         }

     if ($values == "(") {
         $values .= "'$value'";
     } else {
         $values .= ", '$value'";
     }

   }

$fieldnames .= ")";
$values .= ")";

$query = "INSERT INTO table_name $fieldnames VALUES $values";
print $query;

?>

Note that in the foreach loop, I'm now calling $POST (if your form is set to POST, else use $GET). That should work for you.

Let me know how it goes.

Cgraz

Joseph_Sliker

Thanks so much for the reply/effort! It's clear that I did not convey the nature of my problem very well in my original post. Sorry. Here is an example of what I have in mind:

http://www.creamcityrealestate.com/Xphp/arraytest7.html

(for some reason, php on that server does not seem to be functional at the moment, but you'll at least be able to see a simple example of the type of form I have in mind).

Score1, Score2, Score3, Score4 as I had mentioned in the original post would correspond to say, an individual student's grade in a certain subject or grade on a particular test at a specific point in time.

Each line would be a line for separate students.

Joseph_Sliker

Hmmm, I take that back. php seems to be functional there now. The php script that returns when you hit the submit button contains the following code experiments:

<HTML>
<HEAD>
<TITLE>php result from arraytest7.html form</TITLE>
</HEAD>
<BODY>
<?php

//This definition of the array(s) work fine
$firstcounter = $HTTP_POST_VARS['FIRST']; //this gives the count of values in this field: might be usefull as a row counter if all values are filled in
$datacolumns = array ($HTTP_POST_VARS['FIRST'], $HTTP_POST_VARS['Score1'], $HTTP_POST_VARS['Score2'], $HTTP_POST_VARS['Score3'], $HTTP_POST_VARS['Score4']);

//this gives me the number of fields in my array
echo count($datacolumns);
echo " columns <br>\n";

//slight progress here: maybe a kludge, but it gives me a count of rows in the array (this will vary in the actual form)
echo "<br>\n";
echo count($firstcounter);
echo " rows <br>\n";
echo "<br>\n";

//THIS PRINTS: Sam','70','83','94','90','Tanya','89','104','97','70','Theloneous','80','82','79','88','Sandra','99','95','102','79','
echo "This is sort of on the right track, but needs to be split into separate lines/records in proper format to send to MySQL table <br>\n";
{for ($n = 0; $n < count($firstcounter); $n++) {for ($m = 0; $m < count($datacolumns); $m++) print ($datacolumns[$m][$n]."','");};}
echo "<br>\n";
echo "<br>\n";
//This works but does not put full record on each line
echo "This is the result of two nested foreach loops...does not seem to be what I need. <br>\n";
foreach ($datacolumns as $tempone)
{
foreach ($tempone as $key => $temptwo)
{
echo "$key: $temptwo", "<br>\n";
};}
echo "<br>\n";

?>
</BODY>
</HTML>

cgraz

I may not be understanding you correctly, but if you merely want to input the results of that form into your database, why not do something like. . .

<?

for ($i=0; $i < count($FIRST); $i++) {

   $Name = $FIRST[$i];
   $Score1 = $Score1[$i];
   $Score2 = $Score2[$i];
   $Score3 = $Score3[$i];
   $Score4 = $Score4[$i];

// NOTE I USED now() to insert current date into TESTDATE field
$query = mysql_query("INSERT INTO your_table (Name, TESTDATE, Score1, Score2, Score3, Score4) VALUES ('$Name', now(), '$Score1', '$Score2', '$Score3', '$Score4')");

}

?>

Would that do what you want?

Cgraz

Joseph_Sliker

Man! That looks like the best Christmas present ever!

I think you may be right, but I won't have time to test it today (family, church & stuff).

It looks like it will preserve the relationship of the kid to his/her scores...but will that hold if, say, a cell or two is left blank (ie. the kid was not tested on that particular day?

I suspected that my approach might be a little fussier than necessary...but like I say, I'm new at this. I'm still stuck with the idea of passing and parsing a multidimensional array but your approach appears to be quite efficient (and easy to comprehend). The more I think about it, the more I like it.

A step before the data is actually submitted to the form (ie. the action that takes place first after clicking the Submit button, I want the teacher to have one last look at the records she/he is about to submit (and a "Back" button to give a chance to correct any errors spotted). I think I can figure that out.

Thanks again, and happy holidays!

Joe

cgraz

No problem.

If you want the user to look over the info one final time before submitting, just have the form go to like preview.php; which would print out what they entered. Then have a back link and a submit form button. But to pass the variables to the third page (when they click submit), you'd need to use hidden input fields with the values already set (well i guess there are a variety of ways to do this, but this would probably be my approach for this project). Then make a submit button that submits the hidden fields with the values from the previous page onto your final (third page) which would run your INSERT query.

Hope this helps.

Cgraz

Joseph_Sliker

Okay, NOW what am I doing wrong?....grrrr

I'm trying to make the initial "Submit" script show the rows to be input, but the following code only displays the first row of results:

The actual input form/script is at:
http://www.creamcityre.com/Xphp/arraytest8.html

<?php
$FIRST = $HTTP_POST_VARS['FIRST'];
$Score1 = $HTTP_POST_VARS['Score1'];
$Score2 = $HTTP_POST_VARS['Score2'];
$Score3 = $HTTP_POST_VARS['Score3'];
$Score4 = $HTTP_POST_VARS['Score4'];

for ($i=0; $i<count($FIRST); $i++) {
$FIRST = $FIRST[$i];
$Score1 = $Score1[$i];
$Score2 = $Score2[$i];
$Score3 = $Score3[$i];
$Score4 = $Score4[$i];

print "($FIRST, now(), $Score1, $Score2, $Score3, $Score4)";};

?>😕

Joseph_Sliker

This code is getting closer to what I think I need:

<?php

$FIRST = $HTTP_POST_VARS['FIRST'];
$Score1 = $HTTP_POST_VARS['Score1'];
$Score2 = $HTTP_POST_VARS['Score2'];
$Score3 = $HTTP_POST_VARS['Score3'];
$Score4 = $HTTP_POST_VARS['Score4'];

for ($i=0; $i<count($FIRST); $i++)
{
echo "('$FIRST[$i]', '".date("Ymd")."', '$Score1[$i]', '$Score2[$i]', '$Score3[$i]', '$Score4[$i]'),<br>";
}

Joseph_Sliker

...going through some of my earliest problems/questions. This was over ten years ago. Thanks for your help way back then.