How to input an array into INSERT INTO query? [duplicate]

Question

My array:

print_r($entryarray) = Array ( [Place] => lucky [Location] => India [Time] => [Info] => beautiful )

My code to insert the array into INSERT query:

if(count($entryarray) >0 ) {
        $query = "INSERT INTO testing (";
        foreach($entryarray as $col => $val){
            $query.="$col,";
        }
        $query.=" )
        VALUES(";
        foreach($entryarray as $col => $val){
            $query.="$val,";
        }
        $query.=" )";
}

But I am getting an error.

Error: INSERT INTO testing (Place,Location,Time,Info, ) VALUES array_values(lucky,India,,beautiful, )

How can I get INSERT INTO testing ("Place", "Location", "Time", "Info").............?

You can't get the (exactly) same error. However.. you are missing the quotes. But you should you should use a prepared statement. See how-can-i-prevent-sql-injection-in-php. BTW: With laravels querybuilder it would be DB::table('testing')->insert($entryarray); — Paul Spiegel
– Paul Spiegel, Commented May 28, 2020 at 12:51

DE_ · Accepted Answer · 2020-05-29 10:45:08Z

2

You could do something like this:

$keys = implode(",", array_keys($entryarray));
$vals = implode("','", array_values($entryarray));

$query = "INSERT INTO testing ('$keys') VALUES ('$vals')";

As mickmackusa suggested, you should sanitize any user input.

edited May 29, 2020 at 10:45

answered May 28, 2020 at 10:12

DE_

1038 bronze badges

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

7 Comments

bs karthik reddy Over a year ago

Thank you. But still can't resolve it. Error: INSERT INTO testing ('Place','Location','Time','Info') VALUES ('lucky','India','','beautiful') You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''Place','Location','Time','Info') VALUES ('lucky','India','','beautiful')' at line 1

DE_ Over a year ago

The query looks ok to me, does time allow null values? Are the colum names right?

bs karthik reddy Over a year ago

I set the time to be a timestamp. So it will take it automatically.

bs karthik reddy Over a year ago

I figured it. It has to be, INSERT INTO testing (Place,Location,Time,Info) So I changed your code $keys = implode(",", array_keys($entryarray));

DE_ Over a year ago

Good catch, I've updated my answer.

|

Collectives™ on Stack Overflow

How to input an array into INSERT INTO query? [duplicate]

1 Answer 1

7 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Linked

Related