Jump to content
#StayAtHome ×
ramiwahdan

SQL for specific user (oracle id)

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.

Share this post


Link to post
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();

 

Share this post


Link to post
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

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

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.

  • Like 1

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

What do think the date table is for?

But OK, have it your way. Bye.

Edited by Barand

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
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?

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

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.