Jump to content

Barand

Moderators
 View Profile  See their activity

  • Posts

    24,573

  • Joined

  • Last visited

  • Days Won

    824

 Content Type 

Everything posted by Barand

  1. 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
  2. Barand
    What do think the date table is for? But OK, have it your way. Bye.
  3. Barand
    $sdate1 and $edate1 would define the date range to put in the date table.
  4. 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.
  5. Barand
    To insert a record you need to execute the sql, not just define a string.
  6. Barand
    <!DOCTYPE html> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <meta http-equiv="Lang" content="en"> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script> <link href="https://stackpath.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet"> <title>Example</title> <script type="text/javascript"> $().ready( function() { $("#firstname").change( function() { makeUsername() }) $("#surname").change( function() { makeUsername() }) }) function makeUsername() { var a = $("#surname").val().substring(0,8).toLowerCase() var b = $("#firstname").val().substring(0,1).toLowerCase() $("#username").val(a+b) } </script> </head> <body> <form> <label for="firstname"> <i class="fa fa-user"></i> </label> <input type="text" name="firstname" placeholder="Firstname" id="firstname" required><br> <label for="surname"> <i class="fa fa-user"></i> </label> <input type="text" name="surname" placeholder="Surname" id="surname" required><br> <label for="username"> <i class="fa fa-user"></i> </label> <input type="text" name="username" placeholder="Username" id="username" required readonly><br> </body> <label for="password"> <i class="fa fa-lock"></i> </label> <input type="password" name="password" placeholder="Password" id="password" required> </form> </body> </html> Now, is there anything else I can do for you? Cut up your food into bite-size pieces maybe?
  7. Barand
    javascript, for example ... var a = $("#surname").val().substring(0,8).toLowerCase() var b = $("#firstname").val().substring(0,1).toLowerCase() $("#username").val(a+b)
  8. Barand
    readonly ?
  9. Barand
    Your saveWorkout class has no database connection
  10. Barand
    Because we are looking for missing dates. It's null because it isn't there. If it were there, they would not be absent. Read about LEFT JOINS.
  11. Barand
    You need to set a few attributes when you create your PDO connection. $conn = new PDO("mysql:host=$servername;dbname=timeclock", $username, $password); $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $conn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); $conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); Then you will be notified of any sql problems. Did you read my edit to earlier post about subtracting 1 day from the dates (to get Sun - Thu working week)?
  12. Barand
    I haven't a clue. Which foreach() is it complaining about?
  13. Barand
    You can set the start date with $start_date = new DateTime('2020-03-01'); Set the $incr value with $incr = new DateInterval('P1D'); You will now get every day in the DatePeriod so you now have two choices: Load every day (as I am) but then use a DELETE query to remove Fridays and Saturdays, or Don't add Fri and Sat when iterating through the date period values. EDIT: On further thought, my code writes Mon-Fri dates so if you always subtract 1 day when writing to the date table, you should get Sun-Thu dates. foreach ($period as $d) { $d->sub(new DateInterval('P1D')); // subtract one day before adding to table $dates[] = "('{$d->format('Y-m-d')}')" ; }
  14. Barand
    I thought you might be able manage that bit. Here's a fuller version <?php $month = 'March'; $start_date = new DateTime("first monday of $month"); $incr = DateInterval::createFromDateString('next weekday'); $period = new DatePeriod($start_date, $incr, new DateTime()); // create temporary date table $conn->exec("CREATE TEMPORARY TABLE date (date date not null primary key)"); // populate it foreach ($period as $d) { $dates[] = "('{$d->format('Y-m-d')}')" ; } $conn->exec("INSERT INTO date VALUES " . join(',', $dates)); // now get the days absent $res = $conn->query("SELECT s.oracleid , s.name , date_format(date, '%W %d/%m/%Y') as absent FROM attendance_staff s CROSS JOIN date d LEFT JOIN attendance_record a ON s.oracleid = a.oracleid AND d.date = DATE(a.clockingindate) WHERE a.oracleid IS NULL ORDER BY s.oracleid, d.date "); $tdata = ''; foreach ($res as $r) { $tdata .= "<tr><td>" . join('</td><td>', $r) . "</td></tr>\n"; } ?> <html> <head> <title>Example</title> <style type="text/css"> table { border-collapse: collapse; width: 600px; } th, td { padding: 8px; } th { background-color: black; color: white; } </style> </head> <body> <table border='1'> <tr><th>ID</th><th>Name</th><th>Absent</th></tr> <?=$tdata?> </table> </body> </html>
  15. Barand
    As it says on the tin - it's a temporary table. It lasts until the connection closes, which is whe when the script terminates,
  16. Barand
    For the record, here's how SAMPLE INPUT attendance_record staff +----------+---------------------+---------------------+ +----------+--------+-------------+ | oracleid | clockingindate | clockingoutdate | | oracleid | name | designation | +----------+---------------------+---------------------+ +----------+--------+-------------+ | 533349 | 2020-03-02 09:00:00 | 2020-03-02 14:00:00 | | 533349 | Rami | DT Teacher | | 533349 | 2020-03-03 09:00:00 | 2020-03-03 11:00:00 | | 533355 | Fatima | CS Teacher | | 533349 | 2020-03-04 09:00:00 | 2020-03-04 10:00:00 | +----------+--------+-------------+ | 533349 | 2020-03-09 09:00:00 | 2020-03-09 13:00:00 | | 533349 | 2020-03-10 09:00:00 | 2020-03-10 11:00:00 | | 533349 | 2020-03-11 09:00:00 | 2020-03-11 15:00:00 | | 533349 | 2020-03-12 09:00:00 | 2020-03-12 12:00:00 | | 533349 | 2020-03-13 09:00:00 | 2020-03-13 15:00:00 | | 533349 | 2020-03-16 11:52:31 | 2020-03-16 17:52:52 | | 533349 | 2020-03-18 07:59:58 | 2020-03-18 15:00:10 | | 533349 | 2020-03-23 09:00:00 | 2020-03-23 14:00:00 | | 533349 | 2020-03-24 09:00:00 | 2020-03-24 13:00:00 | | 533355 | 2020-03-02 09:00:00 | 2020-03-02 16:00:00 | | 533355 | 2020-03-03 09:00:00 | 2020-03-03 16:00:00 | | 533355 | 2020-03-04 09:00:00 | 2020-03-04 11:00:00 | | 533355 | 2020-03-05 09:00:00 | 2020-03-05 14:00:00 | | 533355 | 2020-03-06 09:00:00 | 2020-03-06 14:00:00 | | 533355 | 2020-03-10 09:00:00 | 2020-03-10 15:00:00 | | 533355 | 2020-03-11 09:00:00 | 2020-03-11 15:00:00 | | 533355 | 2020-03-12 09:00:00 | 2020-03-12 13:00:00 | | 533355 | 2020-03-13 09:00:00 | 2020-03-13 12:00:00 | | 533355 | 2020-03-16 00:26:08 | 2020-03-16 00:26:17 | | 533355 | 2020-03-16 02:50:27 | 2020-03-16 11:50:49 | | 533355 | 2020-03-23 09:00:00 | 2020-03-23 15:00:00 | | 533355 | 2020-03-24 09:00:00 | 2020-03-24 16:00:00 | +----------+---------------------+---------------------+ CODE $month = 'March'; $start_date = new DateTime("first monday of $month"); $incr = DateInterval::createFromDateString('next weekday'); $period = new DatePeriod($start_date, $incr, new DateTime()); // create temporary date table $conn->exec("CREATE TEMPORARY TABLE date (date date not null primary key)"); // populate it foreach ($period as $d) { $dates[] = "('{$d->format('Y-m-d')}')" ; } $conn->exec("INSERT INTO date VALUES " . join(',', $dates)); // now get the days absent $res = $conn->query("SELECT s.oracleid , s.name , date_format(date, '%W %d/%m/%Y') as absent FROM staff s CROSS JOIN date d LEFT JOIN attendance_record a ON s.oracleid = a.oracleid AND d.date = DATE(a.clockingindate) WHERE a.oracleid IS NULL ORDER BY s.oracleid, d.date "); QUERY RESULTS +----------+--------+----------------------+ | oracleid | name | absent | +----------+--------+----------------------+ | 533349 | Rami | Thursday 05/03/2020 | | 533349 | Rami | Friday 06/03/2020 | | 533349 | Rami | Tuesday 17/03/2020 | | 533349 | Rami | Thursday 19/03/2020 | | 533349 | Rami | Friday 20/03/2020 | | 533349 | Rami | Wednesday 25/03/2020 | | 533355 | Fatima | Monday 09/03/2020 | | 533355 | Fatima | Tuesday 17/03/2020 | | 533355 | Fatima | Wednesday 18/03/2020 | | 533355 | Fatima | Thursday 19/03/2020 | | 533355 | Fatima | Friday 20/03/2020 | | 533355 | Fatima | Wednesday 25/03/2020 | +----------+--------+----------------------+
  17. Barand
    1 ) Why do you need it as "Tuesday 24-3-2020" to add it to the DB. The only date format you should use in a database id yyyy-mm-dd. 2 ) Why do you need to store it at all? If you are storing the dates when they logged in you easily find the dates when they didn't.
  18. Barand
    One way is reorganise your form's field naming convention. Here is an example which will send the data in the format you want, IE Array ( [1] => Array ( [weight] => W1 [repetition] => R1 [field_id] => F1 [exercise] => E1 [planned_workout_id] => P1 ) [2] => Array ( [weight] => W2 [repetition] => R2 [field_id] => F2 [exercise] => E2 [planned_workout_id] => P2 ) [3] => Array ( [weight] => W3 [repetition] => R3 [field_id] => F3 [exercise] => E3 [planned_workout_id] => P3 ) [4] => Array ( [weight] => W4 [repetition] => R4 [field_id] => F4 [exercise] => E4 [planned_workout_id] => P4 ) [5] => Array ( [weight] => W5 [repetition] => R5 [field_id] => F5 [exercise] => E5 [planned_workout_id] => P5 ) ) Example code <?php if ($_SERVER['REQUEST_METHOD']=='POST') { // handle the AJAX request if (isset($_POST['data'])) { exit(print_r($_POST['data'], 1)); // and return the data as the response } } ?> <html> <head> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script> <script type="text/javascript"> $().ready( function() { $("#btnSend").click( function() { $.post( "", // send ajax request to self $("#form1").serialize(), function(resp) { $("#output").html(resp) }, "TEXT" ) }) }) </script> </head> <body> <form id="form1"> <?php for ($i=1; $i<=5; $i++) { echo "Weight : <input type='text' name='data[$i][weight]' value='W$i'><br> Repetition : <input type='text' name='data[$i][repetition]' value='R$i'><br> Field_id : <input type='text' name='data[$i][field_id]' value='F$i'><br> Exercise : <input type='text' name='data[$i][exercise]' value='E$i'><br> Planned workout : <input type='text' name='data[$i][planned_workout_id]' value='P$i'><hr> "; } ?> </form> <button id="btnSend">Send</button> <br> <h3>Data received from form:</h3> <textarea cols="50" rows="50" id="output"></textarea> </body> </html>
  19. Barand
    That's because there is an "entry_id" column in both tables. You need to specify which one (as I did) SELECT e.entry_id ^
  20. Barand
    Yes SELECT e.entry_id , field_id , value FROM wp_wpforms_entry_fields f JOIN wp_wpforms_entries e ON f.entry_id = e.entry_id WHERE field_id IN (5, 13, 16, 18) AND e.status='completed' ORDER BY e.entry_id
  21. Barand
    What is the structure of the table you want to write this data to? That could well have an influence on the best way to structure the data. What does the raw data coming from JQuery look like?
  22. Barand
    DATA +----------+----------+-------+ | entry_id | field_id | value | +----------+----------+-------+ | 1 | 5 | Curly | | 1 | 13 | bbb | | 1 | 16 | ccc | | 1 | 18 | ddd | | 2 | 5 | Larry | | 2 | 13 | eee | | 2 | 16 | fff | | 2 | 18 | ggg | | 2 | 43 | hhh | | 3 | 5 | Mo | | 3 | 13 | kkk | | 3 | 16 | mmm | | 3 | 18 | nnn | | 3 | 43 | ooo | | 4 | 5 | Tom | | 4 | 13 | ppp | | 4 | 16 | qqq | | 4 | 18 | rrr | | 4 | 43 | sss | +----------+----------+-------+ CODE $res = $conn->query("SELECT entry_id , field_id , value FROM wp_wpforms_entry_fields WHERE field_id IN (5, 13, 16, 18) ORDER BY entry_id "); $headings = [ 5 => 'Name' , 16 => 'Belt' , 13 => 'School', 18 => 'Events' ]; $temp_array = array_fill_keys(array_keys($headings), ''); // array for each attendee to be filled in from query results // process query results and place in array with attendee as the key $data = []; foreach ($res as $r) { if ( !isset($data[$r['entry_id']])) { $data[$r['entry_id']] = $temp_array ; } $data[$r['entry_id']][$r['field_id']] = $r['value'] ; // store answer in its array position } $theads = "<tr><th>" . join('</th><th>', $headings) . "</th></tr>\n" ; $tdata = ''; foreach ($data as $d) { $tdata .= "<tr><td>" . join('</td><td>', $d) . "</td></tr>\n"; } OUTPUT
  23. Barand
    I disagree, it ignores the entry_id. That is the field which you need to group the other values.
  24. Barand
    Looks like your approach to process name just like you do with school, belts, events is correct.
  25. Barand
    You probably have an error that stops the page executing. Turn on error reporting in your php.ini file.
×
×
  • 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.