0

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").............?

Isaac Bennetch
  • 11,830
  • 2
  • 32
  • 43
bs karthik reddy
  • 55
  • 4
  • What is `array_values` doing in your SQL code? – Paul Spiegel May 28 '20 at 11:40
  • Ya, my mistake. I removed it but still getting same error. – bs karthik reddy May 28 '20 at 12:46
  • 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](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). BTW: With laravels querybuilder it would be `DB::table('testing')->insert($entryarray);` – Paul Spiegel May 28 '20 at 12:51

1 Answers1

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.

DE_
  • 113
  • 7
  • 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 – bs karthik reddy May 28 '20 at 12:49
  • The query looks ok to me, does time allow null values? Are the colum names right? – DE_ May 28 '20 at 15:46
  • I set the time to be a timestamp. So it will take it automatically. – bs karthik reddy May 29 '20 at 05:22
  • 1
    I figured it. It has to be, INSERT INTO testing (Place,Location,Time,Info) So I changed your code $keys = implode(",", array_keys($entryarray)); – bs karthik reddy May 29 '20 at 05:29
  • Good catch, I've updated my answer. – DE_ May 29 '20 at 10:35
  • If this data is from user input, then this solution is insecure. – mickmackusa May 29 '20 at 10:43
  • This solution will break if a user submits a value containing a apostrophe. These types of answers are quick to write and lead unknowing researchers to write bad/insecure/unstable code. Only use a preprared statement with bound parameters. – mickmackusa May 29 '20 at 11:06