MySql Query for a weblog.

[binarynomad]

Hi everyone,

I'm pretty new to PHP and MySql, I've been following it for about 2 years, but havn't been using it much until now.

Yesterday, I created a simple weblog database and wrote a script to pull information from it and plug it into a table on a main web site. I want to refine the query. Right now I it just pulling the last 5 entries into the database.

---

Here is the table:

<logs>
ID:mediumint, primary key not null
date: date
type: enum(private, public, work)
log: text

---

Here is the current query:

SELECT date, log FROM logs ORDER BY date DESC LIMIT 5.

---

PROBLEM:

If I make 3 entries 2 days ago, 1 yesterday, and 1 today, I get those 5 entries, but what I want is all entries for 1 day to equal 1. So what I'm trying to query is:

"Give me the date and log entry for the last five unique days"

Which would equal five days with x number of entries.

I can pull up the last 5 unique entries with this:

SELECT DISTINCT(date) FROM logs ORDER BY date DESC LIMIT 5;

...but I cannot pull all the associated logs. I only get a single log for days that have multiple entries.

---

Here is the query I would like to run, but cannot:

SELECT date, log FROM logs WHERE date IN (SELECT DISTINCT(date) FROM logs ORDER BY date DESC LIMIT 5);

...I figure the only ways this can be done is:
1) wait until subquerys are implemented 🙂
2) breake it out to multiple tables

Im trying to avoid that if possible and keep it all in one table.

Any Suggestions??

[mikeatrpi]

select log, date
from logs
group by date
order by date desc;

I couldn't figure out how to limit the count of date's to be 5. Perhaps you need two select statements...

select distinct date
from logs
order by date desc
limit 5;

PHP use implode() to build a string of dates.

select log, date
from logs
where date in (LIST FROM PREVIOUS QUERY)
order by date desc;