I want to add an array to my db. I have set up a function that checks if a value in the db (ex. health and money) has changed. If the value is diffrent from the original I add the new value to the $db array. Like this $db['money'] = $money_input + $money_db;.

function modify_user_info($conn, $money_input, $health_input){
(...)
if ($result = $conn->query($query)) {   
    while ($user = $result->fetch_assoc()) {
        $money_db = $user["money"];
        $health_db = $user["health"];
    }
    $result->close();

    //lag array til db med kolonnene som skal fylles ut som keys i array
    if ($user["money"] != $money_input){
        $db['money'] = $money_input + $money_db;
        //0 - 20
        if (!preg_match("/^[[0-9]{0,20}$/i", $db['money'])){
            echo "error"; 
            return false; 
        }

    }
    if ($user["health"] != $health_input){
        $db['health'] = $health_input + $health_db;
        //0 - 4
        if (!preg_match("/^[[0-9]{0,4}$/i", $db['health'])){
            echo "error"; 
            return false; 
        }   
        if (($db['health'] < 1) or ($db['health'] > 1000))
        {
            echo "error"; 
            return false;       
        }
    }

The keys in $db represent colums in my database. Now I want to make a function that takes the keys in the array $db and insert them in the db. Something like this ?

$query = "INSERT INTO `main_log` ( `id` , ";
foreach(range(0, x) as $num) {

    $query .= array_key.", ";       
}
$query = substr($query, 0, -3); 
    $query .= " VALUES ('', "; 
    foreach(range(0, x) as $num) {

        $query .= array_value.", ";     
    }
    $query = substr($query, 0, -3); 
    $query .= ")";
share|improve this question

feedback

1 Answer

up vote 1 down vote accepted

If the id field is already set to be an Auto-Increment value, you do not need to declare it in the INSERT command (it is just assumed as not being over-ridden, and will fill with the auto-incremented value).

Assuming that $db is an an associative array, where the element keys are the same as the SQL field names, and the element values are the desired values for those SQL fields.

# Sanitise the Array
$db = array_map( 'mysql_real_escape_string' , $db )
# Create the SQL Query
$query = 'INSERT INTO `main_log` '.
         '( `'.implode( '` , `' , array_keys( $db ) ).'` ) '.
         'VALUES '.
         '( "'.implode( '" , "' , $db ).'" )';

That should produce an SQL query which will perform the required work. Plus it should reduce the possibility of SQL Injection attacks...

(Note: The line breaks, etc. above are for readibility only, and can be removed.)

share|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.