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
– Sanooj T, Commented Aug 9, 2016 at 9:16

Gulmuhammad Akbari · Accepted Answer · 2016-08-09 09:20:35Z

3

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());
}

?>

answered Aug 9, 2016 at 9:20

Gulmuhammad Akbari

2,0142 gold badges15 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Afshan Shujat Over a year ago

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 - stackoverflow.com/questions/18459515/…

Ibrahim Shaer · Accepted Answer · 2016-08-09 09:25:06Z

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 9, 2016 at 9:25

Ibrahim Shaer

12 bronze badges

Comments

user6063698 · Accepted Answer · 2016-08-09 09:47:32Z

0

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());
    }

    ?>

answered Aug 9, 2016 at 9:47

user6063698

Comments

Community · Accepted Answer · 2017-05-23 10:34:14Z

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());
?>

Collectives™ on Stack Overflow

Multiple insert in MySQL table from PHP table

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related