How to add more than one record to mysql database

[Webville312]

Hello,
I want to submit multiple records into a mysql database, and I have hit a snag.
I am developing a web application that displays student records, and upon displaying student records per class, I want to be able to add marks for beginning of term, mid term and end of term.
The problem is that, since I am not sure of how many rows of records the sql statement will return, I can't declare different variables for each student mark.
I tried using the foreach loop but it kept showing me an error in declaration of variables. The loop is is;

<?php
$class = $_POST['class_name'];
$subject = $_POST['subject'];
$term = $_POST['term'];
$search_year = $_POST['search_year'];

if($_POST['add_marks'] ==1)
{
include('includes/connect.php');

foreach($_POST['bot'] as $bot && $_POST['mt'] as $mt && $_POST['eot'] as $eot)
{
$insert = ""INSERT INTO marks(beginning_of_term,mid_term,end_of_term) VALUES($bot,$mt,$eot);
$execute_insert = mysql_query($insert);
}
}
?>

Here is the code that I am working with to display the records from the database; then insert marks as per the student record returned;

 <?php
    include('includes/connect.php');
            // select record from mysql 
            $sql="SELECT DISTINCT * FROM students WHERE school_id='$sch_id' AND class='$class'";
            $result=mysql_query($sql);
            if(mysql_num_rows($result) == 0){ echo "<font color='red' size='3'>No Registered Students yet</font>&nbsp;&nbsp;<a href='teacher_home.php?page=new_student&Id=$sch_id'>Add Student"; 
            }
                else
                {
          echo "<table width='850' border='0'>
                <tr style='font-size: 12px; font-weight: bold; color: rgb(0, 153, 204); background: none repeat scroll 0% 0% rgb(230, 249, 217);'>
                  <td width='400'>Student</td>
                  <td width='200'>Reg No</td>
                  <td width='130'>Class</td>
                  <td width='150'><div align='center'>Beginning of Term</div></td>
                  <td width='150'><div align='center'>Mid Term</div></td>
                  <td width='150'><div align='center'>End of Term</div></td>
                  </tr>";
                  ?>
                  <?php
                    $num=1;
                        while($rows=mysql_fetch_array($result)){
                        $num++;
                        $skizzy = $rows['school_id']; 
                        if(($num%2)!=0){
                        $bg="#FFFF99";
                        }else{
                        $bg="#FFFFFF";
                        }
                        ?>

                <tr bgcolor="<?php echo $bg; ?>">
                  <td><?php  echo $rows['firstName']." ".$rows['lastName']." ".$rows['otherName'];  ?></td>
                  <td><?php  echo $rows['regNo']; ?></td>
                  <td><?php  echo $rows['class']." ".$rows['stream']; ?></td>
                    <td><div align="center"><input type="text" name="bot" size="10" /></div></td>
                  <td><div align="center"><input type="text" name="mt" size="10" /></div></td>
                  <td><div align="center"><input type="text" name="eot" size="10" />
                  </div></td>
              </tr>
                  <?php
                        }
                }
                        ?>
                          <tr>
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                    <td><div align="center"><input type="image" name="submit" id="submit" value="Submit" style="border:none" src="image/submit.jpg" />&nbsp;&nbsp;<input type="hidden" name="add_marks" value="1" /></div></td>
                  <td><div align="center">&nbsp;</div></td>
                  <td><div align="center">&nbsp;
                  </div></td>
              </tr>
              </table>

THANKS IN ADVANCE

[broj1]

The way you use foreach looks strange to me.

foreach($_POST['bot'] as $bot && $_POST['mt'] as $mt && $_POST['eot'] as $eot) {
    $insert = ""INSERT INTO marks(beginning_of_term,mid_term,end_of_term) VALUES($bot,$mt,$eot);
    $execute_insert = mysql_query($insert);
}

The PHP manual says:

foreach (array_expression as $value)
    statement
foreach (array_expression as $key => $value)
    statement

$_POST['bot'] as $bot && $_POST['mt'] as $mt && $_POST['eot'] as $eot) does not seem to be an expression that returns an array (or am I wrong?).

