How to insert data into MySql using an associative array

Question

I had a problem inserting data into an associative array with its key as fields in the table and the values ​​that need to be inserted into the 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 was not able to insert the data into the database, so I am trying to echo the query string and I got something like this:

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

However, if I use a single quote in every value like this

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

I can successfully insert data into the database.

Therefore, I do not know what is wrong here. This is a problem with the quotation mark or not, because when I use a single quote, it seems to work.

So please help me find a suitable solution for this ...

Answer 1

For the query, you need to enclose each value in quotation marks. To do this, you can modify the implode operator to include quotes around the values ​​-

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

• EXAMPLE-demo

---

You should also check 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

Therefore, I do not know what is wrong here. This is a problem with the quotation mark or not, because when I use a single quote, it seems to work.

Yes, you need to use a single quote.

You can check this out:

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

Into something like this:

 $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 specify each value field, I meant the value field by the value field in the query. For example, in my example, the function ignores NOW () as a string and saves it as an SQL timestamp. Because if you consider it as a string type, the command will not work correctly.

---

In any case, the above is ugly and unsafe.

I would advise you to look for an ORM like RedBeanORM or maybe use the right version of PHP MySQL like MySQLi . Mainly to avoid SQL injection.

---

See one example of ORM:

 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 

---

See an example PHP version with an improved version of PHP:

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

Answer 3

Positional position holders:

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

Named place owners:

 $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 in the parameter array itself, I am not familiar with mysqli, but with many DB extensions you can use $data as-is.