SQL get latest record in PHP

[Guest]

This is my current code:

$query = sprintf("SELECT * FROM invoices WHERE CustomerID = '" . mysql_real_escape_string($customerID) . "';");	$result = mysql_query($query);	if (!$result) {		die(mysql_error());	}	while ($invoice = mysql_fetch_assoc($result)) {			$status = $invoice['Status'];

Instead of the 

SELECT *

I would like it to only retrieve the latest record (there is a date column if that is required)

 

Thanks D14RAP

[mcrius]

This is my current code:

$query = sprintf("SELECT * FROM invoices WHERE CustomerID = '" . mysql_real_escape_string($customerID) . "';");	$result = mysql_query($query);	if (!$result) {		die(mysql_error());	}	while ($invoice = mysql_fetch_assoc($result)) {			$status = $invoice['Status'];

Instead of the 

SELECT *

I would like it to only retrieve the latest record (there is a date column if that is required)

 

Thanks D14RAP

If it is a MySQL DB, you can use

SELECT * FROM Invoices ORDER BY date_col DESC LIMIT 1;

or you can order by id if the pk is AI. Bear in mind that if the latest invoice for a customer will always have the largest primary key value you should order by that. The date column will have to be indexed to achieve the same performance as with a PK in a table with a lot of rows.

 

Pretty much every DB server has a way to do it. In Oracle you would use a rownum for example. Hope this helps.

[prolemur]

If each invoice has a primary key that auo-increments you could also select max(id) from Invoices

[mcrius]

On a side note. I see that you want the status of the latest issued invoice. Bear in mind the following case:

A client has 3 invoices:
 

id    amount    issue_date    due_date          status1     1         01-09.2015    15-10-2015        PAID2     1         01-10-2015    15-11-2015        UNPAID3     1         01-11-2015    15-12-2015        UNPAID

A customer may be invoiced every 30 days, but the due period may be 45, so most of the times a customer may have more than one unpaid invoice.
If you want to get the latest unpaid invoice you need to adjust the query to this for example:

SELECT i.* FROM invoices i WHERE i.status = 'UNPAID' ORDER BY i.id ASC LIMIT 1;

or use
 

SELECT i.status ...

if you just want the status column.

The max() function can also be used as in prolemur's answer.