Multiple insert in mysql using php

Question

I made a form which takes the attendance of around 11 people and what i need is to insert the daily attendance in a database. i want to use one single query to insert the attendance of all employees at once instead of writing 11 different queries for each of them. my table structure is ike this : attendance(date,eid,ename,attendance) I tried the following code for my bulk insert but it didnt work. any suggestions??

<?php
    $dbhost = "localhost";
    $dbuser = "root";
    $dbname = "gail";

    $conn = mysql_connect($dbhost, $dbuser,"") or die ('Error connecting to mysql');
    mysql_select_db($dbname);
    $dbh->beginTransaction();

    $stmt = $dbh->prepare("INSERT INTO attendance VALUES (curdate(),'$_POST[eid]','$_POST[ename]','$_POST[pora]')");

    foreach($valuesToInsert as $insertRow)
    {   

        // now loop through each inner array to match binded values
        foreach($insertRow as $column => value)
        {
            $stmt->bindParam(":{$column}", value);
            $stmt->execute();
        }
    }


    $dbh->commit();
?>

I also tried this:

<?php

    $dbhost = "localhost";
    $dbuser = "root";
    $dbname = "gail";

    $conn = mysql_connect($dbhost, $dbuser,"") or die ('Error connecting to mysql');
    mysql_select_db($dbname);

    $employees=array();

    while($row=mysql_fetch_assoc($result))
    {
        $employees[$row["eid"]]=array("ename"=>$row["ename"]);
        $employees[$row["eid"]][$row["dated"]]=array();
        $employees[$row["eid"]][$row["dated"]][$row["ename"]]=$row["pora"];
        $inserts = array();
        foreach($employees as $v)
        {
            $inserts[] = "(curdate(),'$_POST[eid]','$POST[ename]','$POST[pora]')";

            $query = "INSERT INTO attendance VALUES ". implode(", ", $inserts);

            echo "query = $query"; // for debugging purposes, remove this once it is working
            mysql_query($query) or die(mysql_error());
        }
    }


?>

but all this does is insert current date in the table a large number of times. please help. thnks in advance :)

i did this one more thing. it inserts multiple rows in the db exactly the no of rows u want but all blank. any ideas on how to deal with that?

html file:

Beamline ID Flow

Beamline ID
Flow

Beamline ID Flow

Beamline ID Flow

Beamline ID Flow

php:

 <?php
    $dbhost = "localhost";
    $dbuser = "root";
    $dbname = "gail";

    $conn = mysql_connect($dbhost, $dbuser,"") or die ('Error connecting to mysql');
    mysql_select_db($dbname);

    $cnt = count($_POST['bline_id']);
    $cnt2 = count($_POST['flow']);

    if ($cnt > 0 && $cnt == $cnt2) {
        $insertArr = array();
        for ($i=0; $i<$cnt; $i++) {
            $insertArr[] = "('" . mysql_real_escape_string($_POST['bline_id'][$i]) . "', '" .        mysql_real_escape_string($_POST['flow'][$i]) . "')";
        }

        $query = "INSERT INTO bltest (bline_id, flow) VALUES " . implode(", ", $insertArr);
        mysql_query($query) or trigger_error("Insert failed: " . mysql_error());
    }

    echo("<pre>\n");  
    print_r($_POST);
    echo("</pre>\n");
    mysql_close($conn); 
?> 

Answer 1

Got it finally! if anyone needs to insert multiple values from a form with only one block of cide, follow should help! cheers: :)

<?php

$dbhost = "localhost";
$dbuser = "root";
$dbname = "gail";

$conn = mysql_connect($dbhost, $dbuser,"") or die ('Error connecting to mysql');
mysql_select_db($dbname);

$cnt = count($_POST['eid']);
$cnt2 = count($_POST['ename']);
$cnt3 = count($_POST['pora']);

if ($cnt2 > 0 ) {
$insertArr = array();
for ($i=0; $i<$cnt2; $i++) {
    $insertArr[] = "(curdate(),'" . mysql_real_escape_string($_POST['eid'][$i]) . " ','" . mysql_real_escape_string($_POST['ename'][$i]) . " ','" .   mysql_real_escape_string($_POST['pora'][$i]) . "')";
      }

 $query = "INSERT INTO attendance (dated,eid,ename,pora) VALUES " . implode(", ",     $insertArr);
 mysql_query($query) or trigger_error("Insert failed: " . mysql_error());
 }

echo("<pre>\n");
print_r($_POST);
echo("</pre>\n");

mysql_close($conn);
?>

Answer 2

You are on the right track with your foreach() loop. There are two main things to change. Since we want to build a single query (rather than a query for each employee, start the query before the loop:

$query = "INSERT INTO `attendance` (`field1`, `field2`, `field3`) VALUES ";
foreach ($employees as $v)

Next, add the employee-specific fields to the query within each iteration of the loop. Notice that you need to escape your inputs to avoid MySQL inject. mysql_real_escape_string() is one way to do this, but you should consider implementing a more robust solution, perhaps via PDO parameterized queries.

foreach($employees as $v)
{
    $inserts[] = "(curdate(), '"
        . mysql_real_escape_string($_POST['eid'])
        . "', '" . mysql_real_escape_string($_POST['ename'])
        . "', '" . mysql_real_escape_string($_POST['pora']) . "')";
}

Then add the individual inserts to the query:

$query .= implode(",", $inserts);

This will produce a query in the following format:

INSERT INTO `attendance` (`field1`, `field2`, `field3`) VALUES 
    (curdate(), 'id1', 'ename1', 'pora1'),
    (curdate(), 'id2', 'ename2', 'pora2'),
    (curdate(), 'id3', 'ename3', 'pora3')

Finally, there is a bit of confusion in your code regarding whether you are pulling the data from $_POST or from a database query. If the data comes from the query, replace the references to $_POST data with the correct location of the data.

Answer 3

Recently I managed to create a checklist for everyone who starting to learn dynamical SQL queries with PHP. You need to complete every item before turning to the text one:

1. First of all, a database have to be designed to store your data.
2. Once you've done with it, you need to make your mind on what query would serve the purpose of insert.
3. then write this query by hand and make sure it works in console
4. next step would be to determine what data you need for this query
5. then you have to verify the data you have and determine if it fits for the (4)
6. if not - you need to format your existing data to make it meet requirements from (4)
7. As soon as you have it, you may start writing a PHP program that does create your query dynamically, using string concatenation, and echo the result out.
8. then you have to test this dynamically built query in the console as if in (3)
9. if it works - replace variables in the query with placeholders and proceed with running this query using mysqli prepared statements, with one single set of data. Note that when using native prepared statements, a placeholder can represent only a single data literal alone.
10. having done with it, you may finally start your research on a problem of feeding a prepared statement with multiple values.