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
    I had something like this in mind Example code: <?php /* CONNECT TO DB SERVER HERE */ // // Check if data was posted for processing // if ($_SERVER['REQUEST_METHOD']=='POST') { $stmt = $conn->prepare("UPDATE ronel SET status = ? WHERE id = ? "); $stmt->bind_param('si', $_POST['status'], $_POST['id']); $stmt->execute(); header("Location: #"); // reload the page exit; } $pendingRecs = ''; $res = $conn->query("SELECT id , details , location , status , comment FROM ronel WHERE status = 'Pending' ORDER BY date, time "); if ($row = $res->fetch_assoc()) { do { $pendingRecs .= " <form method='post'> <input type='hidden' name='id' value='{$row['id']}'> <tr> <td>{$row['id']}</td> <td>{$row['details']}</td> <td>{$row['location']}</td> <td>{$row['status']}</td> <td>{$row['comment']}</td> <td><button name='status' value='Approved' class='w3-button w3-green'>Approve</button></td> <td><button name='status' value='Rejected' class='w3-button w3-red'>Reject</button></td> </tr> </form> "; } while ($row = $res->fetch_assoc()); } else { $pendingRecs = "<tr><td colspan='7'>No Pending records</td></tr>" ; } ?> <!DOCTYPE html> <html> <head> <meta http-equiv="content-language" content="en"> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <title>Dashboard</title> <link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css"> <style type='text/css'> body { font-family: calibri, sans; font-size: 12pt; } </style> </head> <body> <header class="w3-container w3-orange w3-padding w3-center"> <h2>Dashboard</h2> </header> <div class="w3-content w3-padding"> <table class="w3-table w3-bordered"> <tr style="background-color:#E4EBC5; color:orangered;"> <th>ID</th> <th>Details</th> <th>Location</th> <th>Status</th> <th>Comment</th> <th colspan="2">Action</th> </tr> <?=$pendingRecs?> </table> </div> </body> </html> TABLE: ronel +----------+---------------------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------------------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | | auto_increment | | details | varchar(45) | YES | | | | | location | varchar(45) | YES | | | | | date | date | YES | | | | | time | time | YES | | | | | status | enum('Pending','Approved','Rejected') | NO | | Pending | | | comment | varchar(250) | YES | | | | +----------+---------------------------------------+------+-----+---------+----------------+
  2. Barand
    Change the select query to add a where clause so you only select pending records. On each output row, add Approve and Reject buttons with references to the record's id. A couple of comments on your code: Use prepared statements, don't put values directly into the query string. the "for=" attributes in your labels should contain the unique id of the associated input element.
  3. Barand
    Do you mean "describe persons"? NM - I see they do the same thing mysql> describe pup; +------------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | amount | decimal(8,2) | YES | | NULL | | | transaction_date | datetime | YES | | NULL | | +------------------+--------------+------+-----+---------+----------------+ 4 rows in set (0.01 sec) mysql> explain pup; +------------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | amount | decimal(8,2) | YES | | NULL | | | transaction_date | datetime | YES | | NULL | | +------------------+--------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) I only ever used explain with queries, not tables.
  4. Barand
    That has nothing to do with your problem of finding the MAX(id), although it does show that you are capable, sometimes, of processing the results of a query.
  5. Barand
    Which versions don't? I haven't come across one.
  6. Barand
    All those lines do is assign values to $sql string variable. Do you expect that will magically execute the SQL query and return the result?
  7. Barand
    If you show us some code we might be able to tell you.
  8. Barand
    Sorry, missed the second question It's usually going to be either "TEXT" or "JSON", depending on whether the ajax response will contain text or json encoded data,
  9. Barand
    Yep!
  10. Barand
    I usually pass a variable called "ajax" in my ajax calls. This has two purposes if my ajax handling code is in the same file as my calling code (it often is) it enables me to check for ajax calls with if isset($_GET['ajax']) and handle them separately. if there are multiple ajax calls, the value of that variable tells me what to do with it. if (isset($_GET['ajax'])) { switch ($_GET['ajax']) { case 'region' : exit(getRegionSales($conn, $_GET['region'])); break; case 'area' : exit(getAreaSales($conn, $_GET['area'])); break; } } Example scenario - a sales report showing totals by region Clicking on a region name drills down to show that region's sales broken down by area Clicking on an area drills down again to show the area's sales broken down by product $(".regionname").click(function() { var rgn = $(this).data("id"); $.get ( "ajax_handler.php", {"ajax" : "region", "region" : rgn}, function(resp) { // output region's totals figures by area }, "TEXT" ) }) $(".areaname").click(function() { var area = $(this).data("id"); $.get ( "ajax_handler.php", {"ajax" : "area", "area" : area}, function(resp) { // output area's sales totals by product }, "TEXT" )
  11. Barand
    I'd guess that satellite[j] does not exist for the current value of j. You need more satellites It's better to use foreach() when iterating through arrays instead of for() (which expects a satellite to always be present for every value of j)
  12. Barand
    To pass the value from the client click to PHP requires AJAX. for example, javascript onclick handler would $.get( "ajax-handler.php", {"id" : $(this).data("id")}, function (response) { // handle reponse } , "TEXT" ) Then in "ajax-handler.php" <?php $result = myfunction($_GET['id']); function myfunction (id) { // do something with id // return result } ?>
  13. Barand
    The method of counting the dates not present between the logout dates has a major flaw - it does not take into account those days when they should not be present (ie weekends and holidays) and just counts them as absent on those days. The method I usually use in these situations is to create a temporary table (workdays) which contains all the weekday dates for the required period. In the logout data I have also the data for several users during this period (Oct 1 to Oct 24 2019). CODE TO GENERATE workday DATES $startdate = '2019-10-01'; $enddate = '2019-10-25'; $interval = DateInterval::createFromDateString('next weekday'); $dateperiod = new DatePeriod( new DateTime($startdate), $interval, new DateTime($enddate)); $db->exec("CREATE TEMPORARY TABLE workday (day DATE not null primary key)"); foreach ($dateperiod as $d) { $data[] = "('{$d->format('Y-m-d')}')"; } $db->exec("INSERT INTO workday VALUES " . join(',', $data)); I also have a "holiday" table. In this example, Oct 14th is designated a holiday. So the data I now have is... TABLE: ajoo_log TABLE: ajoo_holiday +-------+---------------------+--------+ +------------+------------+-------------+ | recno | timeoflogout | userid | | holiday_id | hol_date | hol_name | +-------+---------------------+--------+ +------------+------------+-------------+ | 3 | 2019-09-30 13:13:15 | 3 | | 1 | 2019-10-14 | October Day | | 4 | 2019-09-30 13:13:15 | 4 | +------------+------------+-------------+ | 5 | 2019-09-30 13:13:15 | 5 | | 9 | 2019-10-01 07:47:35 | 4 | TABLE: ajoo_user TEMP TABLE: workday | 10 | 2019-10-01 07:47:35 | 5 | +--------+-------+ +------------+ | 14 | 2019-10-03 13:28:17 | 4 | | userid | name | | day | | 15 | 2019-10-03 13:28:17 | 5 | +--------+-------+ +------------+ | 18 | 2019-10-04 21:41:17 | 3 | | 3 | Curly | | 2019-10-01 | Tue | 19 | 2019-10-04 21:41:17 | 4 | | 4 | Larry | | 2019-10-02 | | 20 | 2019-10-04 21:41:17 | 5 | | 5 | Mo | | 2019-10-03 | | 23 | 2019-10-05 11:18:18 | 3 | +--------+-------+ | 2019-10-04 | | 24 | 2019-10-05 11:18:18 | 4 | | 2019-10-07 | Mon | 25 | 2019-10-05 11:18:18 | 5 | | 2019-10-08 | | 28 | 2019-10-08 16:56:55 | 3 | | 2019-10-09 | | 29 | 2019-10-10 16:56:55 | 4 | | 2019-10-10 | | 30 | 2019-10-16 16:56:55 | 5 | | 2019-10-11 | | 33 | 2019-10-18 16:37:43 | 3 | | 2019-10-14 | Mon | 34 | 2019-10-18 16:37:43 | 4 | | 2019-10-15 | | 35 | 2019-10-18 16:37:43 | 5 | | 2019-10-16 | | 38 | 2019-10-19 12:31:23 | 3 | | 2019-10-17 | | 40 | 2019-10-19 12:31:23 | 5 | | 2019-10-18 | | 43 | 2019-10-21 15:23:54 | 3 | | 2019-10-21 | Mon | 44 | 2019-10-21 15:23:54 | 4 | | 2019-10-22 | | 45 | 2019-10-21 15:23:54 | 5 | | 2019-10-23 | | 48 | 2019-10-24 15:37:35 | 3 | | 2019-10-24 | | 49 | 2019-10-24 16:37:35 | 4 | +------------+ | 50 | 2019-10-24 17:37:35 | 5 | +-------+---------------------+--------+ Now to calculate the days absent. A cross join of the user table with workday table gives me a date for each user when they were expected to attend. Left joining to the log table and the holiday tables tells me when they were absent (no matching logout and no matching holiday) SELECT u.name , SUM(ISNULL(timeoflogout) AND ISNULL(hol_date)) as total_absent FROM ajoo_user u CROSS JOIN workday w LEFT JOIN ajoo_log l ON u.userid = l.userid AND w.day = DATE(l.timeoflogout) LEFT JOIN ajoo_holiday h ON w.day = h.hol_date GROUP BY u.userid Comparison of original method with this new method ORIGINAL RESULTS NEW RESULTS +----------+--------------+ +-------+--------------+ | username | total_absent | | name | total_absent | +----------+--------------+ +-------+--------------+ | Curly | 14 | | Curly | 12 | | Larry | 16 | | Larry | 10 | | Mo | 15 | | Mo | 10 | +----------+--------------+ +-------+--------------+
  14. Barand
    If what you really want is just the content of the latest row, then SELECT MAX(timeoflogout) as timeoflogout , SUM(absents)) as tot FROM ( SELECT recno , DATEDIFF(timeoflogout, @prevlog) - 1 as absents , @prevlog := timeoflogout as timeoflogout FROM ( SELECT recno , timeoflogout FROM ajoo ORDER BY timeoflogout ASC ) as sorted JOIN (SELECT @prevlog := NULL) as initialise ) recs; +---------------------+------+ | timeoflogout | tot | +---------------------+------+ | 2019-10-24 17:37:35 | 15 | +---------------------+------+ EDIT: P.S. Just curious - what is your next query that requires this data?
  15. Barand
    Turn on your error checking.
  16. Barand
    To do it properly the transaction require a timestamp +----+-------+--------+---------------------+ | id | name | amount | transaction_date | +----+-------+--------+---------------------+ | 1 | Curly | 10.00 | 2020-01-01 10:00:00 | Jan | 2 | Larry | 20.00 | 2020-01-15 12:30:00 | | 3 | Mo | 15.00 | 2020-02-01 09:00:00 | Feb | 4 | Peter | -5.00 | 2020-02-01 10:30:00 | | 5 | Paul | 10.00 | 2020-02-02 11:30:00 | | 6 | Mary | 5.00 | 2020-02-02 12:15:00 | +----+-------+--------+---------------------+ Suppose we want to list this month's transactions only but showing the correct running balance total. Our initialise subquery needs to get the opening balance as at the end of January (ie sum of amount column where the date is prior to start of Feb). We then list the records since the start of February SELECT name , transaction_date , amount , FORMAT(@balance := @balance + amount, 2) as balance FROM pup JOIN (SELECT @balance := (SELECT SUM(amount) FROM pup WHERE transaction_date < '2020-02-01 00:00:00') ) as init WHERE transaction_date >= '2020-02-01 00:00:00'; +-------+---------------------+--------+---------+ | name | transaction_date | amount | balance | +-------+---------------------+--------+---------+ | Mo | 2020-02-01 09:00:00 | 15.00 | 45.00 | | Peter | 2020-02-01 10:30:00 | -5.00 | 40.00 | | Paul | 2020-02-02 11:30:00 | 10.00 | 50.00 | | Mary | 2020-02-02 12:15:00 | 5.00 | 55.00 | +-------+---------------------+--------+---------+
  17. Barand
    No, you haven't executed any query, and you haven't even defined a string variable correctly.
  18. Barand
    All that would be required is to get the SUM(amount) for those transactions up to that one (as @requinix has already mentioned.)
  19. Barand
    "absents" is a column alias. You can't reference an alias inside the SELECT or WHERE parts of the same query. If you want the cumulative then you'll need an extra subquery SELECT recno , timeoflogout , absents , @tot := @tot + IFNULL(absents,0) as total FROM ( SELECT recno , DATEDIFF(@prevlog, timeoflogout) - 1 as absents , @prevlog := timeoflogout as timeoflogout FROM ( SELECT recno , timeoflogout FROM ajoo ORDER BY timeoflogout DESC ) as sorted JOIN (SELECT @prevlog := NULL, @tot := 0) as initialise ) recs; +-------+---------------------+---------+-------+ | recno | timeoflogout | absents | total | +-------+---------------------+---------+-------+ | 30 | 2019-10-24 17:37:35 | NULL | 0 | | 29 | 2019-10-21 15:23:54 | 2 | 2 | | 28 | 2019-10-19 12:31:23 | 1 | 3 | | 27 | 2019-10-18 16:37:43 | 0 | 3 | | 26 | 2019-10-15 16:56:55 | 2 | 5 | | 25 | 2019-10-05 11:18:18 | 9 | 14 | | 24 | 2019-10-04 21:41:17 | 0 | 14 | | 23 | 2019-10-03 13:28:17 | 0 | 14 | | 21 | 2019-10-02 07:47:35 | 0 | 14 | | 4 | 2019-09-30 13:13:15 | 1 | 15 | +-------+---------------------+---------+-------+ "absents" is now a column in the subquery (a dynamic temporary table)
  20. Barand
    I'd advise against storing any derived data values such as the balance. Calculate it when rerquired SELECT * FROM pup; +----+-------+--------+ | id | name | amount | +----+-------+--------+ | 1 | Curly | 10.00 | | 2 | Larry | 20.00 | | 3 | Mo | 15.00 | | 4 | Peter | -5.00 | | 5 | Paul | 10.00 | | 6 | Mary | 5.00 | +----+-------+--------+ SELECT name , amount , FORMAT(@balance := @balance + amount, 2) as balance FROM pup JOIN (SELECT @balance:=0) as init; +-------+--------+---------+ | name | amount | balance | +-------+--------+---------+ | Curly | 10.00 | 10.00 | | Larry | 20.00 | 30.00 | | Mo | 15.00 | 45.00 | | Peter | -5.00 | 40.00 | | Paul | 10.00 | 50.00 | | Mary | 5.00 | 55.00 | +-------+--------+---------+
  21. Barand
    That code snippet without any context is as much use as a chocolate teapot. What is the table structure? How are attempting to get the balance?
  22. Barand
    This will calculate the "absents" value between consecutive logouts SELECT recno , DATEDIFF(@prevlog, timeoflogout) - 1 as absents , @prevlog := timeoflogout as timeoflogout FROM ( SELECT recno , timeoflogout FROM ajoo ORDER BY timeoflogout DESC ) as sorted JOIN (SELECT @prevlog:=NULL) as initialise +-------+---------+---------------------+ | recno | absents | timeoflogout | +-------+---------+---------------------+ | 30 | | 2019-10-24 17:37:35 | | 29 | 2 | 2019-10-21 15:23:54 | | 28 | 1 | 2019-10-19 12:31:23 | | 27 | 0 | 2019-10-18 16:37:43 | | 26 | 2 | 2019-10-15 16:56:55 | | 25 | 9 | 2019-10-05 11:18:18 | | 24 | 0 | 2019-10-04 21:41:17 | | 23 | 0 | 2019-10-03 13:28:17 | | 21 | 0 | 2019-10-02 07:47:35 | | 4 | 1 | 2019-09-30 13:13:15 | +-------+---------+---------------------+ You can accumulate the total of "15" as you process the query results.
  23. Barand
    I thought it was perhaps an "Illiteracy filter" service offered by the site. Would you hire someone who can't read simple instructions?
  24. Barand
    Lose the output buffering (ob_ functions). Rename "EmailBut.html" to "EmailBut.php" so that php code is executed. The first code should end like this . . . if ($mail->Send() ) { $Message = "Email sent..!"; } else{ $Message = "Error..!"; } $mail->smtpClose(); $Message = urlencode($Message); header("Location:EmailBut.php?Message=$Message"); ?> EmailBut.php <?php if(isset($_GET['Message'])){ echo $_GET['Message']; } ?> <html> <body> <form action="index.php" method="POST"> <button type="submit">Send</button> </form> </body> </html>
  25. Barand
    If you currently have that data in a string variable only, then file_put_contents()
×
×
  • 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.