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

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?

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));
}

As MrCode suggested, foreach is the way to go. See a procedural approach that uses the built-in mysqli_query PHP function below.

// start building query
$q = "INSERT INTO table (skill_name, value) VALUES";

// loop through array, adding values to query
foreach($array as $key => $value) {
    $q .= " ('$key', $value),";
}

// remove the last comma
$q = substr($q, 0, -1);

// run query
mysqli_query($your_db_connection, $q);

found the answer to my edit.

function fixArrayKey(&$arr)
{
    $arr=array_combine(array_map(function($str){return str_replace("_"," ",$str);},array_keys($arr)),array_values($arr));
    foreach($arr as $key=>$val)
    {
        if(is_array($val)) fixArrayKey($arr[$key]);
    }
}

props to Passerby for posting this in a related article found here: