How to insert data into MySql using an associative array

Question

I am now having a problem inserting data in associative array with its key as fields in the table and the values to be inserted into MySql database. Here is my code.

<?php
$table = 'articles';

$data = array(
        'title' => 'Header',
        'content' => 'This is content',
        'author' => 'James');

$keys = implode(', ', array_keys($data));
$values = implode(', ', array_values($data));

$sql = 'insert into '.$table.'('.$keys.') values ('.$values.')';

$db = new mysqli('localhost', 'root', 'root', 'blog');
$db->query($sql);
?>

With this code, I wasn't able to insert the data into the database so I try to echo the query string out and I got something like this :

insert into articles(title, content, author) values (Header, This is content, James)

However, if I use the single quote in each value like this

insert into articles(title, content, author) values ('Header', 'This is content', 'James')

I can successfully insert the data into the database.

So I don't know what's wrong here. Is it a problem with the quote sign or not because when I use the single quote, this seems to work.

So please help me find the proper solution for this...

Answer 1

For the query you need to enclose each value in quotes. To do that you can change your implode statement to include the quotes to around the values -

$values = "'" .implode("','", array_values($data)) . "'";

• EXAMPLE-demo

---

You should also be checking for errors.

Replace $db->query($sql);

with

if(!$result = $db->query($sql)){ 

die('There was an error running the query [' . $db->error . ']'); 

}

else{
echo "Data inserted.";
}

Answer 2

So I don't know what's wrong here. Is it a problem with the quote sign or not because when I use the single quote, this seems to work.

Yes, you need to use the single quote.

You can check it out:

$values = implode(', ', array_values($data));

Into something like it:

$values = implode (',', array_map (
function ($z)
{
return ((is_numeric ($z) || (is_string ($z) ? ($z == "NOW()" ? true : false) : false) || (is_array ($z)?(($z=implode(";",$z))?false:false):false)) ? $z : "'" . utf8_decode ($z) . "'");
}, array_values ($data)));

The idea is that you make every value field quoted, I meant value field by value field in the query. For instance, in my example, the function ignores NOW() as string, and keep it up to work as SQL's timestamp. Because if you treat it as string type, the command wouldn't work properly.

---

Anyway, the above is ugly and insecure.

I would advice you to look for some ORM like RedBeanORM or, maybe, use the proper PHP MySQL version like MySQLi. Mainly to avoid SQL injections.

---

Look one ORM example:

require 'rb.php';
R::setup();

$post = R::dispense('post');
$post->text = 'Hello World';

$id = R::store($post);       //Create or Update
$post = R::load('post',$id); //Retrieve
R::trash($post);             //Delete

---

Look one PHP MySQL improved version example:

$stmt = mysqli_prepare($link, "INSERT INTO CountryLanguage VALUES (?, ?, ?, ?)");
mysqli_stmt_bind_param($stmt, 'sssd', $code, $language, $official, $percent);
$code = 'DEU';
$language = 'Bavarian';
$official = "F";
$percent = 11.2;
mysqli_stmt_execute($stmt);

---

Good luck. Good learning.

Answer 3

Positional place-holders:

$values = implode(', ', 
    array_fill(0, count($data), '?')
);
// insert into articles(title, content, author) values (?, ?, ?)

Named place-holders:

$values = implode(', ', array_map(
    function($value){
        return ":$value";
    },
    array_keys($data)
));
// insert into articles(title, content, author) values (:title, :content, :author)

Not necessarily the nicest or best code.

As about the parameter array itself, I'm not familiar with mysqli but with many DB extensions you could use $data as-is.