Poor SQL

planetsim

Okay im looking at improving my SQL and i want to start with a simple news script.

Basically what i want to do in my mini project is to

Date

News for Date here

etc sort of like

Okay i can do that but with 2 queries one of which is inside the Loop. Now i want to know if at it is possible to display what i showed with One Query and loop. Ive tried Group By but it may require a sub query which i have never actually done before.

LordShryku

Why would this take two queries? What's your table structure going to look like? Right now, I'm thinking it would be one query of select from table where date = 'the date'

planetsim

hmm.. you have it all wrong.

I want to select all the dates and sort them by the date and as im sure there are going to be multiple articles of the same date then sort by id

SELECT * FROM news GROUP BY date DESC ORDER BY id DESC

But with that Query it only shows the first article for each date. I want all shown. That is why i think i may requrie a Sub Query and i have no idea how to do it exactly.

LordShryku

Ahhhh, ok I see. So, in this case, you won't want a group by at all. What I would do is just order by date, then id(if needed), and use PHP to see where the date changes to add breaks, table row, or whatever your seperation method will be as needed

planetsim

Hmm so this isnt possible with SQL to do automatically.. That is a pitty

LordShryku

It's possible, though it's probably just more work than neccessary. And really, the resultset won't be to much different if you did it through a subquery. You would still need to do something with it.

planetsim

Ah well thanks anyway.

Bunkermaster

do a select ordered by date and then while looping on your record set create a new frame when date changes...

ultraslacker

Tis is a simple query and does not require a subquery, assuming you have a sane database design. LordShyrku and Bunkermaster already answered your question, but let me make it painfully clear -

SELECT title, article, newsdate
FROM news
ORDER BY newsdate DESC, id

Hmm so this isnt possible with SQL to do automatically.. That is a pitty

Yes it is, you are just confused on how to go about it.

hiroh2k

Okay i can do that but with 2 queries one of which is inside the Loop. Now i want to know if at it is possible to display what i showed with One Query and loop.

$old_date = false;

$table = "";
$day = "";
while ($rows = dbfetch ($query)) // query order by date, of course.
{
$day .= $rows ["news"]; // news inside the layer.
if (!(old_date === false) // FLAG, do not check in 1st loop
&& ($rows["date"] != $old_date)) // date change.
{
$table .= add_header ($old_date) . $day . add_footer . add_separator;
$day = "";
}
$old_date = $rows ["date"];
}
$table .= add_header ($old_date) . $day . add_footer . add_separator;

I haven't checked the script but i think it should work 🙂

DoD

How's your date stored? Best way IMO is to use the time() function to store it... so you have the date down to the second. Then, to compare the days, just use the date function on it...

Bunkermaster

always use timestamps