SMT SMT - 25 days ago 6
MySQL Question

Selecting users whos shifts are off on 2nd day, i want to check the firstday at backend which should not be off

$first_day = $_POST['day1'];
$second_day = $_POST['day2'];
$sql = "SELECT * FROM users_shift WHERE userid!='$_SESSION[userid]' AND grp='$user_group' AND date_start = '$second_day' AND shift='Dayoff' ORDER BY name ASC";


I want those users who don't have dayoff in their first day should show up. But the result i want to show is off 2nd day. Just want to check at backend that users should not have dayoff on their 1st day.

Answer

You can use a subquery where you select the id of users. Subquery is:

select userid from users_shift where userid!='$_SESSION[userid]' AND grp='$user_group' and date_start = '$first_day' AND shift!='Dayoff' 

Full query is :

$first_day = $_POST['day1'];
$second_day = $_POST['day2'];
$sql = "SELECT * FROM users_shift WHERE userid!='$_SESSION[userid]' AND grp='$user_group' AND date_start = '$second_day' AND shift='Dayoff' and userid in(select userid from users_shift where userid!='$_SESSION[userid]' AND grp='$user_group' and date_start = '$first_day' AND shift!='Dayoff') ORDER BY name ASC";

Or you can use inner join like this:

select * from users_shift s 
inner join users_shift f 
on s.userid = f.userid where 
where s.userid!='$_SESSION[userid]' AND s.grp='$user_group' And
s.date_start = '$first_day' AND s.shift!='Dayoff' And f.date_start = 
'$second_day' And f.shift = 'Dayoff';

P.S. query is not tested.