From an array to a db table using the PHP implode function

I stumbled across this neat way of updating a MySQL database table from an array.

This is part of the Google AdWords API Project I’m working on at the moment. I needed to update  a MySQL table with x amount of records returned from the AdWords API Service and really wanted to avoid looping through the array with an MySQL Insert statement on each occasion.

This is how it works. I run through the array of records and build another that will be carried over to the MySQL query. That query then uses the PHP implode function to include the array that I’ve just built as part of the insert.

The result is that we only have to run the query once, that’s one visit to the database with a bulk insert or update rather than x amount.

1
2
3
4
5
6
7
8
9
10
11
	$dbh = dbconnect();                 // open db conn
	$tblupdatedetailsql = array();      // define $tblupdatedetailsql array
	foreach ($adGroups as $adGroup) {   // populate new sql array using content array
	    $tblupdatedetailsql[] = '('.$tblupdateid.', 0, "'.mysql_real_escape_string($adGroup->name).'")';
	}
        // insert in the db using the implode function and the array
	$insert = "INSERT INTO tbl (id,type,desc) VALUES".implode(',', $tblupdatedetailsql); 	$statement = $dbh->prepare($insert); // prepare
	$statement->execute();               // execute
	unset($tblupdatedetailsql);          // unset $tblupdatedetailsql.
	$dbh = null;                         // close db conn