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...

It seems to me you have answered your own question. However, you would need to escape your values properly (or use a prepared statement) and make sure your keys are valid column names and don't contain for example reserved words that need to be quoted using backticks. — jeroen, Jan 08 '15 at 15:48
You're not checking for errors; do. `if(!$result = $db->query($sql)){ die('There was an error running the query [' . $db->error . ']'); }` — Funk Forty Niner, Jan 08 '15 at 15:48
Is there anyway to insert the single quote to the values automatically after the implode(). This is an error I got 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 'content, James)' at line 1 — H.J. Frost, Jan 08 '15 at 16:00
Jay's answer below will work for you. I know, I tested it myself. — Funk Forty Niner, Jan 08 '15 at 16:08

score 5 · Accepted Answer · edited Jan 08 '15 at 16:18

5

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.";
}

edited Jan 08 '15 at 16:18

Funk Forty Niner

74,450
15
68
141

answered Jan 08 '15 at 16:02

Jay Blanchard

34,243
16
77
119

1

Thank you very much. You have just saved the day! – H.J. Frost Jan 08 '15 at 16:20
^ another smile on one's face ;-) – Funk Forty Niner Jan 08 '15 at 16:20
Injecting raw input into SQL code will lead to SQL injection (worst case) and application errors (best case). – Álvaro González Jan 08 '15 at 16:20
1

We aim to please! ;-) – Jay Blanchard Jan 08 '15 at 16:21
^ That's right. Fix the car now, take care of the gators later. I still have my 5 fingers? *Yepper* Ok, 4 and a thumb. – Funk Forty Niner Jan 08 '15 at 16:21

score 1 · Answer 2 · answered Jan 08 '15 at 16:11

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.

score 1 · Answer 3 · answered Jan 08 '15 at 16:19

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.

How to insert data into MySql using an associative array

3 Answers3