CODEWITHSUNDEEP
phpmysqlsql
ganjan
ganjan

Reputation: 7576

insert array to mysql db function

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." ";

Upvotes: 0

Views: 527

Answers (4)

user669677
user669677

Reputation: 

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

Upvotes: 0

Felix Kling
Felix Kling

Reputation: 816462

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.

Upvotes: 3

John Parker
John Parker

Reputation: 54445

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.

Upvotes: 0

ifaour
ifaour

Reputation: 38135

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!

Upvotes: 0

Related Questions