Sql problem...

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
chris98
Forum Contributor
Posts: 103
Joined: Tue Jun 11, 2013 10:47 am
Location: England, United Kingdom

Sql problem...

Post 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?
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Sql problem...

Post 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
User avatar
chris98
Forum Contributor
Posts: 103
Joined: Tue Jun 11, 2013 10:47 am
Location: England, United Kingdom

Re: Sql problem...

Post by chris98 »

Thanks, I have managed to do it now.Do you know what it would be in PDO?
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Sql problem...

Post 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);
Post Reply