And the insert statement has two double quotes on the beginning and none at the end.

$insert = ""INSERT...

Maybe you correct these errors first and then post your error messages.

[broj1]

And also check for existence of $_POST values before using them in a query:

if(isset($_POST['bot']) && isset($_POST['mt']) && isset($_POST['eot'])) {

    $query = "...";
} else {

    // handle the error as you see fit
    die('Not all the values were supplied');
}

[Webville312]

thanks for the replies, bt then; I have read through the post about updating mysql records. Unlike that post, where the user was enabling and disabling user records [one at a time]; for me I want to add marks for all student records that are returned, and I can't seem to find the way to do that!!

[broj1]

I am not sure if I understand th question correctly:
- on a webpage you have a table of students with their name, reg. no, cals etc and three input fields do BOT, MOT and EOT marks
- you get student data (except for marks) from the students table
- input fields are initialy empty
- someone fills in the values for marks and you want to insert those marks into the marks table

If this is correct then first thing you have to enclose the whole table in form tags:

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

Then you have to correct the name attributes for input fields. They have to be unique and tied to a student. You can use regNo field for that. Also add a hidden field in each row that will hold the regNo:

<?php
    // construct the name attributes for the current row
    $bot_name = 'bot-' . $rows['regNo'];
    $mt_name = 'mt-' . $rows['regNo'];
    $eot_name = 'eot-' . $rows['regNo'];

// the name for the hidden field with the regNo
    $reg_name = 'reg-' . $rows['regNo'];
?>

<!-- This is one row of a table -->

<tr bgcolor="<?php echo $bg; ?>">
<td><?php echo $rows['firstName']." ".$rows['lastName']." ".$rows['otherName']; ?></td>
<td><?php echo $rows['regNo']; ?></td>
<td><?php echo $rows['class']." ".$rows['stream']; ?></td>
<td><div align="center"><input type="text" name="<?php echo $bot_name; ?>" size="10" />
<input type="hidden" name="<?php echo $reg_name; ?>" value="<?php echo $rows['regNo']; ?>" /></div></td>
<td><div align="center"><input type="text" name="<?php echo $mt_name; ?>" size="10" /></div></td>
<td><div align="center"><input type="text" name="<?php echo $eot_name; ?>" size="10" />
</div></td>
</tr>

In process.php (or in the same file as the form is) you then get all the values in an $_POST array which could look like:

$_POST = array(
    'reg-123' => '123', // regNo for student 123
    'bot-123' => 'A',   // BOT marks for student 123
    'mt-123' => 'A',    // MT marks for student 123
    'eot-123' => 'B',   // EOT marks for student 123
    'reg-456' => '456', // regNo for student 456
    'bot-456' => 'A',   // BOT marks for student 456
    'mt-456' => 'A',    // MT marks for student 456
    'eot-456' => 'B'    // EOT marks for student 456
)

<?php
if(isset($_POST) and !empty($_POST)) {

    // first build an array of registration numbers
    $reg_numbers = array();
    foreach($_POST as $val) {

        if(substr($val, 0, 3) == 'reg') {

            $reg_numbers[] = substr($val, 4);
        }
    }

    // initialize the string for the INSERT query
    // I have added the regNo field since I think it is the key
    $q = 'INSERT INTO marks (reg_no, beginning_of_term, mid_term, end_of_term) VALUES ';

    // now you have an array of all the regNo, build the insert query
    foreach($reg_numbers as $regNo) {

        // temporary variables for keys in $_POST array
        $bot_key = 'bot-' . $regNo;
        $mt_key = 'mt-' . $regNo;
        $eot_key = 'eot-' . $regNo;

        // read the values for this regNo from $_POST
        // note I have used a substr function to tahe only the first character
        // of the $_POST value as a security measure against sql injection
        // you can use your security measures, depending on vhat the
        // values for marks are
        if(isset($_POST[$bot_key])) {
            $bot_mark = substr($_POST[$bot_key], 0, 1);
        } else {
            $bot_mark = '';
        }

        if(isset($_POST[$mt_key])) {
            $mt_mark = substr($_POST[$mt_key], 0, 1);
        } else {
            $mt_mark = '';
        }

        if(isset($_POST[$eot_key])) {
            $eot_mark = substr($_POST[$eot_key], 0, 1);
        } else {
            $eot_mark = '';
        }

        // concatenate values to the query
        $q .= "($regNo, '$bot_mark', '$mt_mark', '$eot_mark'), ";
    }

    // remove the last coma and space from the query
    $q = rtrim($q, ', ');

    // now insert the data into database
    ...
}
?>

