Jump to content

Barand

Moderators
 View Profile  See their activity

  • Posts

    24,433

  • Joined

  • Last visited

  • Days Won

    807

 Content Type 

Posts posted by Barand

    PHP Mysqli update

    in PHP Coding Help

    Posted

    Then add one, for example 

    <form method='post'>
<textarea name='comment' cols='50' rows='5'><?=$comment?></textarea>
<button name='status' class='w3-button w3-green' value='Approved'>Approve</button> 
<button name='status' class='w3-button w3-red' value='Rejected'>Reject</button>
</form>

     

    how to insert two input values and multi data textarea values in one query?

    in PHP Coding Help

    Posted · Edited by Barand

    You should be normalizing your data and put the miles in a separate table 

    +---------------------+
|  invoice            |
+---------------------+
| inv_id (PK)         |
| order_no (UQ)       |-----+
| order_date          |     |
+---------------------+     |      +----------------------+
                            |      | state_miles          |
                            |      +----------------------+
                            |      | state_miles_id (PK)  |
                            +-----<| order_no (FK)        |
                                   | state                |
                                   | miles                |
                                   +----------------------+

    Are you getting any pdo errors? - I don't see you checking for any (have you set the ERRMODE attribute when connecting?)

    [edit]

    PS Why are you getting the last insert id before you have executed the insert?

    Why do you need the last insert id?

    Why is there a subsequent update immediately after the insert?

    checkbook balance scripting

    in PHP Coding Help

    Posted · Edited by Barand

    Works fine for me 

    mysql> select * from pup;
+----+-------+--------+---------------------+
| id | name  | amount | transaction_date    |
+----+-------+--------+---------------------+
|  1 | Curly |  10.00 | 2020-01-01 10:00:00 |
|  2 | Larry |  20.00 | 2020-01-15 12:30:00 |
|  3 | Mo    |  15.00 | 2020-02-01 09:00:00 |
|  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 |
+----+-------+--------+---------------------+
6 rows in set (0.00 sec)

mysql> select MAX(id) as maxid FROM pup;
+-------+
| maxid |
+-------+
|     6 |
+-------+
1 row in set (0.01 sec)

    or 

    <?php
    $res = $conn->query("SELECT MAX(id) as maxid FROM pup");
    $row = $res->fetch_assoc();
    echo $row['maxid'];               //--> 6
?>

     

    PHP Mysqli update

    in PHP Coding Help

    Posted · Edited by Barand

    Your "view details" link will be 

    <a href="details.php?id=$id">View full details</a>

    so that when details.php loads the id of the selected record will be in GET['id'].

    Your query is then 

    SELECT 
       details
     , location,
     , date
     , time
     , status
     , comment
FROM tablename 
WHERE id = ?

    Lay out the results as you want

    PHP Mysqli update

    in PHP Coding Help

    Posted

    1 hour ago, Ronel said:

    what if i want to display the data from the database with a link and when i click on the link shows the whole database on the other page?

    The code will be pretty much the same as this. The exceptions will be

    • No update processing required
    • No " WHERE status = 'Pending' "
    • Select all columns (ie date and time also)

    PHP Mysqli update

    in PHP Coding Help

    Posted · Edited by Barand

    I had something like this in mind

    image.png.d32383ef1351d84f445fbb498e1a1381.png

    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  |     |         |                |
+----------+---------------------------------------+------+-----+---------+----------------+

     

    PHP Mysqli update

    in PHP Coding Help

    Posted

    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.

    checkbook balance scripting

    in PHP Coding Help

    Posted · Edited by Barand

    16 minutes ago, mconte said:

    "explain persons;"

    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.

    How to organise ajax code

    in Javascript Help

    Posted

    I usually pass a variable called "ajax" in my ajax calls. This has two purposes

    1. 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.
    2. 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"
    )

     

    Uncaught TypeError

    in Javascript Help

    Posted

    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)

    • Thanks 1

    Pass a variable into PHP function

    in PHP Coding Help

    Posted

    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
}
?>

     

    to count the absents from the table

    in MySQL Help

    Posted · Edited by 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           |
+----------+--------------+                +-------+--------------+

     

    • Great Answer 1

    to count the absents from the table

    in MySQL Help

    Posted · Edited by 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?

    • Great Answer 1
×
×
  • 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.