Bulk insert using PDO and PHP variable containing all database values

Question

I am new to PHP and am trying to update a deprecated code from mysql to PDO.

Considering that the variable $insert contains all values to bulk insert such as:

('82817cf5-52be-4ee4-953c-d3f4ed1459b0','1','EM3X001P.1a','04.03.10.42.00.02'),
('82817cf5-52be-4ee4-953c-d3f4ed1459b0','2','EM3X001P.2a','04.03.10.33.00.02'),

...etc 13k lines to insert

here is the deprecated code:

mysql_connect('localhost', 'root', '') or die(mysql_error()); 
mysql_select_db("IPXTools") or die(mysql_error());

if ($insert != '')
{
  $insert = "INSERT INTO IPXTools.MSSWireList (ID,Record,VlookupNode,HostWireLocation) VALUES ".$insert;
  $insert .= "ON DUPLICATE KEY UPDATE Record=VALUES(Record),VlookupNode=VALUES(VlookupNode),HostWireLocation=VALUES(HostWireLocation)";

  mysql_query($insert) or die(mysql_error());
  $insert = '';
}

here is the new code:

try 
{
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);    
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);    //set the PDO error mode to exception

    // prepare sql and bind parameters
    $stmt = $conn->prepare("INSERT INTO IPXTools.MSSWireList (ID, Record, VlookupNode, HostWireLocation) 
    VALUES (:ID, :Record, :VlookupNode, :HostWireLocation)");
    $stmt->bindParam(':ID', $ID);
    $stmt->bindParam(':Record', $Record);
    $stmt->bindParam(':VlookupNode', $VlookupNode);
    $stmt->bindParam(':HostWireLocation', $HostWireLocation);    

    // insert a row
    // loop through all values inside the $insert variable??????? how?
    $stmt->execute();
}
catch(PDOException $e)
{
     echo "Error: " . $e->getMessage();
}
$conn = null;

During my research I found an excellent post: PDO Prepared Inserts multiple rows in single query

One method says I would have to change my $insert variable to include all the field names. And other method says I dont have to do that. I am looking at Chris M. suggestion:

The Accepted Answer by Herbert Balagtas works well when the $data array is small. With larger $data arrays the array_merge function becomes prohibitively slow. My test file to create the $data array has 28 cols and is about 80,000 lines. The final script took 41s to complete

but I didnt understand what he is doing and I am trying to adapt my code to his. The PHP sintax is new to me so I am strugling with handling the arrays, etc...

I guess the starting point would be the variable $insert which contains all the database values I need. Do I need to modify my $insert variable to include the field names? Or I could just use its content and extract the values (how?) and include the values in a loop statement? (that would probably execute my 13k rows one at at time)

Thank you

Your code looks fine, and you haven't actually posted any question. As far as the vague concerns raised in the linked question, that guy had issues because he did a dumb thing that: 1. Is unrelated to PDO. 2. You are not doing and don't need to do. — Sammitch, Jan 13 '16 at 17:53
@JayBlanchard Yeah but that's the deprecated code he's trying to update to instead use PDO. — dokgu, Jan 13 '16 at 17:57

score 1 · Accepted Answer · answered Jan 13 '16 at 20:16

1

If you have 13k records to insert, it is good for performance to do not use prepared SQL statement. Just generate SQL query in format like this:

INSERT INTO IPXTools.MSSWireList 
(ID, Record, VlookupNode, HostWireLocation) 
VALUES 
('id1', 'r1', 'node1', 'location1'),
('id2', 'r2', 'node2', 'location2'),
...
('id13000', 'r13000', 'node13000', 'location13000');

What you may do for it - use maner of your legacy code. Your try block will looks loke this:

try 
{
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);    
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $stmt = $conn->exec($insert);
}

answered Jan 13 '16 at 20:16

Nick

9,735
7
59
89

thanks man. That is exactly what I needed. Just to execute the command I had before in PDO. I thought I had to do all the binding. The only problem is that I am getting the error below which I am researching now: Warning: PDO::exec(): MySQL server has gone away in C:\xampp\htdocs\angular\IPXTool\version001\php\IPXTool_Admin_MSS_Ingest.php on line 162 Warning: PDO::exec(): Error reading result set's header in C:\xampp\htdocs\angular\IPXTool\version001\php\IPXTool_Admin_MSS_Ingest.php on line 162 Error: SQLSTATE[HY000]: General error: 2006 MySQL server has gone awayStatus::Ingested WireList. – Max Jan 13 '16 at 21:25
1

just changed the max_allowed_packet inside my.ini in MySQL and restarted the service. Thanks you! – Max Jan 13 '16 at 22:35
@mnv: I see that a separate prepared statement and execute for each data row is bad for performance, but what about using one prepared statement for all the data rows, as suggested here: http://stackoverflow.com/a/15070222/2737565 After all, prepared statements have the huge benefit of preventing SQL injection... – Alexander Jank Apr 02 '17 at 09:01
@Alexander Jank you may try that way for 100k+ records and compare execution time with my approach. Difference will be 10+ times, because linking data in PDO is heavy process. – Nick Apr 02 '17 at 12:36

Bulk insert using PDO and PHP variable containing all database values

1 Answers1