CASE CLOSED. The solution was partially implemented and is now fully working. There is a MySQL field called dateposted. I have changed this to be a DATETIME field. The sort is now by date DESC, dateposted DESC. This should all work. Thanks for getting those grey cells working!
In the PHP code for the page which shows our news in Norwegian, we sort in descending order based on the date field. The problem is that this means that the newest items added to the database within a single date are moved to the bottom. How can we keep the newest items on top?
Here is the code we’re using:
$query2 = “SELECT * FROM news WHERE language = ‘no’ order by date DESC LIMIT 100″;
3 September 2008 at 12:01 pm
I’m guessing the news table has an primary key field which auto-increments? In that case, the newest item would always have a higher value so then you can do:
ORDER BY id, date DESC
Replace id above by whatever name the key field has.
3 September 2008 at 12:04 pm
Unfortunately, no. So the question is, can it just sort in reverse order showing the last record in the database first? Maybe I put everything into an array, then sort the array in reverse order?
3 September 2008 at 12:05 pm
Here is a Forum that has helped me in the past: http://www.phpfreaks.com/forums/index.php/board,1.0.html.
3 September 2008 at 12:12 pm
Thanks, Richard. I’ve registered and will do a test post to this list. I’ve used groups like Perl Monks in the past with some success.
3 September 2008 at 12:15 pm
And I’ve posted a version of this question there.
3 September 2008 at 12:27 pm
If I understand the question properly, you need to have finer grained sorting on the time (by second, not by day). Do you have a field in the database for storing a timestamp when the story is entered into the database?
3 September 2008 at 12:41 pm
Excellent. I have sent a note to another friend who lives and works in Florida. He is not exactly working within the union movement, but knows his tech like it ain’t nobody’s business. I invited him to help us out — and hope he arrives here as the day kicks in State-side.
3 September 2008 at 12:57 pm
OK, I tried to create a field for a proper timestamp. (This would not entirely solve the problem because what we really want is to show the latest news stories within a certain, and then within the previous date, etc. But this could be a beginning.)
I created a field in the table with the type of ‘date’ – would this include the time? And unfortunately I told it in Perl to insert only the date into that field. In other words, there are two date fields. I guess a solution would be to make sure date and time both fit into the new (second) date field and then do a sort on the two fields — would that work? Is the type ‘date’ going to hold the date and time?
3 September 2008 at 12:59 pm
A reply from PHP freaks essentially suggesting what I’ve indicated above:
“You’ve got to store information about when within given date, the item has been added. Most logical would be extending date field to store date and time.”
Remember, we have two date fields — the one which appears publicly (and that only needs the date) and an internal one which would work fine if we added a time to it