PHP - inserting multiple rows from an array gives invalid output

Question

I am trying to insert multiple rows from an array by creating an INSERT STATEMENT using the implode function. I am unable to figure out why I am getting more than 2 INSERT STATEMENT rows as there are only 2 rows.

I have also looked at this link, but still no joy insert multiple rows via a php array into mysql

Please help !

Below is my code:

while ($row = sqlsrv_fetch_array($getResults,SQLSRV_FETCH_ASSOC))
{
    foreach ($row as $v) {
        $sql[] = "(".$row['ReportName'].");";   
    }
    $test = "INSERT INTO PublishedComments (ReportName) VALUES ".implode(",",$sql);
    echo $test;
    echo "<br />...........<br />";
}

The output is the following:

INSERT INTO PublishedComments (ReportName) VALUES (Report Sales Per Order Method Type);,(Report Sales Per Order Method Type);,(Report Sales Per Order Method Type);,(Report Sales Per Order Method Type);,(Report Sales Per Order Method Type);,(Report Sales Per Order Method Type);
...........
INSERT INTO PublishedComments (ReportName) VALUES (Report Sales Per Order Method Type);,(Report Sales Per Order Method Type);,(Report Sales Per Order Method Type);,(Report Sales Per Order Method Type);,(Report Sales Per Order Method Type);,(Report Sales Per Order Method Type);,(Report Sales Per Order Method Type);,(Report Sales Per Order Method Type);,(Report Sales Per Order Method Type);,(Report Sales Per Order Method Type);,(Report Sales Per Order Method Type);,(Report Sales Per Order Method Type);
...........

The output has also a trailing comma and is repeating the values. — Toyampa, Jul 20 '18 at 13:58

score 0 · Answer 1 · answered Jul 20 '18 at 14:00

0

Two problems:

First, your values are not surrounded by '.

Second, each set ends with a semicolon ; but it should be comma ,

Third, pointed out in comments, you are looping with a while() and then again with a foreach()... surely that's not what you wanted :}

while ($row = sqlsrv_fetch_array($getResults,SQLSRV_FETCH_ASSOC))
{

    $sql[] = "('".$row['ReportName']."'),"; // Add apostrophes, change to comma
    $test = "INSERT INTO PublishedComments (ReportName) VALUES ".implode(",",$sql) . ';'; // Add semicolon
    echo $test;
    echo "<br />...........<br />";
}

answered Jul 20 '18 at 14:00

Patrick Moore

13,251
5
38
63

Thanks for your prompt replies, the output is the following, its is somewhat concatenating the values on the second INSERT into statement:
INSERT INTO PublishedComments (ReportName) VALUES ('Report Sales Per Order Method Type') ; ...........
INSERT INTO PublishedComments (ReportName) VALUES ('Report Sales Per Order Method Type') ,('Report Sales Per Order Method Type') ; ...........
– Toyampa Jul 20 '18 at 14:20
Thanks all of you, @Rakesh yes I have got it now, again many thanks. – Toyampa Jul 20 '18 at 14:37

score 0 · Accepted Answer · answered Jul 20 '18 at 14:12

0

You might want to take out the insert query out of the while loop, if you are trying to insert whole rows as a batch insert Also remove the foreach and the ";" at the end of "('".$row['ReportName']."');";

while ($row = sqlsrv_fetch_array($getResults,SQLSRV_FETCH_ASSOC))
{

    $sql[] = "('".$row['ReportName']."')"; 
}
$test = "INSERT INTO PublishedComments (ReportName) VALUES ".implode(",",$sql) . ';'; // Add semicolon
echo $test;
echo "<br />...........<br />";

answered Jul 20 '18 at 14:12

Rakesh

173
7

If I take it out of the while loop it will not create 2 insert statement so the output will be INSERT INTO PublishedComments (ReportName) VALUES ('Report Sales Per Order Method Type') ,('Report Sales Per Order Method Type') ; ........... – Toyampa Jul 20 '18 at 14:25
unless you are not doing anything with the insert queries, both those queries are inserting into the same table with similar kind of data. So you will not have to insert it two times. – Rakesh Jul 20 '18 at 14:28

PHP - inserting multiple rows from an array gives invalid output

2 Answers2