Nested Array into Mysql Insert Statement

JasonP128

Forgive me but I am a newbie trying to pick up the slack of a developer who left me in the lurch on a project and I have hit a bit of a roadblock. I have a nested array that I need to use to update a table in Mysql.

Basically the array looks like:

Array
(
[0] => Array
(
[id] => 85334
[name] => test
[firstName] => test
[middle] => t.
)

[1] => Array
    (
        [id] => 13111
        [name] => test
        [firstName] => test
        [middle] => 
    )
[2]etc...

)

I need this to be updated into a single Mysql table and I need to results in the table to basically reflect a single row for each set of the master array (i.e. [1].id, [1].name, [1],firstName, [1].middle would be one row in the table then loop back and do the same insert for each instance in the array).

Any help would be greatly appreciated.

Thanks,
Jason

laserlight

If you want to update rather than insert, you need to know which element of your array corresponds to the primary key of the table (most likely id).

Also, what database interface are you using? For example, the MySQL extension, the MySQLi extension, or the PDO extension.

JasonP128

sorry for not adding this to begin with. I'd be using the mysql or mysqli connection method to the database and the primary key will be the id.

To be honest, when I run this procedure I'll be recreating the table each time (i.e. delete all info in the table before running the insert statement to add the info from the array). Sorry for misusing "update".

To give you a basic idea of the scope of the project, I am using cURL to scrape info from a site that stores various reports for my customer base for the purposes of adding them to a print newsletter.

Thanks
Jason

laserlight

In that case you are looking for something along these lines:

$mysqli->autocommit(false);
$mysqli->query("TRUNCATE TABLE Reports");
$stmt = $mysqli->prepare("INSERT INTO Reports (id, name, firstName, middle) VALUES (?, ?, ?, ?)");
$stmt->bind_param('dsss', $report['id'], $report['name'], $report['firstName'], $report['middle']);
foreach ($reports as $report) {
    $stmt->execute();
}
$mysqli->commit();

If you are using a table type that does not support transactions then there would be no point in turning autocommit off and then using commit(), so you could remove those lines (but at the loss of atomicity).

JasonP128

got it to work. Thank you for your help 🙂