Multiple insert in MySQL table from PHP table

Question

I am stuck on the following problem. I have two php files: One which displays a list of students with a combo box to mark each student as either 'Present' or 'Absent'and another that receives these postings and inserts the values in a MySql table. I am having a problem posting the values of the array attendance_status[] resulting in an error: "Array to string conversion". I know it might be something elementary that I am missing but cannot find my way out. These are my two files (I know that it is deprecated and will update accordingly):

index.php

?php 
error_reporting( E_ALL );
ini_set('display_errors', 1);


require "config.php";
$con = mysql_connect ( DBSERVER, DBUSER, DBPASS );
mysql_select_db ( DBNAME, $con );


?>


<h1 align="center"><font color="black"><b>ATTENDANCE FOR GRADE1</font></b></h1>
<table id="attendance" border="1"  cellspacing="1" cellpadding="1" >
<tr >
<th>id</th>
<th>name</th>
<th>surname</th>
<th>attendance</th>

</tr>
<?php

   $query = ("SELECT * FROM `b10_18591250_JC`.`STUDENT`");
   $result = mysql_query($query);

while( $row = mysql_fetch_array($result))

{


  echo "<form action=insertattend.php method=POST>";
  echo "<tr>";
  echo "<td>" . "<input name=stid type=number value=" .$row['ID']." </td>";
  echo "<td>" . "<input name=stname type=text value=" .$row['NAME']." </td>";
  echo "<td>" . "<input name=stsurname type=text value=" .$row['SURNAME']." </td>";

echo "<td>";
echo "<select name=attendance_status[] id=attendance_status>";
echo "<option value=1>Present</option>";
echo "<option value=0>Absent</option>";
echo "</select>";
echo "</td>";
echo "</tr>"; 

}

  echo"<input type=submit value=Submit>";
?>
</table>

</form>

Posting in this page called insertattend.php

<?php 
error_reporting( E_ALL );
ini_set('display_errors', 1);

require "config.php";
$con = mysql_connect ( DBSERVER, DBUSER, DBPASS );
mysql_select_db ( DBNAME, $con );

$stid = $_POST["stid"];
$attendance_status = $_POST["attendance_status"];


