-1

i cant understand the way of forming the mysqli query when variables are involved. I have been trying for days different ways modifying it but getting all the time the very same error SQLSTATE[42000]: Syntax error or access violation: 1064. Can anybode help me telling what is wrong with this line of code when the firt variable is a string, as the second as well...?

`$tablename = 'sofka';
$sql = "INSERT INTO " . $tablename . " (" . $columns[1] . ")
VALUES (" . $columnsval[1] . ")";`
dkrx81
  • 63
  • 1
  • 8
  • It should be like this: `$tablename = 'sofka'; $sql = "INSERT INTO " . $tablename . " (" . $columns[1] . ") VALUES ('" . $columnsval[1] . "')";` – Hari Lamichhane Mar 26 '17 at 09:15
  • If that doesn't help, please post value of `$columnsval`. – Hari Lamichhane Mar 26 '17 at 09:16
  • it is Figth Club and browser says : INSERT INTO sofka (Naziv kluba) VALUES ('Figth Club') ......SQLSTATE[42000]: Syntax error or access violation: 1064 .....You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'kluba) VALUES ('Figth Club')' at line 1 .... i have to add i tryed all the possible combinations of using " or ' with . – dkrx81 Mar 26 '17 at 09:23
  • The reason is the name of your column has space. So you need to wrap it using ` symbol – Hari Lamichhane Mar 26 '17 at 09:24

1 Answers1

0

for formulating/concatenating query or string in general, it's better to use sprintf.

$tablename = 'sofka';

$sql = sprintf("INSERT INTO `%s` (`%s`) VALUES ('%s');", $tablename, $columns[1],     
$columnsval[1]);

echo $sql;

In addition to that, I would make a wrapper function that takes an array of dictionary, and loop through these key and vals, and formulate the SQL.

function generateSqlInsert($tablename, $parameters){ 
   $columns = $values = [];

   foreach($parameters as $key => $val){
      $columns[] = "`" . $key . "`";
      $values[] = "'" . $val . "'";
   }

   $columnsSql = "(". implode(",", $columns) . ")";
   $valuesSql = "(". implode(",", mysqli_real_escape_string($values)) . ")";

   $sql = sprintf("INSERT INTO %s %s %s", $tablename, $columnsSql, $valuesSql);

   return $sql;
}

You would then use this function:

  $parameters = ['title' => 'fight club', 'year' => 1995];
  $sql = generateSqlInsert('movies', $parameters);
r4ccoon
  • 3,056
  • 4
  • 26
  • 32