[Bachu]

In form

<tr bgcolor="<?php echo $bg; ?>">
                  <td><?php  echo $rows['firstName']." ".$rows['lastName']." ".$rows['otherName'];  ?></td>
                  <td><?php  echo $rows['regNo']; ?></td>
                  <td><?php  echo $rows['class']." ".$rows['stream']; ?></td>
                    <td><div align="center"><input type="text" name="bot[]" size="10" /></div></td>
                  <td><div align="center"><input type="text" name="mt[]" size="10" /></div></td>
                  <td><div align="center"><input type="text" name="eot[]" size="10" />
                  <input type="hidden" value="<?php echo $rows['school_id']; ?>" name="schoolId[]"> 
                  </div></td>
              </tr>

In action page

if($_POST['add_marks'] ==1)
{
include('includes/connect.php');
$bots =$_POST['bot'];
$mts=$_POST['mt'];
$eots=$_POST['eot'];
    foreach($bots as $key=>$bot)
    {
    $insert = "INSERT INTO marks(beginning_of_term,mid_term,end_of_term) VALUES('$bot','$mts[$key]','$eots[$key]')";
    $execute_insert = mysql_query($insert);
    }
}

[broj1]

@Bachov Varghese
Your solution is more elegant, good stuff. The query could be made in one go as well:

if($_POST['add_marks'] ==1)
{
    include('includes/connect.php');
    $bots =$_POST['bot'];
    $mts=$_POST['mt'];
    $eots=$_POST['eot'];

    $insert = "INSERT INTO marks(beginning_of_term,mid_term,end_of_term) VALUES ";

    foreach($bots as $key=>$bot)
    {
        $insert .= "('$bot','$mts[$key]','$eots[$key]'),";
    }

    insert = rtrim(insert, ',');

    $execute_insert = mysql_query($insert);
}

[Webville312]

Thanks alot broj1 and Bachov Varghese!! I really appreciate the replies. However, I have tried it out, and I am getting an error;
"Invalid argument supplied for foreach()". What does that mean??

[broj1]

$bots is an array of beginning of term marks copied from $_POST['bot'], which is an array of beginning of term marks returned from your form. $bots serves as a parameter for the foreach loop and is expected to contain the same numnber of marks as the arrays $mts and $eots.

In case $bots is not set (does not exist) you get the above error. To figure out the value of $_POST (used for $bots) put this debug code in the beginning of your action page:

die(print_r($_POST, 1));

and post here the output. This command will output the contents of $_POST array after submitting the marks and stop the script.

[Webville312]

I still get the same error "Invalid argument supplied for foreach() in C:\wamp\www\Autonomy\add_marks.php on line 18"

[broj1]

You should not if you put the above statement in the action page. Which is your action page? Is it the same page as the form or is it a separate page? You have to check for the $_POST to find a bug. Maybe you post the whole script.

[Bachu]

if($_POST['add_marks'] ==1)
{
include('includes/connect.php');
$bots = array();
$mts = array();
$eots = array();

$bots =$_POST['bot'];
$mts=$_POST['mt'];
$eots=$_POST['eot'];

    foreach($bots as $key=>$bot)
    {
    $insert = "INSERT INTO marks(beginning_of_term,mid_term,end_of_term) VALUES('$bot','$mts[$key]','$eots[$key]')";
    $execute_insert = mysql_query($insert);
    }
}

[Webville312]

My action page is the same page as the form!!

[Webville312]

Thanx alot Bachov Varghese and Broj1. It has finally worked!! You have been so helpful.