update data where id exist , insert if not exist id

[ainbila]

i have error where my code should update existing data where id exist, it get updated ..but the others data is disappeared .only the data updated is remained 

$sql1 ="select*from semakan_dokumen where email='$stdEmail'";
$sqlsearch1 = mysqli_query($dbconfig,$sql1);
$resultcount1 = mysqli_num_rows($sqlsearch1);

if($resultcount1 > 0){
$query1=("UPDATE  semakan_dokumen set student_id='$noMatrik', email= '$stdEmail', surat_tawaran='$fileName', ic='$fileName1',sijil_lahir='$fileName2',sijil_spm= '$fileName3',sijil_sekolah= '$fileName4', sijil_dip= '$fileName5',sej_julai='$fileName6',bm_julai='$fileName7',muet='$fileName8', mid1='$fileName9',yuran= '$fileName10',umpa_bend1= '$fileName11',umpa_bend2='$fileName12',bpkp='$fileName13', penaja='$fileName14',kesihatan= '$fileName15', jhepa='$fileName16' where email= '$stdEmail' ");

}else{


//filezip
$query1 = "INSERT INTO semakan_dokumen (email,surat_tawaran,ic,sijil_lahir,sijil_spm,sijil_sekolah,sijil_dip,sej_julai,bm_julai,muet,mid1,yuran,umpa_bend1,umpa_bend2,bpkp,penaja,kesihatan,jhepa) VALUES ('$stdEmail','$fileName','$fileName1','$fileName2','$fileName3','$fileName4','$fileName5','$fileName6','$fileName7','$fileName8','$fileName9','$fileName10','$fileName11','$fileName12','$fileName13','$fileName14','$fileName15','$fileName16')";
}

 

[requinix]

Data does not simply disappear. If you name those columns in the UPDATE then only those columns will be updated - unless you have some sort of other magic happening in the table, like triggers.
And what's the deal with student_id? Why is it in the UPDATE but not the INSERT?

Unrelated, hopefully: did you know that MySQL has a INSERT... SET syntax?

INSERT INTO semakan_dokumen SET
  email = '$stdEmail',
  surat_tawarn = '$fileName',
  ic = '$fileName1',

That looks a lot like the UPDATE syntax, doesn't it? If you build one string in PHP with all the column=value assignments then you could use it in both queries. (Don't forget that the UPDATE query cares about student_id in addition to the other columns.)

[Psycho]

Not sure if this is the cause of your problem, but your UPDATE query is using the email address in the WHERE calus to determine which record to update AND it is attempting to overwrite the email address. I see no reason why that wouldn't work, but why would you do that?

In any event, MySQL has you covered with the  INSERT ... ON DUPLICATE KEY UPDATE Statement. Such a statement will attempt to insert a new record But, if the insert would fail because of a duplicate key (e.g. $stdEmail) then the statement will update the current record based on the conditions provided. No need for an initial query to see if the record exists - just run the ONE query.

INSERT INTO semakan_dokumen
   (email,        surat_tawaran,  ic,            sijil_lahir,   sijil_spm,     sijil_sekolah,
    sijil_dip,    sej_julai,      bm_julai,      muet,          mid1,          yuran,
    umpa_bend1,   umpa_bend2,     bpkp,          penaja,        kesihatan,     jhepa)

VALUES
   ('$stdEmail',   '$fileName',   '$fileName1',  '$fileName2',  '$fileName3',  '$fileName4',
    '$fileName5',  '$fileName6',  '$fileName7',  '$fileName8',  '$fileName9',  '$fileName10',
    '$fileName11', '$fileName12', '$fileName13', '$fileName14', '$fileName15', '$fileName16')

ON DUPLICATE KEY UPDATE  
    student_id = '$noMatrik'
    surat_tawaran = VALUES(surat_tawaran),
    ic = VALUES(ic),
    sijil_lahir = VALUES(sijil_lahir),
    sijil_spm = VALUES(sijil_spm),
    sijil_sekolah = VALUES(sijil_sekolah),
    sijil_dip = VALUES(sijil_dip),
    sej_julai = VALUES(sej_julai),
    bm_julai = VALUES(bm_julai),
    muet = VALUES(muet),
    mid1 = VALUES(mid1),
    yuran = VALUES(yuran),
    umpa_bend1 = VALUES(umpa_bend1),
    umpa_bend2 = VALUES(umpa_bend2),
    bpkp = VALUES(bpkp),
    penaja = VALUES(penaja),
    kesihatan = VALUES(kesihatan),
    jhepa = VALUES(jhepa)

 

Edited July 13, 2021 by Psycho