PHP Insert into table not creating new line of data

Question

I'm new to PHP and I'm trying to insert data from a nested array into a database. I'm noticing though that only the last key:value gets put in (seems almost as if everything else before gets overwritten). How do I get a new line of each data so everything gets put in?

 $tmpArray = array(array("one" => abc, "two" => def), array("one" => ghi, "two" => jkl));

 //  Scan through outer loop
 foreach ($tmpArray as $innerArray) {
    //  Scan through inner loop
    foreach ($innerArray as $key => $value) {
        if ($key === 'one') {
             $sql = "INSERT INTO test2 (alpha, beta) VALUES ('$key', '$value')"; 
        }
    }
 }

For simplicty, all I'm trying to do is to get "one" and "abc" put into alpha and beta. Then have another row of the table input "one" and "ghi". But when I run the code, all I get is "one" and "ghi". When I put an echo statement though, all the correct stuff gets printed. Just don't understand why they aren't getting input into my tables.

What am I doing wrong? Thanks!

Where are you executing the SQL statement? If you're doing it outside of the loop it will only insert the last value because $sql does get overwritten each time through the loop. — Jay Blanchard
– Jay Blanchard, Commented Feb 27, 2017 at 18:52

AmericanUmlaut · Accepted Answer · 2017-02-27 19:01:04Z

2

$sql = "INSERT INTO test2 (alpha, beta) VALUES ('$key', '$value')";

This line overwrites the contents of the variable $sql every time it is called. You need to concatenate your strings together instead:

$sql = '';
//  Scan through outer loop
foreach ($tmpArray as $innerArray) {
    //  Scan through inner loop
    foreach ($innerArray as $key => $value) {
        if ($key === 'one') {
            $sql .= "INSERT INTO test2 (alpha, beta) VALUES ('$key', '$value'); "; 
        }
    }
 }

Still better, as suggested in a comment, would be to simply execute the query directly rather than store it in a variable. Then you don't have to concatenate the strings together, nor do you have to enable multiple statements in a single MSQLI call.

edited Feb 27, 2017 at 19:01

answered Feb 27, 2017 at 18:53

AmericanUmlaut

2,8472 gold badges19 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

aynber Over a year ago

This will only work if they're using mysqli's multi_query. It would be better to run the sql statement within the loop.

AmericanUmlaut Over a year ago

@aynber You are right, but if I answer in that way, then I fail to address the much more profound error that is the source of the asker's question.

AmericanUmlaut Over a year ago

@aynber I've edited my question to add your thought, since you're right that that's the better approach to this.

Collectives™ on Stack Overflow

PHP Insert into table not creating new line of data

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related