Asked  7 Months ago    Answers:  5   Viewed   34 times

Say, we have multiple rows to be inserted in a table:

$rows = [(1,2,3), (4,5,6), (7,8,9) ... ] //[ array of values ];

Using PDO:

$sql = "insert into `table_name` (col1, col2, col3) values (?, ?, ?)" ;

Now, how should you proceed in inserting the rows? Like this?

$stmt = $db->prepare($sql);

foreach($rows as $row){
  $stmt->execute($row);
}

or, like this?

$sql = "insert into `table_name` (col1, col2, col3) values ";
$sql .= //not sure the best way to concatenate all the values, use implode?
$db->prepare($sql)->execute();

Which way would be faster and safer? What is the best way to insert multiple rows?

 Answers

62

You have at least these two options:

$rows = [(1,2,3), (4,5,6), (7,8,9) ... ];

$sql = "insert into `table_name` (col1, col2, col3) values (?,?,?)";

$stmt = $db->prepare($sql);

foreach($rows as $row)
{
    $stmt->execute($row);
}

OR:

$rows = [(1,2,3), (4,5,6), (7,8,9) ... ];

$sql = "insert into `table_name` (col1, col2, col3) values ";

$paramArray = array();

$sqlArray = array();

foreach($rows as $row)
{
    $sqlArray[] = '(' . implode(',', array_fill(0, count($row), '?')) . ')';

    foreach($row as $element)
    {
        $paramArray[] = $element;
    }
}

// $sqlArray will look like: ["(?,?,?)", "(?,?,?)", ... ]

// Your $paramArray will basically be a flattened version of $rows.

$sql .= implode(',', $sqlArray);

$stmt = $db->prepare($sql);

$stmt->execute($paramArray);

As you can see the first version features a lot simpler code; however the second version does execute a batch insert. The batch insert should be faster, but I agree with @BillKarwin that the performance difference will not be noticed in the vast majority of implementations.

Wednesday, March 31, 2021
 
samayo
answered 7 Months ago
27

I had the same question a few months ago, and answered it for myself after some tinkering and research.

I used Node.js in combination with PHP.

Basically, it works like this. Apache/php send a page to the client on request. It contains an auth token. The browser Initializes socket.io and sends the token back to a node js server. Node then sends the token to a private API (if you will) to the Apache server requesting needed context on the user (user id for example). When node gets this back, it stores it in a "connection ID" => "user ID" table. (or routing table (fancy specific term for a hash map))

If a user sends a "chat" message to another user, it goes to Apache via ajax, does the database/processing stuff, then passes it back to Node.js on the back-end which plugs the user id into the routing table and sends the message to the users browser.

I cant post code because this may be used in production at some point but that Is how I did it with a LAMP based server.

NOTES: You will need to have a string account management system implemented for this to work, and I implemented this concept for my own account handling system so it is very application specific in my case.

The need for the token is for security. This token for example could be a sessions ID. Something that can derive the user ID on the back-end, but not on the front end. It should change periodically. This way, if it is compromised, the attacker wont be able to connect to the live session for long, or at all.

This concept can be improved. Such as a per-request token, that changes on every request that may only be used once. This is currently something I am working on with my live notification system.

Wednesday, March 31, 2021
 
commonpike
answered 7 Months ago
31

You can use NOW()

$query = "INSERT INTO `time` (`now`) VALUES (NOW())";
Saturday, May 29, 2021
 
CMOS
answered 5 Months ago
36

If you're interested in conserving resources and still using SQL for reporting, and precise # doesn't matter, you could try sampling like this (modify sample rate to suit your scale):

$sample_rate = 100;
if(mt_rand(1,$sample_rate) == 1) {
    $query = mysql_query(" UPDATE posts SET views = views + {$sample_rate} WHERE id = '{$id}' ");
    // execute query, etc
}
Tuesday, July 27, 2021
 
aWebDeveloper
answered 3 Months ago
49

Just quoting the manual:

If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications.

Saturday, August 21, 2021
 
gnasher729
answered 2 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :