SQL for specific user (oracle id)

[ramiwahdan]

I have this code that will get the absent dates for all employees, how can i make it for one specific employee using oracle id?

code:

$res = $conn->query("SELECT s.oracleid
                          , s.staffname
                          , date_format(date, '%W %d/%m/%Y') as absent
                     	  FROM staff s
                          CROSS JOIN
                          date d
                          LEFT JOIN
                          attendance_records a ON s.oracleid = a.oracleid
                                              AND d.date = DATE(a.clockingindate)
                     	  WHERE a.oracleid IS NULL
                          ORDER BY s.oracleid, d.date    
                         ");

the records i am concerned about is for the attendance records table, I have oracle id in that table.

[gizmola]

Most likely you simply need to pass the oracleid into the query as a parameter.  Assuming this is PDO...

$stmt = $conn->prepare("SELECT s.oracleid
                          , s.staffname
                          , date_format(date, '%W %d/%m/%Y') as absent
                     	  FROM staff s
                          CROSS JOIN
                          date d
                          LEFT JOIN
                          attendance_records a ON s.oracleid = a.oracleid
                                              AND d.date = DATE(a.clockingindate)
                     	  WHERE a.oracleid = ?
                          ORDER BY s.oracleid, d.date    
                         ");

$stmt->execute(array($oracleid));
$result = $stmt->fetchAll();

 

[ramiwahdan]

4 minutes ago, gizmola said:

Most likely you simply need to pass the oracleid into the query as a parameter.  Assuming this is PDO...

 

$stmt = $conn->prepare("SELECT s.oracleid
                          , s.staffname
                          , date_format(date, '%W %d/%m/%Y') as absent
                     	  FROM staff s
                          CROSS JOIN
                          date d
                          LEFT JOIN
                          attendance_records a ON s.oracleid = a.oracleid
                                              AND d.date = DATE(a.clockingindate)
                     	  WHERE a.oracleid = ?
                          ORDER BY s.oracleid, d.date    
                         ");

$stmt->execute(array($oracleid));
$result = $stmt->fetchAll();

 

here is the whole code to see where is my mistake!

why i am getting error

$conn->exec("INSERT INTO date VALUES " . join(',', $dates));

// now get the days absent
$res = $conn->query("SELECT s.oracleid
                          , s.staffname
                          , date_format(date, '%W %d/%m/%Y') as absent
                     FROM staff s
                          CROSS JOIN
                          date d
                          LEFT JOIN
                          attendance_records a ON s.oracleid = a.oracleid
                                              AND d.date = DATE(a.clockingindate)
                     WHERE a.oracleid = $userid
                     ORDER BY s.oracleid, d.date    
                    ");
					
$result = $conn->fetchAll();

$tdata = '';
foreach ($res as $r) {
    $tdata .= "<tr><td>" . join('</td><td>', $r) . "</td></tr>\n";
}
?>

    <table border='1'>
        <tr><th>ID</th><th>Name</th><th>Absent</th></tr>
        <?=$tdata?>
    </table>

please advise

[gizmola]

Check the value of userid.  Is it not a varchar?

WHERE a.oracleid = '$userid'

If you write code this way, you are using variable interpolation which opens your code up to SQL Injection.  That is why I showed you the parameter passing method, which uses prepared statements and bound variables.  You would not need the single quotes if you used the parameter, as it will determine the datatype from the type of the variable being passed.

[Barand]

You would need to test for the oracleid in the staff table IE

WHERE s.oracleid = ? AND a.oracleid IS NULL

since it is doing a LEFT JOIN the attendance_records table (alias a) to find missing dates.

[ramiwahdan]

25 minutes ago, Barand said:

You would need to test for the oracleid in the staff table IE

WHERE s.oracleid = ? AND a.oracleid IS NULL

since it is doing a LEFT JOIN the attendance_records table (alias a) to find missing dates.

i need to add between dates from a form to add to where clause and I tried this but it will return nothing, the table is empty again

code:

WHERE a.oracleid IS NULL and s.oracleid = '$userid' and DATE(a.ClockingOutDate) >= $sdate1 and DATE(a.ClockingOutDate) <= $edate1

sdate1 and edate1 are variables taken from form fields in the page of type date.

[Barand]

$sdate1 and $edate1 would define the date range to put in the date table.

[ramiwahdan]

22 minutes ago, Barand said:

$sdate1 and $edate1 would define the date range to put in the date table.

no, i want to use them so i can pull out records from db only between the chosen dates.

[Barand]

What do think the date table is for?

But OK, have it your way. Bye.

Edited March 26, 2020 by Barand

[ramiwahdan]

5 minutes ago, Barand said:

What do think the date table is for?

But OK, have it your way. Bye.

i am sorry that you got upset but in my company they might search for someone for a week as example so they dont want to look the whole month.

[ramiwahdan]

6 minutes ago, ramiwahdan said:

i am sorry that you got upset but in my company they might search for someone for a week as example so they dont want to look the whole month.

i got it when you said the word date and realized i have start date. now, can i add to that code an end date?

$month = $sdate1;
$start_date = new DateTime("$month");
$incr = DateInterval::createFromDateString('next weekday');
$period = new DatePeriod($start_date, $incr, new DateTime());

can i have end date since it is taking start date?

[Barand]

Then just put that week's dates in the date table.

The CROSS JOIN of staff and date gives a row for every date for each staff member that you want to check.

staff          date                staff CROSS JOIN date
  -----         -----                ---------------------
    A              1                       A   1
    B              2                       A   2
    C              3                       A   3
                   4                       A   4
                   5                       A   5
                                           B   1
                                           B   2
                                           B   3
                                           B   4
                                           B   5
                                           C   1
                                           C   2
                                           C   3
                                           C   4
                                           C   5

It then LEFT JOINS with the attendance records to see where there is no match (IE they are absent).

                                                    staff CROSS JOIN date
staff CROSS JOIN date      attendance_record        LEFT JOIN attendance_record
---------------------      -----------------        ---------------------------
      A   1                      A   1               A   1       A   1    
      A   2                      A   3               A   2     NULL NULL  
      A   3                      A   4               A   3       A   3    
      A   4                      A   5               A   4       A   4    
      A   5                      B   1               A   5       A   5    
      B   1                      B   2               B   1       B   1    
      B   2                      B   4               B   2       B   2    
      B   3                      B   5               B   3     NULL NULL  
      B   4                      C   1               B   4       B   4    
      B   5                      C   2               B   5       B   5    
      C   1                      C   3               C   1       C   1    
      C   2                      C   5               C   2       C   2    
      C   3                                          C   3       C   3    
      C   4                                          C   4     NULL NULL  
      C   5                                          C   5       C   5    

 

Edited March 27, 2020 by Barand

[Barand]

7 minutes ago, ramiwahdan said:

can i have end date since it is taking start date?

https://www.php.net/datePeriod