0

I am creating an application system. when the applicant applies at the same place/time/day, the system will reject the application.

The same date and time will be rejected but there is a problem in 'day' because the data is combined from the input checkbox. i use sql LIKE for 'day' but not working.

LIKE is only working when the same input is in the database (ex: Rabu= Rabu) working, but (ex: Rabu= Rabu, KHamis, Jumaat) not working even 'Rabu' is in the data.

this is database 'day' (input from checkbox)

$checkbox1 = $_POST['day'];
$chk="";  
foreach($checkbox1 as $chk1)  
{  
    $chk.= $chk1." ";  
}  
   
$sql = "SELECT * FROM job WHERE start <= '$end' and fetchEnd  >= '$start' and location = '$lokasi' and timeStart <= '$timeEnd' and timeEnd >= '$timeStart' 
        and day like '%$chk%'  LIMIT 1" ;
$check_query = mysqli_query($con,$sql);
$count_valid = mysqli_num_rows($check_query);

if ($count_valid > 0) {
    echo "<script>
    alert('Permohonan anda gagal kerana bertindih dengan tarikh / masa / lokasi mesyuarat lain. Sila semak Kalendar Perkhidmatan Sidang Video.'); 
    window.location.href = 'mohon.php';</script>
    ";
} else {
    $sqlInsert = "INSERT INTO job (title,start,end,department,fetchEnd,location,day,timeStart,timeEnd,kekerapan,communication,promoter,typeMeeting,status,employId) 
            VALUES ('".$title."','".$start."',DATE_ADD('".$end."', INTERVAL 1 DAY),'".$bahagian ."','".$end ."','".$lokasi ."','".$chk ."','".$timeStart ."',
            '".$timeEnd ."','".$kekerapan ."','".$komunikasi ."','".$penganjur ."','".$sulit ."','".$status ."','".$id ."')";

    $result = mysqli_query($con, $sqlInsert);
}

sorry for my bad english. I hope u guys can help me

mickmackusa
  • 43,625
  • 12
  • 83
  • 136
SYAMIL BIN YUSRI
  • 1
  • 1
  • 2
    Remember to post code and reproduction data as *text*. – user2864740 Nov 09 '21 at 05:47
  • I don't think "like" is your issue here. And your code didn't show how you'd use the query result. Perhaps your problem is somewhere else. Please check. – Koala Yeung Nov 09 '21 at 05:53
  • *LIKE is only working when the same input is in the database (ex: Rabu= Rabu) working, but (ex: Rabu= Rabu, KHamis, Jumaat) not working even 'Rabu' is in the data.* No, the reason is that you are using it incorrectly. And `=` is not `LIKE`. – Akina Nov 09 '21 at 06:03
  • I already check. let say in my database 'day' only have 'Rabu'. Then, the new applicant want to apply and the new 'day' data is 'Rabu, Khamis,Jumaat'. the application should have been rejected because the new data has 'Rabu' but it was not reject. – SYAMIL BIN YUSRI Nov 09 '21 at 06:04
  • 2
    Your real problem is the inappropriate data model. 'Rabu' is a day (Wednesday), 'Khamis' is a day (Thursday), 'Jumaat' is a day (Friday); 'Rabu Khamis Jumaat', howver, is not a day. Don't store a list of values in a string when you are interested in the separate parts. Use a child table instead with one row per part (wekkday in your case). I am not saying that `WHERE 'Rabu Khamis Jumaat' LIKE '%Khamis%'` doesn't work. It does. It is just not how to properly use a relational database. – Thorsten Kettner Nov 09 '21 at 06:04
  • 1
    You have `day like '%$chk%'`. If day contains 'Rabu Khamis Jumaat' and the variable expression is resolved to '%Khamis%' for instance, then it should work. (It is not *good*, because looking for '%ham%' would also work, and 'ham' is not a weekday, but this is a problem with storing the days as a list.) – Thorsten Kettner Nov 09 '21 at 06:13
  • 1
    The glaring issue that I see is that prepared statements are not implemented. This code is vulnerable to injection attacks. Not to mention [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/q/3653462/2943403) as Thorsten is saying. – mickmackusa Nov 09 '21 at 06:25

0 Answers0