mysql query latest entry?

craptakular · 2 Sep 2012 at 20:11

PHP:

           $query =		("SELECT * 
						FROM operators
						JOIN products
						ON operators.idoperators = products.idoperator JOIN results
						ON products.idProducts = results.idproducts
						WHERE (products.idProducts ='2'
						OR products.idProducts ='5'
						OR products.idProducts ='9'
						OR products.idProducts ='10'
						OR products.idProducts ='12'
						OR products.idProducts ='13')
						ORDER BY date  DESC
                        LIMIT 6");

The problem I have is idproduct 9 has more entries each week, which duplicates the entries, I want 1 entry per idproduct, how can I do that?

I do have an auto increment column...

craptakular · 2 Sep 2012 at 20:46

I can't post the real output but here is a sample:

idproduct----result----date
2------------400------28/06/2012
5------------600------16/08/2102
9------------300------15/08/2012
12-----------900------14/08/2012
13-----------200------13/08/2012
9------------900------12/08/2012

The problem is it outputs two idproducts 9 and there for totally misses idproduct 10 because I need it limited to 1 per idproduct, but my query doesnt do this.

craptakular · 2 Sep 2012 at 21:23

hhmmm I am newbie what does that even mean?

craptakular · 2 Sep 2012 at 21:37

Unknown column 'products.date' in 'field list'

craptakular · 2 Sep 2012 at 21:40

I have some tables, that I join together to make one big one.

For idproduct = 9 it has 3 entries a week, the rest have only 1 new entry per week.

The problem is I only want to show the latest entry for each idproduct.

I do not want to have 2 idproduct 9 rows in my query

craptakular · 2 Sep 2012 at 21:54

How can I test an output that qould show its working?

craptakular · 2 Sep 2012 at 22:00

PHP:

           $query =		("SELECT
						MAX(results.drawndate), 
						results.idProducts 
						FROM results 
						GROUP BY results.drawndate, results.idProducts");
					
					
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result) or die(mysql_error());



// Print out the contents of each row into a table
foreach($row as $element) 
{

echo $element;

}
mysql_close();

Outputs:

2012-01-01
2012-01-01
10
10

What is that?

craptakular · 2 Sep 2012 at 22:12

PHP:

2012-08-300
2012-08-30MAX(results.drawndate)
11
1idProducts

craptakular · 2 Sep 2012 at 22:37

No idea m8. I'm gonna sleep on it.