CODEWITHSUNDEEP
phpmysql
Bala
Bala

Reputation: 3638

How to fetch the latest value by date in MySQL?

I have a table called payment which adds all the payment entries. I have field in payment table as customer_id which refers to the customer id in the customer table.

There is a field called flag where I store 1 or 0 based on their payment status. If they paid the full amount they flag will be 0 and if there is a balance the flag wil be 1

There is another field in payment table, added_on, which is a timestamp

I want to fetch out the latest added row of all the customer_id(in the payment table) who have flag 1(pending payment)

I tried fetching from the payment table like this

   $rResult = mysql_query("select * from payment  where flag='1'")

and then looped like this

    while ($aRow=mysql_fetch_object($rResult))
        {
        //fetch out the customer id like this
$customer= $aRow->customer_id;
    $pay_quer = mysql_query("select*from payment where customer_id='$customer'");

        }

I know it is long to do this..is there any shorter way to do it with one query? The results are not coming as I expected too. :(

Upvotes: 0

Views: 149

Answers (5)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

SELECT p.* 
FROM 
    payment AS p
  JOIN
    ( SELECT customer_id
           , MAX(added_on) AS max_added_on
      FROM payment
      WHERE flag='1'
      GROUP BY customer_id
    ) AS g
    ON  g.customer_id = p.customer_id
    AND g.max_added_on = p.added_on
ORDER BY p.added_on DESC

or (it's not clear what you want exactly nor how the flag is used). This will show different results, only those customers that their latest row in the payment has flag=1:

SELECT p.* 
FROM 
    payment AS p
  JOIN
    ( SELECT customer_id
           , MAX(added_on) AS max_added_on
      FROM payment
      GROUP BY customer_id
    ) AS g
    ON  g.customer_id = p.customer_id
    AND g.max_added_on = p.added_on
WHERE p.flag='1'
ORDER BY p.added_on DESC

Upvotes: 1

Mithun Satheesh
Mithun Satheesh

Reputation: 27855

use this to order by time added.

  $rResult = mysql_query("select * from payment  where flag='1' ORDER BY added_on DESC");

also

  while ($aRow=mysql_fetch_object($rResult))
    {
    //fetch out the customer id like this
     $customer= $aRow->customer_id;
     $field2 = $aRow->field2 ;
     $field3 = $aRow->field3 ;
     $field3 = $aRow->field4 ;
    }

there is no need for the second query. you have already got the values in the for loop

Upvotes: 1

Grrbrr404
Grrbrr404

Reputation: 1805

Simply order your select statement:

Select * from payment where flag = '1' and customer_id='$customer' order by YourTimeStempFieldHere

You can change the order direction by adding "desc" or "asc" behind your timestemp field.

You can also limit the result with the mysql LIMIT 0,1 operator. If you do that, you dont have to do a while loop.

Upvotes: 1

evolutionxbox
evolutionxbox

Reputation: 4122

Why not just use one select statement and loop through the results instead?

SELECT customer_id FROM payment WHERE flag='1' ORDER BY added_on DESC

Hopefully this will get all customers who have a balance in order of 'added_on'. =)

Like the answer below, you can also add an 'AND' to further cut down your results.

Upvotes: 1

Marcus
Marcus

Reputation: 12586

I don't know the name of your table and fields but you get the point: 

SELECT * 
FROM payment
JOIN customer ON payment.customer_id = customer.customer_id
WHERE payment.flag = '1'
ORDER BY payment.added_on ASC;

This will contain all information about the customer related the payment sorted by the latest first.

Upvotes: 1

Related Questions