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

    checkbook balance scripting

    in PHP Coding Help

    Posted

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

     

    to count the absents from the table

    in MySQL Help

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

    • Great Answer 1

    checkbook balance scripting

    in PHP Coding Help

    Posted

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

     

    to count the absents from the table

    in MySQL Help

    Posted

    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.

    • Thanks 1

    Echo a message on header location return

    in PHP Coding Help

    Posted

    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>

     

    adding multiple rows into databse incrementing one field.

    in PHP Coding Help

    Posted

    Oh Joy!

    I love it when someone tells us what they want and you give up your time to tell them how, only to have them then tell us that it wasn't really what they want so the exercise was a waste of time.

    However, if you use a MyIsam table you can still do it. Just make the PRIMARY KEY (raffle_number, ticket_number) 

    CREATE TABLE `raffle` (
  `name` varchar(50) DEFAULT NULL,
  `raffle_number` int(11) NOT NULL,
  `ticket_number` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`raffle_number`,`ticket_number`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO raffle (name, raffle_number) VALUES
('Curly', 1),
('Curly', 1),
('Curly', 1),
('Larry', 2),
('Larry', 2),
('Larry', 2),
('Larry', 2),
('Larry', 2),
('Mo', 1),
('Mo', 1),
('Mo', 1),
('Mo', 2),
('Mo', 2),
('Mo', 2);

SELECT name 
     , raffle_number
     , ticket_number
FROM raffle
ORDER BY raffle_number, ticket_number;

+-------+---------------+---------------+
| name  | raffle_number | ticket_number |
+-------+---------------+---------------+
| Curly |             1 |             1 |
| Curly |             1 |             2 |
| Curly |             1 |             3 |
| Mo    |             1 |             4 |
| Mo    |             1 |             5 |
| Mo    |             1 |             6 |
| Larry |             2 |             1 |
| Larry |             2 |             2 |
| Larry |             2 |             3 |
| Larry |             2 |             4 |
| Larry |             2 |             5 |
| Mo    |             2 |             6 |
| Mo    |             2 |             7 |
| Mo    |             2 |             8 |
+-------+---------------+---------------+

     

    adding multiple rows into databse incrementing one field.

    in PHP Coding Help

    Posted

    Make ticket_number an auto_increment column 

    CREATE TABLE raffle (
  name VARCHAR(50)
  ticket_number INT NOT NULL auto_increment primary key
);

INSERT INTO raffle (name) VALUES
('Curly'),
('Curly'),
('Curly'),
('Larry'),
('Larry'),
('Larry'),
('Larry'),
('Larry'),
('Mo'),
('Mo'),
('Mo'),
('Mo'),
('Mo'),
('Mo');

SELECT name, ticket_number FROM raffle;


+-------+---------------+
| name  | ticket_number |
+-------+---------------+
| Curly |             1 |
| Curly |             2 |
| Curly |             3 |
| Larry |             4 |
| Larry |             5 |
| Larry |             6 |
| Larry |             7 |
| Larry |             8 |
| Mo    |             9 |
| Mo    |            10 |
| Mo    |            11 |
| Mo    |            12 |
| Mo    |            13 |
| Mo    |            14 |
+-------+---------------+

     

    • Like 1

    a bit of guidance

    in PHP Coding Help

    Posted

    7 hours ago, phppup said:

    Is it better to nest the watermarking function within the resizing for a more streamlined flow? Or does that create impracticalities of it's own?

    It all depends on your requirements.

    • Do you ever have a requirement for a reduced-size version that does not have a watermark?
    • Do you want to store the original full-size image with or without the watermark? Note that as soon as you manipulate the uploaded image you lose any exif data (such as rotation).
    • Do you require different sizes of images (thumbnail for product listings and medium when viewing individual product, say)?

    PHP - Get Machine name

    in PHP Coding Help

    Posted

    try 

    function getISP($str)
{
    $k = strlen($str);
    while (!ctype_digit($str[--$k]))
        ;
    $p = strpos($str, '.', $k);
    return substr($str, $p+1);
}

echo getISP('host86-197-dynamic.3-87-r.retail.telecomitalia.it');           //--> retail.telecomitalia.it

     

    pick a name from a number

    in PHP Coding Help

    Posted · Edited by Barand

    If you define your array like this, to begin the indexing at 1 instead of 0, 

    $names = array (1 => "Stan", "John", "Dean", "Sam", "Lee");

    then your indexes will align with your $num values, 1 - 5; In other words 

    $num = 5;
echo $names[$num];              //--> Lee

    That will save you doing what ginerjm is about to tell you to do :)

×
×
  • 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.