Conditional insert query

[doddsey_65]

I have a page which updates the database when the user submits the data. This page has multiple inserts, some of which are empty. what is the best way to prevent clearing data? for example i have this query:

 

switch($method)
    {
        case 'personal':
        $sql = "INSERT INTO ".TBL_PREFIX."users_profile
                (p_real_name, 
                p_location,
                p_gender,
                p_dob_day,
                p_dob_month,
                p_dob_year,
                p_website,
                p_occupation)
                VALUES
                (:location,
                :gender,
                :day,
                :month,
                :year,
                :website,
                :occupation)";
        $sth = $db->prepare($sql);
        $sth->execute(array(
            ':location'        => $data['location'],
            ':gender'        => $data['gender'],
            ':day'            => $data['dob_day'],
            ':month'        => $data['dob_month'],
            ':year'            => $data['dob_year'],
            ':website'        => $data['website'],
            ':occupation'    => $data['occupation'],
            )
        );
        break;
    }

 

if the database column "occupation" contains information but the input is empty then it would replace the info in the database. So what is the best way to prevent this? i know i could use if(empty($data['occupation'])) and build an sql query on loads of if statements to check each input. Is that best?

 

NB. this is example code, most inputs will contain data pulled from the database. This is only relevant for purposely empty inputs like password and secret answers.

 

[dcro2]

A MySQL insert doesn't change any rows, it just adds new rows. So I'm not sure what you mean.

[doddsey_65]

that shows how tired i am, its supposed to be an update query.

 

$db = db_pdo::$instance;
    
    switch($method)
    {
        case 'personal':
        $sql = "UPDATE ".TBL_PREFIX."users_profile
                SET
                    p_real_name = :real_name,
                    p_location  = :location,
                    p_gender    = :gender,
                    p_dob_day   = :day,
                    p_dob_month = :month,
                    p_dob_year  = :year,
                    p_website   = :website,
                    p_occupation = :occupation";
                
        $sth = $db->prepare($sql);
        
        $sth->execute(array(
            ':real_name'    => $data['real_name'],
            ':location'        => $data['location'],
            ':gender'        => $data['gender'],
            ':day'            => $data['dob_day'],
            ':month'        => $data['dob_month'],
            ':year'            => $data['dob_year'],
            ':website'        => $data['website'],
            ':occupation'    => $data['occupation'],
            )
        );
        break;
    }

 

[teynon]

You'll have to determine if the variable is empty before hand. If it is empty, you have two options:

 

If you have the data in your script somewhere, you can replace the post value with the database value.

 

The other option is to simply make your script determine what values to set IE:

 

$update=array();
if (!empty($_POST['value'])) {
   $update[]="value = '{$_POST['value']}'";
}

if (count($update) > 0) {
   $updateString=implode(",", $update);
   $sql="UPDATE table SET {$updateString} WHERE VALUE = '{$value}'";
   // Rest of your mysql code.
}