0

I have a similar issue to insert multiple rows via a php array into mysql but I want to insert array both into the columns and values in my database.

The columns are from a json_decode string and is working properly when I am inserting static values like below

$columns = implode(",",$key_string);

$results = array(NULL, 'test');
$results_in = implode(",",$results);
$sql_store = "INSERT into dabas_test (".$columns.") VALUES (NULL, 'test')";
$sql = mysqli_query($db, $sql_store) or die(mysql_error());

I want to insert around 150 values into 150 columns but want to try the code with 2 values. But when I am using the code below 

$columns = implode(",",$key_string);

$results = array(NULL, 'test');
$results_in = implode(",",$results);
$sql_store = "INSERT into dabas_test (".$columns.") VALUES (".$results_in.")";
$sql = mysqli_query($db, $sql_store) or die(mysql_error());

I then get the following error message:

Error description: 1054 Unknown column 'test' in 'field list'

As if test would be the column, cannot figure this out!

Thanks for help

Qirel
  • 21,424
  • 7
  • 36
  • 54
Ola Karlsson
  • 101
  • 1
  • 2
  • 10
  • Could you show `$key_string` variable (what columns you have in code) as well as the structure of your table you want to insert data into? It seems like those two don't match. – Giedrius Jul 18 '17 at 19:43
  • 1
    `mysql_error()` won't give you anything, as it won't mix with your `mysqli_query()`. You should use the same API. – Qirel Jul 18 '17 at 19:43
  • 1
    You're already using an API that supports **prepared statements** with bounded variable input, you should utilize parameterized queries with placeholders (prepared statements) to protect your database against [SQL-injection](http://stackoverflow.com/q/60174/)! Get started with [`mysqli::prepare()`](http://php.net/mysqli.prepare) and [`mysqli_stmt::bind_param()`](http://php.net/mysqli-stmt.bind-param). – Qirel Jul 18 '17 at 19:44
  • 150 columns? Sounds like you need to redesign your database – baao Jul 18 '17 at 19:45
  • Your string-values needs to be quoted. Try defining it as `$results = array(NULL, "'test'");` and you'll notice a difference. – Qirel Jul 18 '17 at 19:45
  • @Qirel, thanks this was the solution I was looking for. Have to read the "When to use single quotes, double quotes, and backticks in MySQL". Will also look into protection against SQL injection. – Ola Karlsson Jul 18 '17 at 19:50

0 Answers0