insert array to mysql db function

Question

I have an array where the keys represent each column in my database. Now I want a function that makes a mysql update query. Something like

    $db['money'] = $money_input + $money_db;

    $db['location'] = $location

    $query = 'UPDATE tbl_user SET '; 

            for($x = 0; $x < count($db); $x++ ){

            $query .= $db something ".=." $db something

            }

    $query .= "WHERE username=".$username." ";

Felix Kling · Accepted Answer · 2011-01-01 17:57:24Z

3

You want foreach:

$values = array();
foreach($db as $column => $value){
    $values[] = $column . "='" . mysql_real_escape_string($value) . "'";
}
$query .= implode(',', $values);

But this will set all values as strings (not sure if MySQL makes automatic conversion), you might want to test with is_numeric first:

if(!is_numeric($value)) {
    $value = "'" . mysql_real_escape_string($value) . "'";
}
$values[] = $column . "=" . $value;

Update:

Whenever you set string values in a query, you have to put them into quotation marks. Hence you have to write:

$query .= "WHERE username='".$username."'";

(note the single quotation marks).

Using sprintf can make the whole thing much clearer.

edited Jan 1, 2011 at 17:57

answered Jan 1, 2011 at 17:50

Felix Kling

820k181 gold badges1.1k silver badges1.2k bronze badges

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

3 Comments

Your Common Sense Over a year ago

@DrColossos nope, it doesn't.

Felix Kling Over a year ago

@Col. Shrapnel: I know I'm gonna regret this but just for the fun: Thank you for that productive comment. I'm looking forward to reading your answer to this question.

Your Common Sense Over a year ago

Lol :) I seldom write answers and this question is definitely not one requiring my contribution :)

ifaour · Accepted Answer · 2011-01-01 17:51:31Z

0

something like this:

function mysql_update_array($table, $data, $id_field, $id_value) {
    foreach ($data as $field=>$value) {
        $fields[] = sprintf("`%s` = '%s'", $field, mysql_real_escape_string($value));
    }
    $field_list = join(',', $fields);

    $query = sprintf("UPDATE `%s` SET %s WHERE `%s` = %s", $table, $field_list, $id_field, intval($id_value));

    return $query;
}

Please note that the code is NOT mine!

answered Jan 1, 2011 at 17:51

ifaour

38.1k12 gold badges74 silver badges79 bronze badges

2 Comments

ifaour Over a year ago

@2astalavista so you just down-voted the solution because you think the code is ugly?! :-)

user669677 Over a year ago

by the way, why are my comments disappeared? :O

John Parker · Accepted Answer · 2011-01-01 17:56:11Z

0

You could use array_keys to extract the names of the array elements and then simply use something like...

$query .= $keys[$x] . '="' . mysql_real_escape_string($db[$keys[$x]]) .'" ';

...within the for loop.

(I've presumed the presence of a...

$keys = array_keys($db);

... after you've defined the $db values in the above.)

That said, I'm really not sure I'd recommend such a "one size fits all" approach.

edited Jan 1, 2011 at 17:56

answered Jan 1, 2011 at 17:50

John Parker

54.5k11 gold badges133 silver badges131 bronze badges

Comments

user669677 · Accepted Answer · 2012-05-25 10:19:32Z

0

foreach($array as $attr => $val)
    $items[] = "$attr='$val'";    
mysql_query("INSERT INTO table SET ".implode(',', $items));

answered May 25, 2012 at 10:19

user669677

Collectives™ on Stack Overflow

insert array to mysql db function

4 Answers 4

3 Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related