Looping through Array Values with $SQL output

I have a list of serialized data that I unserialize and store into an array.

$employee_data = unserialize($entry, '63');

Which results in an expected output:

Array ( [0] => Array ( [First] => Joe [Last] => Test [Birth Date] => 01/01/2011 ) [1] => Array ( [First] => Mike [Last] => Miller [Birth Date] => 01/01/1980 ) )

Ive been trying, unsuccessfully, to insert these records into a table in MySQL using foreach() or something like:

$employee_array = array();
$k = 1;
for($n=0; $n<count($employee_data); $n++ ){
    $employee_array['employee_first_name'.$k] = $employee_data[$n]['First'];
    $employee_array['employee_last_name'.$k] =  $employee_data[$n]['Last'];
    $employee_array['employee_birthdate'.$k] =  $employee_data[$n]['Birth Date'];
$SQL = "INSERT INTO employee_test (
employee_first_name,
employee_last_name,
employee_birthdate
)
VALUES (
'$employee_first_name.$k',
'$employee_last_name.$k',
'$employee_birthdate.$k'
)"
$k++;
};

Each employee in the array needs to be entered into a new row in the table, however the number of employees will vary from 1 to 10+

We've tried

foreach($employee_array as $key => $value)

with the same results.

The actual results we're hoping for is the SQL Statement to be:

insert into employee_test(
employee_first_name,
employee_last_name,
employee_birthdate)
VALUES(
'Joe',
'Test',
'01/01/2011');
insert into employee_test(
employee_first_name,
employee_last_name,
employee_birthdate)
VALUES(
'Mike',
'Miller',
'01/01/1980');

Keep in mind that your sql statement is not escaped. For example, a name with an apostrophe like "0'neil" will break your sql. I would also familiarise yourself with php's foreach: https://www.php.net/manual/en/control-structures.foreach.php.

I'm not sure exactly what you're trying to accomplish by adding the index to the name and sql value, but I would do something like this:

foreach($employee_data as $key => $value){
    // $employee_array is not necessary 
    $employee_array[$key]['employee_first_name'] = $value['First'];
    $employee_array[$key]['employee_last_name'] =  $value['Last'];
    $employee_array[$key]['employee_birthdate'] =  $value['Birth Date'];
    // Needs escaping
    $SQL = "INSERT INTO employee_test (
employee_first_name,
employee_last_name,
employee_birthdate
)
VALUES (
'{$value['First']}',
'{$value['Last']}',
'{$value['Birth Date']}'
)";
    echo $SQL;
};

Looping : PhP Array Loops through Database query to output result , Don't pull data and then massage it, construct your SQL to return the data you If it has to be data driven then sort your hello array by descending values and then code sample above, you are looping through your array as key=>value pairs. Below is a sample set of queries which generates array variable named MYARRAY, you can store your data in this array. Then we will iterate through these array values using WHILE clause, we have used two variables to loop through array – INDEXVAR and TOTALCOUNT. As usual the loop will continue until INDEXVAR is smaller than TOTALCOUNT.

The first implementation wont work as your calling a variable rather than the key in your array.

'$employee_first_name.$k',

Should be

$employee_array['employee_first_name'.$k]

You are also creating the SQL statement every iteration of the for loop, so in this implementation only the last employee will save.

Also I don't see the reasoning in doing it that way anyway you may as well just use the employee_data array and the $k variable can also be made redundant.

$SQL = ""; 
for($n=0; $n<count($employee_data); $n++ ){
    $SQL .= "INSERT INTO employee_test (
    employee_first_name,
    employee_last_name,
    employee_birthdate
    ) VALUES (";
    $SQL .= "'".$employee_data[$n]['First']."',";
    $SQL .= "'".$employee_data[$n]['Last']."',";
    $SQL .= "'".$employee_data[$n]['Birth Date']."'";
    $SQL .= ");";
};

Ive not tested but it should give you an idea.

You will also have issues with the date formatted that way, Your database would likely require the date in yyyy/mm/dd format

Finally I would not recommend inserting values like this, look at the PDO library for placeholders.

