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

kkelly · 2 Sep 2012 at 20:32

Could you post some sample data?

You could use "group by"?

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.

HungryHippos · 2 Sep 2012 at 21:15

You should be able to join the query to a subquery that contains the max dates and product ids.

First of all get a subquery working that returns the data you expect.

PHP:

SELECT
MAX(Products.Date),
Products.idProducts
FROM Products
GROUP BY Products.Date, Products.idProducts

craptakular · 2 Sep 2012 at 21:23

hhmmm I am newbie what does that even mean?

HungryHippos · 2 Sep 2012 at 21:30

See if you can get the above working first and then I will explain the rest. try and get a simple query like in my example that just returns the most recent Date/ProductID combos, mine should work out of the box based on what you posted.

Randomface74 · 2 Sep 2012 at 21:33

I'm struggling to understand what you want. Column names are weird, query is weird, description is weird.

Maybe easier to post sample data and desired output.

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

HungryHippos · 2 Sep 2012 at 21:40

Aha, is your date column in your Results table?

If so:

PHP:

SELECT
MAX(Results.Date),
Results.idProducts
FROM Results
GROUP BY Results.Date, Results.idProducts

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?

HungryHippos · 2 Sep 2012 at 22:09

Alright boss, try this:

PHP:

SELECT
MAX(results.drawndate), 
results.idProducts 
FROM results 
GROUP BY results.idProducts

craptakular · 2 Sep 2012 at 22:12

PHP:

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

HungryHippos · 2 Sep 2012 at 22:20

No way you can run this in a query that runs against the database directly?

In SQL I can use SQL Management Studio. Anything similar for MYSQL?

That query I linked should return the most recent result date grouped by ProductID that exists in the table results.

So i'd expect something like:

2012-08-30 - 10
2012-08-30 - 6
2012-08-29 - 5
etc

craptakular · 2 Sep 2012 at 22:37

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