Jump to content

SQL for specific user (oracle id)


ramiwahdan

Recommended Posts

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.

Link to comment
Share on other sites

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();

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

  • Great Answer 1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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 by Barand
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.