Jump to content

update data where id exist , insert if not exist id


ainbila

Recommended Posts

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')";
}

 

Link to comment
Share on other sites

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.)

Link to comment
Share on other sites

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 by Psycho
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.