Jump to content

Barand

Moderators
 View Profile  See their activity

  • Posts

    24,612

  • Joined

  • Last visited

  • Days Won

    834

 Content Type 

Everything posted by Barand

  1. 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)?
  2. Barand
    I haven't a clue. Which foreach() is it complaining about?
  3. 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')}')" ; }
  4. 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>
  5. 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,
  6. 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 | +----------+--------+----------------------+
  7. 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.
  8. 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>
  9. 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 ^
  10. 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
  11. 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?
  12. 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
  13. Barand
    I disagree, it ignores the entry_id. That is the field which you need to group the other values.
  14. Barand
    Looks like your approach to process name just like you do with school, belts, events is correct.
  15. Barand
    You probably have an error that stops the page executing. Turn on error reporting in your php.ini file.
  16. Barand
    Sounds like a job for the dynamic duo, Ajax and Javascript.
  17. Barand
    Sounds like there are no matching schools records - perhaps if you had CONCAT(s.city,s.school) instead of CONCAT(s.school,s.school) it may find matches. or ...LEFT JOIN a_schools USING (city, school)
  18. Barand
    you don't specify a specific ticket id in the query
  19. Barand
    To round $x up to the nearest $y mm $x = ceil($x / $y) * $y;
  20. Barand
    Use a prepared statement with placeholders and bound parameters
  21. Barand
    Typing it is a good way. Put it after the FROM section and before the ORDER BY.
  22. Barand
    pdoConnect() is my custom function which does what you have already done in the lines above it. Remove that line and replace $db in my code with $conn (as that is the connection you are creating) .
  23. Barand
    As it's a query() and not a prepare() the code is the same. Just create a mysqli connection instead.
  24. Barand
    try <?php require 'db_inc.php'; $db = pdoConnect('test'); $res = $db->query("SELECT oracleid , name , des , clockingindate as clockin , clockingoutdate as clockout , timediff(clockingoutdate, clockingindate) as duration , total FROM attendance_record JOIN ( SELECT oracleid , sec_to_time(sum(timestampdiff(SECOND, clockingindate, clockingoutdate))) as total FROM attendance_record GROUP BY oracleid ) tots USING (oracleid) ORDER BY oracleid, clockingindate "); ?> <html> <body> <table border='1' style='border-collapse:collapse; width: 700px;'> <tr><th>OracleID</th><th>Name</th><th>Designation</th><th>Clock In</th><th>Clock Out</th><th>Duration</th></tr> <?php $previd = 0; foreach ($res as $row) { if ($row['oracleid'] != $previd) { // id changed so output total if ($previd != 0) { echo "<tr><td colspan='4'</td><td>Total:</td><td>$total</td></tr>"; } $previd = $row['oracleid']; } echo "<tr><td>{$row['oracleid']}</td> <td>{$row['name']}</td> <td>{$row['des']}</td> <td>{$row['clockin']}</td> <td>{$row['clockout']}</td> <td>{$row['duration']}</td> </tr> "; $total = $row['total']; } echo "<tr><td colspan='4'</td><td>Total:</td><td>$total</td></tr>"; // dont forget total for last id ?> </table> </body> </html>
  25. Barand
    mysql> SELECT login_time, logout_time FROM login; +---------------------+---------------------+ | login_time | logout_time | +---------------------+---------------------+ | 2020-03-09 09:15:00 | 2020-03-09 15:00:00 | | 2020-03-10 09:30:00 | 2020-03-10 16:00:00 | | 2020-03-11 09:00:00 | 2020-03-11 13:30:00 | +---------------------+---------------------+ 3 rows in set (0.00 sec) mysql> SELECT sec_to_time(SUM(timestampdiff(SECOND, login_time, logout_time))) as total -> FROM login; +----------+ | total | +----------+ | 16:45:00 | +----------+
×
×
  • 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.