Array mysql insert using array_implode with different datatype

Question

Hi i been trying to inserting array's into MySql database

The problem i am having is that i have different datatypes and sometime data can be a 0 value, having () curly brackets, percentage value with % sign. I would like to know a way use some already built php function that can deal with this issues.

So here is what i have done so far:

$t = array('country_code' => $data->country_code,   
           'Name' => $data->Name,
           'money' => $data->money,
           'chanceToDie' => $data->death,
           'age' => $cb->age)

/*  FORMAT EXAMPLE
    country_code = Africa (AF)
    name = jack
    chanceToDie = 5.5
    age = 62
*/

$columns = implode(", ",array_keys($t));

//Tried
$values = implode(", ",array_values($t));           //Dont work
$values = "'".implode("', '",array_values($t))."'";     //Dont work

$sql = "INSERT INTO table ($columns) VALUES ($values)";

Can you edit your question to include expected result for your sample? — Oluwafemi Sule
– Oluwafemi Sule, Commented Oct 27, 2017 at 8:59
Learn to use prepared statements and bind variables, this helps with preserving datatypes etc. — Nigel Ren
– Nigel Ren, Commented Oct 27, 2017 at 8:59

jeroen · Accepted Answer · 2017-10-27 08:59:31Z

2

You need to quote each individual value and use array_values() instead of array_keys():

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

However, this leaves you with an sql injection problem so you should really use a prepared statement.

In PDO you could use something like (assuming you control the keys and they are safe to use):

$values = ':' . implode(', :', array_keys($t));
// generates: ... VALUES(:country_code, :Name, :money,  // etc

Now you can prepare and execute your query using the array to bind the values to the placeholders. See for example http://php.net/manual/en/pdo.prepared-statements.php (the 6th example).

answered Oct 27, 2017 at 8:59

jeroen

91.8k22 gold badges117 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Photonic Over a year ago

I had errors else where in the database and my code was work btw, but i will accept your answer anyway for the effort, Thanks you.

Oleg Loginov · Accepted Answer · 2017-10-27 09:16:28Z

1

Try to use the advantage of PDO prepared queries - it is more safe and convinient.

Your code may look like this:

$col_names = array_keys($t);
// filter column names before inserting to sql to prevent sql injection
array_filter($col_names, function($v){return preg_relace("@\W@", "_", $v);});
// generate placeholders list: ?,?,?,?
$placeholders = implode(',', array_fill(0, count(t), "?"));
$values = array_values($t);

$q = $pdo->prepare('insert into (' . implode(",", $col_names) . ') values (' . $placeholders . ')');
$q->execute($values);

PDO will deal with data types and correctly replace every placeholder with the corresponding value.

edited Oct 27, 2017 at 9:16

answered Oct 27, 2017 at 9:10

Oleg Loginov

3471 silver badge6 bronze badges

Collectives™ on Stack Overflow

Array mysql insert using array_implode with different datatype

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related