mysql_query("INSERT INTO ATTENDANCE (ID, STUDENT_ID, ATTENDANCE) VALUES
(NULL, '$stid', '$attendance_status')") or die (mysql_error());

?>

attendance_status is array you are trying to save it directly — Sanooj T, Aug 09 '16 at 09:16

score 3 · Accepted Answer · answered Aug 09 '16 at 09:20

You are posting an array of attendance_status and you should loop that.

<?php 
 while( $row = mysql_fetch_array($result))

{


  echo "<form action=insertattend.php method=POST>";
  echo "<tr>";
  echo "<td>" . "<input name=stid[] type=number value=" .$row['ID']." </td>";
  echo "<td>" . "<input name=stname type=text value=" .$row['NAME']." </td>";
  echo "<td>" . "<input name=stsurname type=text value=" .$row['SURNAME']." </td>";

echo "<td>";
echo "<select name=attendance_status[] id=attendance_status>";
echo "<option value=1>Present</option>";
echo "<option value=0>Absent</option>";
echo "</select>";
echo "</td>";
echo "</tr>"; 

}
?>

<?php 
error_reporting( E_ALL );
ini_set('display_errors', 1);

require "config.php";
$con = mysql_connect ( DBSERVER, DBUSER, DBPASS );
mysql_select_db ( DBNAME, $con );

$stid = $_POST["stid"];
$attendance_status = $_POST["attendance_status"];

for($i=0;$i<count($attendance_status);$i++){
   mysql_query("INSERT INTO ATTENDANCE (ID, STUDENT_ID, ATTENDANCE) VALUES
   (NULL, $stid[$i], $attendance_status[$i])") or die (mysql_error());
}

?>

Execute insert statement inside a loop is not a good approach it degrade performance and slow down the system. instead you can generate query in a loop and insert outside the loop see here - http://stackoverflow.com/questions/18459515/is-there-an-alternate-to-using-a-for-loop-to-insert-multiple-queries — Afshan Shujat, Aug 09 '16 at 09:45

score 0 · Answer 2 · answered Aug 09 '16 at 09:25

0

You can solve the problem by declaring a variable for each result you have. For example, $id = $row['ID'] and them inserting this value to string you have.

answered Aug 09 '16 at 09:25

Ibrahim Shaer

1
2

score 0 · Answer 3 · answered Aug 09 '16 at 09:47

You need loop to your array.

 <?php 
    error_reporting( E_ALL );
    ini_set('display_errors', 1);

    require "config.php";
    $con = mysql_connect ( DBSERVER, DBUSER, DBPASS );
    mysql_select_db ( DBNAME, $con );

    $stid = $_POST["stid"];
    $attendance_status = $_POST["attendance_status"];
    $size = sizeof($attendance_status);
    for ($i = 0 ; $i < $size ; $i++ ){
        $value = $attendance_status[$i]; 
        mysql_query("INSERT INTO ATTENDANCE (ID, STUDENT_ID, ATTENDANCE) VALUES
        (NULL, '$stid', '$value')") or die (mysql_error());
    }

    ?>

score 0 · Answer 4 · edited May 23 '17 at 10:34

Like the others said. You are trying to save an array of data in a single work step. I've slightly restructured your files and changed the stid input to array.

What insertattend.php does:

creates the sql insert query header
loops for each studentid, gets the key and value from each studentid
concats the values to the header
and trims away the last colon on the last concat

after that, you'll get a sql string like

INSERT INTO ATTENDANCE (ID, STUDENT_ID, ATTENDANCE) VALUES
    (NULL, '1', '0'),
    (NULL, '2', '1'),
    ...
    (NULL, '26', '1')

wich you can execute with mysql_query().

But be aware!

You are using unescaped input from the post and therefore vulnerable for sql injections!

Read this for further information:

SQL Injections

How can I prevent SQL injection in PHP?

<?php // index.php
    error_reporting(E_ALL);
    ini_set('display_errors', 1);

    require "config.php";
    $con = mysql_connect(DBSERVER, DBUSER, DBPASS);
    mysql_select_db(DBNAME, $con);
    $query  = ("SELECT * FROM `b10_18591250_JC`.`STUDENT`");
    $result = mysql_query($query);
?>

<h1 align="center"><font color="black"><b>ATTENDANCE FOR GRADE1</font></b></h1>
<form action=insertattend.php method=POST>
    <table id="attendance" border="1" cellspacing="1" cellpadding="1">
        <tr>
            <th>id</th>
            <th>name</th>
            <th>surname</th>
            <th>attendance</th>
        </tr>

        <?php while ($row = mysql_fetch_array($result)): ?>
            <tr>
                <td><input name=stid[] type=number value="<?= $row['ID'] ?>"</td>
                <td><input name=stname type=text value="<?= $row['NAME'] ?>"</td>
                <td><input name=stsurname type=text value="<?= $row['SURNAME'] ?>"</td>
                <td>
                    <select name=attendance_status[] id=attendance_status>
                        <option value=1>Present</option>
                        <option value=0>Absent</option>
                    </select>
                </td>
            </tr>
        <?php endwhile; ?>

    </table>
    <input type=submit value=Submit>
</form>

<?php // insertattend.php
    error_reporting(E_ALL);
    ini_set('display_errors', 1);

    require "config.php";
    $con = mysql_connect(DBSERVER, DBUSER, DBPASS);
    mysql_select_db(DBNAME, $con);

    $ids    = $_POST["stid"];
    $status = $_POST["attendance_status"];

    $sql = "INSERT INTO ATTENDANCE (ID, STUDENT_ID, ATTENDANCE) VALUES";

    foreach ($ids as $key => $value) {
        $sql .= "(NULL, '$value', '$status[$key]'),";
    }

    $sql = rtrim($sql, ',');

    mysql_query($sql) or die (mysql_error());
?>

Multiple insert in MySQL table from PHP table

4 Answers4