Insert fields into table dynamically but error with Query

Question

public function add($table, $data, $exclude = array()){

$fields = $values = array();
if( !is_array($exclude) ) $exclude = array($exclude);
foreach( array_keys($data) as $key ) {

    if( !in_array($key, $exclude) ) {
        $fields[] = "'$key'";
        $values[] = "'" . $this->db->real_escape_string($data[$key]) . "'";

    }
}

$fields = implode(",", $fields);
$values = implode(",", $values);

$query = "INSERT INTO $table($fields) VALUES ($values)";

 if(!$result = $this->db->query($query)) {
      echo "Prepare failed: (" . $this->db->errno . ") " . $this->db->error;
    }

}

Error

Prepare failed: (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 ''name','address','country','type','status') VALUES ('Starbucks','Washington ','U' at line 1

 $food->add("food", $_POST, "add");

Tested printing fields and values and they were correct. So called the function add and then got the error on syntax, but couldn't figure where query went wrong.

Post value of `print_r($fields)` and `print_r($values)` and quotes in column and table name create problem!! — Saty, Jun 06 '16 at 08:22
I think your number of column fields do not match the number of corresponding values. From your error. ( 'name','address','country','type','status') VALUES ('Starbucks','Washington ','U') — Vibhesh Kaul, Jun 06 '16 at 08:28

score 2 · Accepted Answer · answered Jun 06 '16 at 08:22

2

Key should be wrapped in ` instead of '

$fields[] = "`$key`";

answered Jun 06 '16 at 08:22

Jamie Bicknell

2,306
17
35

oh what does ` do? – joe Jun 06 '16 at 08:26
@joe read http://stackoverflow.com/questions/261455/using-backticks-around-field-names – Saty Jun 06 '16 at 08:27

Insert fields into table dynamically but error with Query

1 Answers1