Sorting by two columns and outputting

[pseudonym]

🙁

Today, I feel like a n00bie again... well I always have been really.. but Ive managed to grasp most bits reasonably well

Im building a lite CMS tailored to suit my sites needs.. but Ive come unstuck with a query... so maybe you guys can help me?

This is my table structure...

newsid int(11) No
newswatch tinyint(1) No 0
newstitle varchar(250) No
newsintro mediumtext No
newsbody longtext No
newspreimage varchar(255) Yes NULL
newsquote mediumtext Yes NULL
newspostimage varchar(255) Yes NULL
newsclosingcomment varchar(255) Yes NULL
newssource varchar(50) No
newslink varchar(255) No
newscategory int(11) No 0
newstimestamp timestamp(14) Yes NULL
newsthreadid int(11) No 0
newsfirstpostid int(11) No 0
newspostername varchar(30) No
newspostervbullid

.. and what Im "attempting" to do.. is display like this (imagine this would be HTML)...

News--------todaysDate-----Watch 3
News title - link
News title - link
News title - link
News--------todaysDate-----Watch 2
News title - link
News title - link
News--------todaysDate-----Watch 1
News title - link
News title - link
News title - link
News title - link
News--------yesterdaysDate-----Watch 4
News title - link
News title - link
News--------yesterdaysDate-----Watch 3
News title - link
News title - link
News title - link
News title - link

... and so on..

Just three days.. then the latest watch .. then the news in that watch (which the "watch" column does.. )

function epochtotime ($date, &$year, &$month, &$day, &$hour, &$minutes, &$seconds) {
$epochdate = Date("Y-m-d-H-i-s", $date);
$fulldate = explode("-", $epochdate);
global $year;
global $month; 
global $day;
global $hour;
global $minutes;
global $seconds;

$year = $fulldate[0];
$month = $fulldate[1];
$day = $fulldate[2];
$hour = $fulldate[3];
$minutes = $fulldate[4];
$seconds = $fulldate[5];
}

Thats my time function.. so I can get a date from the *nix timestamp...

$news=$frontend->query("SELECT newspost.newstitle, newspost.newswatch, newspost.newsintro, newspost.newstimestamp, newspost.newsthreadid, newspost.newspostername, thread.replycount
                        FROM vbulletin.thread, frontend.newspost
                        WHERE newspost.newsthreadid = thread.threadid
                        ORDER BY newspost.newswatch ASC, newspost.newstimestamp DESC");

$watch='';
$finaldate='';
while ($results = mysql_fetch_array($news))
{
$date = $results['newstimestamp'];
epochtotime ($date, &$year, &$month, &$day, &$hour, &$minutes, &$seconds);
    if($day !=$finaldate)
    {
        $finaldate=$day;
        echo "{$finaldate}<br />";
    }
        if($results['newswatch'] !== $watch)
        {
            $watch=$results['newswatch'];
            echo "<b>News watch {$results['newswatch']}</b><br />";
        }
echo "{$results['newstitle']}<br />"; 

}

.. is my current query.. but eek.. it does no workie at all

19
News watch 0
Testing new algo
A random news story
AT&T sues Ebay & Paypal
News watch 1
VIA PT880 & SiS 655FX P4 chipsets vs the Intel i875P
Arctic Silver 5 Shootout @ GruntvillE
Futuremark Debuts PCMark04 For PC Performance Benchmarking
Catalyst 3.9 Investigations

"Testing new algo" was inserted into the db today..

The rest were entered into the database yesterday and carry a yesterday timestamp.. so there should be two days worth of entries.. one for today.. and one watch 0 .. and yesterday.. several entries and two watches..

Can anyone suggest or point where Im going wrong..?? So I can rejig it.. and understand where I went wrong? 🙁

[toplay]

Change this:

if($results['newswatch'] !== $watch)

To this:

if($results['newswatch'] != $watch)

Not equal to is with one equal sign.

[pseudonym]

:o Well Ive just proved my total n00bness.

I had the column type in SQL as timestamp... so my unix timestamp function doesn't work with it. Hence everything was all fubared..

I changed the column type and used my unix function to generate timestamps.. and voila.. it works.. .. once Id removed the double = ... thanks for that!!

I feel so stupid.. :rolleyes:

[toplay]

Well, done. You found the problem. Don't feel bad about the mistake. That's how we learn sometimes.

FYI: I don't see the point of specifying all the global definitions in your epochtotime() function when you're using pass by reference anyway. You can remove the global definitions in that function and it will still work.