Page 1 of 1

Sql problem...

Posted: Thu Aug 22, 2013 2:47 am
by chris98
Hello, I am creating a downloads section for my site.I have all the information in one table, and on the actual downloads home, I want to display the total number of downloads for that category, when it was last updates and what the name of the last download was.I can do it when I have them all in seperate tavles, but once I join them all in one table, with only the category column to seperate them, I don't know how to do it.This is what I have so far

I have managed to get the total number of downloads for that category by using this:

Code: Select all

SELECT COUNT(*) AS id FROM downloads WHERE category='Kingmaker';

But now, I want to get the last added download, where the category is kingmaker.How could I do this?

The old code I was using was this below:

This worked because all categories were in different tables, but now I have interjoined them I don't know how to do it.

Code: Select all

select * from kingmaker where id = (select max(id) from kingmaker);
And I have tried several things, such as:

Code: Select all

select * from downloads where category = kingmaker AND id = (select max(id));
And:

Code: Select all

SELECT * FROM downloads WHERE ID = (SELECT MAX(ID)  AND category='kingmaker');
But no matter what I try, I can't get it.Could someone help me?

Re: Sql problem...

Posted: Thu Aug 22, 2013 5:59 am
by Celauran
I would advise against using insert ID to determine most recently uploaded. Adding a DATETIME field would make more sense. That aside, try this:

Code: Select all

SELECT field1, field2 FROM downloads WHERE category = 'Kingmaker' ORDER BY id DESC LIMIT 1

Re: Sql problem...

Posted: Fri Aug 23, 2013 5:49 am
by chris98
Thanks, I have managed to do it now.Do you know what it would be in PDO?

Re: Sql problem...

Posted: Fri Aug 23, 2013 6:06 am
by Celauran
Using PDO won't change the query.

Code: Select all

// Assuming $pdo is your PDO object
$query = "SELECT field1, field2 FROM downloads WHERE category = 'Kingmaker' ORDER BY id DESC LIMIT 1";
$result = $pdo->query($query);