How To Check If Some Value Exist In Table A, And Then Insert Data To Tabke B If The Value Does Exist-CodePudding

I'm learning php and trying to make a form with php that inserts data to MySQL, but before the data is inserted to table X there is a field that must be checked in table Y before inserting. If this value does exist in table Y, then the data is inserted to table X if not, then the data will not inserted.

what i want to do is check if the value $nip exist in table Y, if $nip exist in table Y i want to put values ('$kode_pd','$tujuan','$tgl_mulai','$lama_hari','$total_biaya_pd','$nip') to table X

Below Is The Code That I Already Tried

`$sql = "SELECT * FROM karyawan WHERE nip ='$nip'";
$q1  = mysqli_num_rows(mysqli_query($koneksi, $sql));
    if ($q1 > 0) {
    $sql1   = "INSERT INTO perjalanan (kode_pd,tujuan,tgl_mulai,lama_hari,total_biaya_pd,nip)  
    values  ('$kode_pd','$tujuan','$tgl_mulai','$lama_hari','$total_biaya_pd','$nip')";
             if ($sql1) {
                 $sukses = "success";
             } else {
                 $error  = "fail"; 
             }
         } else {
             $error = "nip not found";
         }`

CodePudding user response：

         $sql1  = "SELECT * FROM karyawan WHERE nip ='$nip'";
         $q1    = mysqli_num_rows(mysqli_query($koneksi, $sql1));
         if ($q1 > 0) {
            $sql2   = "INSERT INTO `perjalanan` (`kode_pd`, `tujuan`, `tgl_mulai`, `lama_hari`, `total_biaya_pd`, `nip`) VALUES ('$kode_pd','$tujuan','$tgl_mulai','$lama_hari','$total_biaya_pd','$nip')";
            $sql3   =  mysqli_query ($koneksi, $sql2);
            $sukses = "berhasil memasukkan data baru";
         } else {
            $error  = "nip tidak ditemukan";
         }

fix it i just forgot to execute the query, thank you @jens for pointing it out XD

CodePudding user response：

You need to execute the query which insert data to table. Selecting count of records is faster than selecting the record. Your code may be like that :

$sql = "SELECT COUNT(*) FROM karyawan WHERE nip ='$nip'";
$result = mysqli_query($koneksi, $sql);

if($result){
  $row = $result->fetch_row();
  $c = $row[0];
}
else
  $c = -1;

if ($c > 0) {
  $sql1 = "INSERT INTO perjalanan (kode_pd,tujuan,tgl_mulai,lama_hari,total_biaya_pd,nip)  
    values  ('$kode_pd','$tujuan','$tgl_mulai','$lama_hari','$total_biaya_pd','$nip')";

  $result = mysqli_query($koneksi, $sql);
  $sukses = ($result) ? "success" : "fail"; 
} 
else {
  $c = (mysqli_errno($koneksi)>0)? -1, $c;
  $error = ($c == 0) ? "nip not found" : "known error";
}

CodePudding user response：

For the query table A "SELECT * FROM tableAWHEREid = :id" The prepare statements and bind values, $res = rewCount if(res) => insert data into tableB

And if have to repeat put all of it in a loop