Scubadiver Scubadiver - 7 months ago 21
SQL Question

Losing data in foreach loop

Struggling with this project - I have a multidimensional associative array that is being parsed to pull out tablenames, fieldnames and data for insertion into a mysql db. In the array2query function, if the key:value is a top level pair, then the $tablename is set to 'project', otherwise $tablename=array name (discarding array[0] array names). Data is NOT coming users, it's coming from API call in a json file. Implementation will use PDO for writing to mysql db.

I'm getting a undeclared variable notice on $sql. If I declare it inside the function, it gets reset to empty during the foreach loop and I lose part of the $sql string that has been built (all 'project' tablename data).

I also need to discard data where $element is null or empty but not 0 or 'false' which I can't seem to find a way to do cleanly.

How do I properly handle the NOTICE without losing the existing string?

<?php

error_reporting(E_ALL);

//read the json file contents
$data = file_get_contents('c:\wamp64\www\json\test.json');
$data1 = preg_replace('/("\w+"):(-?\d+(\.\d+)?)/', '\\1:"\\2"', $data);
$array1 = json_decode($data1, true);

function array2query(array $array1, $tableName = ''){
$sql=""; // not declaring variable results in NOTICE; declaring results in $sql being reset to "" during foreach loop
foreach ($array1 as $key => $element) {
if (is_array($element)) {
// if key is integer then skip - it is the first level of array, keep the $tablename
$key = is_int($key) ? $tableName : $key;
array2query($element, $key);
} else {
if ($tableName === '') {
$tableName = 'project';
}
$sql .= 'INSERT INTO ' . $tableName . ' (' . rtrim($key, ',') . ') VALUES (' . rtrim($element, ',') . ');';
// }
}
}
echo $sql; //dump to screen; missing non-nested values from tablename 'project'
return $sql;
}

$array_query=array2query($array1);


Thank you for looking at this. Test data is at http://pastebin.com/iSvqCMxb

EDIT:
Screen dump from echo:

INSERT INTO Contacts (ContactType) VALUES (Sales);
INSERT INTO Contacts (ContactAddressId) VALUES (ec4539c0-9012-4b4f-a590-bee11cc91109);
INSERT INTO Contacts (ExportId) VALUES ();
INSERT INTO Vendors (VendorId) VALUES (c3abfde4-4390-4e09-aa5f-60284613c8c5);
INSERT INTO Vendors (Name) VALUES (Fabrication Specialists);
INSERT INTO Vendors (ShortName) VALUES (Fabspec);


This is missing all "INSERT INTO (project)... " statements.

Answer

If you want to create your string by calling a function recursively, you must return the value through a parameter using a reference instead of the return statement.

Change your function signature:

function array2query(array $array1, &$sql, $tableName = '')

Then call the function with a variable that will collect your SQL statements:

$sql = '';
$array_query=array2query($array1, $sql);

Remember to change the recursive call too:

if (is_array($element)) {
    $key = is_int($key) ? $tableName : $key;
    array2query($element, $sql, $key);
}

Hope this helps, I cannot really test it now.