Update mysql database and skip empty fields

[vincekaribusana]

Hi, i’ve created a form to update user details such as first name, username, email etc.
Now, when i run the update query to the database, I would like to skip a field if it has not been filled in. So, if the user leaves the first name filed blank and changes the last name i would like to update only the last name and don’t change the first name value already stored into the database.
I’ve searched on google for an answer and someone suggested to use a update query with a case statement like this one:

UPDATE table
SET field1 = CASE
                WHEN input = '' THEN field1
                ELSE input
             END
   , field2 = CASE
                WHEN input2 = '' THEN field2
                ELSE input2
             END
   , field3 = CASE
                WHEN input3 = '' THEN field3
                ELSE input3
             END
WHERE ID = 123

I’me not really sure how to use it and if it is the best method. Many thanks for your help

[liontas76]

Write initially a query to get the data fields of the row.Then compare and filter the old and the new data.And finally update the new records keeping the unchangable data

[vincekaribusana]

Hi @liontas76 thanks for your help, i’ve followed your instructions and this is what i’ve created so far, but it doesn’t work I hope you could give me some help. Many thanks again

<?php
session_start();

/*

File Name: Mio condominio - ctrl_client_edit_profile.php
Description: Controlla il modulo di di aggiornamento profilo lato cliente
Version: 1.0
Author: Karibusana
Website: http://www.karibusana.org
Contact: info@karibusana.org

 */

// Includo il file di connessione al database e alle funzioni generali

include('../../config/konasi.php');
include('../../includes/functions.php');

// Assegno l'id dell'utente alla variabile $userid
$userid = $_SESSION['user_id'];

// Prendo i dati inseriti nel form di registrazione

$user_first = check_input($_POST['userfirst']);
$user_last = check_input($_POST['userlast']);
$user_email = check_input($_POST['useremail']);
$user_telephone = check_input($_POST['usertel']);
$user_fiscalcode = check_input($_POST['userfiscalcode']);
// converto la data inserita dall'utente in un formato copatibile con mysql
$user_birth = $_POST['userbirth'];
$edit_birth_date = str_replace('/', '-', $user_birth);
$new_birth_date = date('Y-m-d', strtotime($edit_birth_date));
$user_iddocument = check_input($_POST['useriddocument']);
$user_address = check_input($_POST['useraddress']);
$user_city = check_input($_POST['usercity']);
$user_postalcode = check_input($_POST['userpostcode']);


// Prima di eseguire la query di inserimento controllo che il nome utente o la email non siano già stati utilizzati

        // Query al database per vedere se l'email insirita nel modulo esiste già
        $rsEmails = mysqli_prepare($conn, "SELECT user_email FROM users WHERE user_email= ? "); 
        mysqli_stmt_bind_param( $rsEmails, "s", $user_email );
        mysqli_stmt_execute( $rsEmails );
        mysqli_stmt_store_result( $rsEmails );
        $numEmails = mysqli_stmt_num_rows( $rsEmails );


        // Risultato delle query e gestione del messaggio d'errore
     	if($numEmails > 0){

                    // Messaggio di errore se l'email è già in uso
                    $_SESSION['error_emailexist_msg'] = 'Ops, email '.$user_email.' already used!';
                    header("location: ../client_profile.php");
                    exit();

			        /* Close statement controllo email */
			        mysqli_stmt_close($$rsEmails);

        } else {

        			// Query al database per selzionare i campi utente
        $user_query = mysqli_prepare($conn, "SELECT user_first, user_last, user_email, user_telephone, user_fiscalcode, user_birth, user_iddocument, user_address, user_city, user_postcode	 FROM users WHERE user_id= ? "); 

        $user_query -> bind_param("i", $userid);
        $user_query -> execute();
        $user_query -> bind_result($userfirst, $userlast, $usermail, $userphone, $userfiscalcode, $userbirth, $useriddoc, $useraddress, $usercity, $userpostcode);
        $user_query -> fetch();
        $user_query -> close();


        	if (($user_first != $userfirst) && ($user_last != $user_last) && ($user_email != $useremail) && ($user_telephone != $userphone) && ($user_fiscalcode != $userfiscalcode) && ($new_birth_date != $userbirth) && ($user_iddocument != $useriddoc) && ($user_address != $useraddress) && ($user_city != $usercity) && ($user_postalcode != $userpostcode)) {


				// Query al database per aggiornare il profilo dell'utente
                $user_update_query = mysqli_prepare($conn, "UPDATE users SET user_first= ? , user_last= ?, user_email=?, user_telephone=?, user_fiscalcode=?, user_birt=?, user_iddocument=?, user_address=?, user_city=?, user_postcode=?  WHERE user_id= ? ");
                mysqli_stmt_bind_param($user_update_query, 'ssssssssssi', $user_first, $user_last, $user_email, $user_tlephone, $user_fiscalcode, $new_birth_date, $user_iddocumet, $user_address, $user_city, $user_postalcode, $userid);
                mysqli_stmt_execute($user_update_query);


                echo "updated successfully";


        	}else {

        		echo "problema";
        	}

        }


?>

[vincekaribusana]

Hi, I’ve sorted it. I’m posting the solution if anybody else is interested. Thanks for your help anyway

$update_values = array();
                
                if(!empty($user_first))
                    $update_values[] = "user_first='".$user_first."'"; 

                if(!empty($user_last))
                    $update_values[] = "user_last='".$user_last."'";
$update_values_imploded = implode(', ', $update_values);

                    if( !empty($update_values) )
                        {
                            $q = "UPDATE users SET $update_values_imploded WHERE user_id='$userid' ";
                            $r = mysqli_query($conn,$q);

                            if($r)
                            {
                                // Messaggio di successo se l'utente è stato modificato
                                $_SESSION['success_msg'] = 'Utente aggiornato con successo!';
                                header("location: ../client_profile.php");
                                exit();

                            }


                        }