Cannot get first and last transaction?

Question

I have a table for transactions that need to display the first transaction date and the last transaction date. The column is FECHA which is spanish. I just cannot get the dates to display.

If you know of a better solution I would love to here it.

<?php
    $result2 = mysql_query("SELECT `FECHA` FROM `transaction` where id = '".$id."' GROUP BY `id` ORDER BY `FECHA` ASC LIMIT `1`");
    $trans = mysql_fetch_row($result2);
    $date = date_create($trans[0]);

    echo date_format($date, 'd/m/y');
    #output: 24/03/12


    $result1 = mysql_query("SELECT `FECHA`  FROM `transaction`  where id = '".$id."' GROUP BY `id` ORDER BY `FECHA` DESC LIMIT `1`");
    $trans = mysql_fetch_row($result1);
    $date = date_create($trans[0]);

    echo date_format($date, 'd/m/y');
    #output: 24/03/12
?>

This API was deprecated so long ago, it's only of archaeological interest — Strawberry, Nov 28 '19 at 22:55

score 1 · Answer 1 · answered Nov 28 '19 at 18:07

You can make use of min and max aggregate functions;

Change your query to be

SELECT min(FECHA) earliest_date, max(FECHA) latest_date
FROM `transaction`  
WHERE id = '".$id."'
GROUP BY id

Then when you select the first date will earliest_date and the last date will be the latest_date.

Please, however, read up about SQL injection and don't pass variables such as $id which come from external sources. You may wish to take a look at this post How can I prevent SQL injection in PHP?

score 0 · Answer 2 · edited Nov 28 '19 at 18:54

working thanks

<?php
    $result2 = mysql_query("SELECT MIN(FECHA)  FROM `transaction`  where id = '".$id."' ");
    $trans = mysql_fetch_row($result2);
    $datemin = date_create($trans[0]);

    echo date_format($datemin, 'd/m/y');
    #output: 24/03/12

    $result1 = mysql_query("SELECT MAX(FECHA)  FROM `transaction`  where id = '".$id."' ");
    $trans = mysql_fetch_row($result1);
    $datemax = date_create($trans[0]);

    echo date_format($datemax, 'd/m/y');
    #output: 24/03/12
?>

Cannot get first and last transaction?

2 Answers2