插入数组键作为字段值 - mysql / php

Question

I have multiple named keys and each gets assigned a value between 0 and 5.

I have created an array and array filter to keep only the key that has been assigned a value > 0. Dropping any 0 value pairs from the array.

What I need is a way to insert the key name and the value assigned as separate fields in the same table.

The column names are skill_name and value.

The array after submit:

Array ( [Academics] => 2 [Animal_Ken] => 3 [Athletics] => 2 [Awareness] => 0 [Blood_Sense] => 0 [Brawl] => 0 [Craft] => 0 [Dodge] => 0 [Empathy] => 0 [Enigma] => 0 [Etiquette] => 0 [Expression] => 0 [Finance] => 0 [Firearms] => 0 [Intimidation] => 0 [Investigation] => 0 [Larceny] => 0 [Law] => 0 [Leadership] => 0 [Linguistics] => 0 [Malkavian_Time] => 0 [Medicine] => 0 [Melee] => 0 [Occult] => 0 [Performance] => 0 [Politics] => 0 [Science] => 0 [Scrounge] => 0 [Security] => 0 [Stealth] => 0 [Streetwise] => 0 [Subterfuge] => 0 [Survival] => 0 [submit_skills] => Add Skills )

The array after filter:

Array ( [Academics] => 2 [Animal_Ken] => 3 [Athletics] => 2 )

Now I need to insert into skill_name = key, value = value for each array index.

Any ideas?

EDIT

After taking the advice I was given everything seems to be working correctly-ish.

Now I'm having the problem that any key that had a space in the name is now given an underscore by default.

e.g : Animal Ken --> Animal_Ken .. the database is rejecting the insert because of that.

Any way to strip out the underscore and leave it as a space again?

Answer 1

Use a foreach and include the key.

Example with a PDO parameterised query:

$stmt = $db->prepare("INSERT INTO table(skill_name, value) VALUES(:skill_name,:value)");

foreach($myArray as $key => $value)
{
   // insert: $key = Academics, $value = 2
   $stmt->execute(array(':skill_name ' => $key, ':value' => $value));
}