Inserting an array key as a field value - 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?

MrCode · Accepted Answer · 2012-12-14 17:15:23Z

4

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

answered Dec 14, 2012 at 17:15

MrCode

64.6k10 gold badges95 silver badges114 bronze badges

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

2 Comments

Kirk Dollmont Over a year ago

Thank you much MrCode. As I am just starting on the PHP and MySQL path, I really need to look into prepared statements.

Eric Over a year ago

Indeed, props to MrCode as his answer introduces 2 best practices, including the use of PDO to standardize database usage, and prepared statements for higher performance & better security.

Eric · Accepted Answer · 2012-12-14 17:54:13Z

1

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

answered Dec 14, 2012 at 17:54

Eric

9077 silver badges13 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:21:29Z

0

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:

edited May 23, 2017 at 12:21

CommunityBot

11 silver badge

answered Dec 17, 2012 at 1:13

Kirk Dollmont

1012 silver badges11 bronze badges

Collectives™ on Stack Overflow

Inserting an array key as a field value - mysql/php

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related