Iterate Through Array Of Data In SQL Query, Then we will iterate through these array values using WHILE clause, we have used two variables to loop through array – INDEXVAR and� Declare @Ids Table (id integer primary Key not null) Insert @Ids(id) values (4),(7),(12),(22),(19) -- (or call another table valued function to generate this table) Then loop based on the rows in this table

I think I understand what you're trying to do here. You are using the = operator, effectively setting $SQL to a new value each time your loop iterates. If you adapt your code, you will be able to append to $SQL variable each time.

//I used this array for testing. Comment this out
$employee_data = array(
        0 => array(
                "first" => "test",
                "last"  => "tester",
                "birth date" => "01/01/1970"
            ),
        1 => array(
                "first" => "bob",
                "last"  => "david",
                "birth date" => "02/02/1972"
            ),
    );

    //Start SQL as a blank string
$SQL = "";

//Do your iteration
foreach( $employee_data as $key=>$value ){
    $first     = $value['first'];
    $last      = $value['last'];
    $birthDate = $value['birth date'];

    //append this query to the $SQL variable. Note I use `.=` instead of `=`
    $SQL .= "INSERT INTO employee_test(
                 `employee_first_name`,
                 `employee_last_name`,
                 `employee_birthdate`)
             VALUES(
                 '$first',
                 '$last',
                 '$birthDate'
             );";
}

//output the query
echo $SQL;

There are much easier ways of doing this though. Look into prepared statements :)

Looping through Array Values in T-SQL - Wrox, I have a function that will spilt comma separated values and output them How can I loop through each value in ArraySplit and compare that� Looping through Array Values in T-SQL Hiya, Newbie here! I have a function that will spilt comma separated values and output them as one column of values called Item.

How to iterate through a result set by using Transact-SQL in SQL , Use Transact-SQL Statements to Iterate Through a Result Set identifier greater than the current row that is being processed in the query. Pros and Cons of Using a While Loop to Iterate Through Table Rows in SQL Server. There are also benefits to use a WHILE loop compared to a cursor. While loops are faster than cursors. While loops use less locks than cursors. Less usage of Tempdb: While loops don’t create a copy of data in tempdb as a cursor does.

SQL While loop: Understanding While loops in SQL Server, The article explains how to use the SQL While loop in Microsoft SQL Server to we use a SQL While loop to print the first five positive integer values: In the second iteration, since the @count variable will have the value 2, the a SQL Table � How to implement array-like functionality in SQL Server � SQL� Getting a SQL Server result set from a stored procedure The stored procedure has a required parameter; Iterating through the rows of data returned from SQL Server; Using column values from the SQL server result set to create – and uniquely name – text files; Note: All of these operations will be done in PowerShell. PowerShell Code

Documentation: 9.5: Control Structures, The current values of the output parameter variables will be returned. loop is much like a FOR loop, but instead of iterating through the rows returned by a SQL query, Here is an example of looping through the elements of an integer array: Infinite SQL WHILE loop . In the infinite loop AKA endless loop, the condition result will never be false, so the loop never ends and can work forever. Imagine that we have a WHILE loop, and we don’t increment the value of the variable. In this scenario, the loop runs endlessly and never ends.

Comments
  • Do you need the $employee_array or just using it for the INSERT for the SQL?
  • Agreed on the apostrophe - Oddly, that is addressed in a sanitization before the names are passed with a "str_replace". Apparently keeping the apostrophe was deemed unimportant. Thanks for the suggestion - it appears similar to an approach that was tried earlier but only resulted in one INSERT() statement.
  • Ahhh, sometimes a fresh set of eyes is exactly what is needed. I completely missed the '=' instead of appending!
  • Two huge steps in the right direction. The $SQL looks exactly as expected however it fails when run via $wpdb->query($SQL). The echo looks exactly like what I need but it seems to be throwing an incorrect syntax error 1064 error at line 12: right syntax to use near 'INSERT INTO employee_test(employee_fir' But copying the echo $SQL and running it in MYSQL inserts both records as expected.
  • @kiknit, try amending the code to use backticks in your INSERT statement around each column name, answer updated.