Jump to content

Archived

This topic is now archived and is closed to further replies.

Berserk87

Problems with "post by date desc/time desc"

Recommended Posts

first off, hello.

im completely new here and am hoping that i can get help asap.
i got help from other places before, but one of them seems to have disappeared, and the other it sometimes takes 2 days or more, if i get a response at all.

so ill explain the problem, quoting off a post of mine somewhere else.

[quote]im still a noob to php but i have gotten into mysql and made my own user/posting system.

well, it was working right....

it was organizing the posts by date desc, time desc, so that the newest post would be on top and descending.

what happened was the first day it worked fine, and now its off.

it looks like its organizing them all by date desc, but the second day is time accending.

[url=http://distortedminds.net/]take a look[/url]

that is my website, im just testing stuff out and it does still need quite a bit of work on it.

i actually modified the default wordpress theme a little, but all the other stuff coded into it i made.

u can make a user, u wont be able to do anything yet :P


[code]$result = mysql_query("SELECT * FROM messages ORDER BY date desc, time desc")
or die(mysql_error()); 

while($row = mysql_fetch_array( $result )) {

 

echo "<br> <br> <b>" . $row['username'] . ":</b>";

echo "<font color = 'red'><b><br>" . $row['title'] . "</b></font>";

echo "<br>" .  $row['message'];
 
$id = $row['id'];
 
echo " <br><br> | <a href='comments.php?id=$id'>comments</a> | ";[/code]



[/quote]



Share this post


Link to post
Share on other sites
Post the output of
[code]
SHOW CREATE TABLE messages
[/code]

Also post an example of the dates and times in the table with an example of the output you're getting.

Share this post


Link to post
Share on other sites
<looking at the page from the link>

the two bottom ones are in the right order.

the one of the very bottom was a test, then that same day "Adam" posted again, and it appeared on top of my first one like it should have.

then the next day i posted the "ill do more on it tomorrow. i work 10-3 tomorrow, and im goin to bed righ tnow "

then this morning (i think) or late last night "Adam" posts but the post appeared under my last one instead of above, then i post after that labeled "error?" which appears under "Adams" post instead of on top again...

Share this post


Link to post
Share on other sites
[quote author=shoz link=topic=109798.msg442930#msg442930 date=1159435138]
Post the output of
[code]
SHOW CREATE TABLE messages
[/code]
[/quote]

and

[quote=shoz]
Also post an example of the dates and times in the table with an example of the output you're getting.
[/quote]

By example dates and times I mean the dates and times that are in the table and the order that those dates and time come out. Are the dates and times being recorded properly when being inserted?

This is an example of the example data I'm asking for. Post one in addition to the CREATE STATEMENT for the table. You may even see the error when preparing the example.

table
[code]
date                  time
--------------------------------
2000-10-02    10:20:33
2000-10-02    11:21:33
2001-10-03    01:01:01
[/code]

output
[code]
2001-10-03    01:01:01
2000-10-02    11:21:33
2000-10-02    10:20:33
[/code]

By including the "date" and "time" when outputting the message you'll be able to get the values
[code]
$row['date'];
$row['time']
[/code]

Share this post


Link to post
Share on other sites
[url=http://distortedminds.net/]http://distortedminds.net/[/url]

i time stamped all the posts on the main page.


and heres a screen of 'php my admin'



[attachment deleted by admin]

Share this post


Link to post
Share on other sites
Storing the time in 12 hr format with an "AM" or  "PM" in what I assume is a VARCHAR field is causing the problem. I'd suggest you change the "date" column to type DATETIME and sort based on that field.

You can insert the current date and time in the field using the function NOW()
[code]
INSERT INTO tablename (date, ...) VALUES(NOW(), ....);
[/code]

Ordering by the datetime value will now allow you get an accurate sort. If you need to format the value stored in the field you can use MYSQL's [url=http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html]DATE_FORMAT[/url] function.

[code]
SELECT DATE_FORMAT(date, '%Y-%m-%d/%r') AS date_frmt FROM tablename
echo $row['date_frmt'];
[/code]

Share this post


Link to post
Share on other sites
im not very fluent in php, i know basics, and some intermediate stuff but not much into mysql, so ull have to explain what im replacing that with =\

i get what your saying, im jsut a bit confused.

right now the posts are timestamped using this when its posted.

[code]$date = date("Y-m-d");

$time = date("h:i:s A");[/code]

and yes, that is being stored in a VARCHAR field

Share this post


Link to post
Share on other sites
[quote author=Berserk87 link=topic=109798.msg443256#msg443256 date=1159472983]
im not very fluent in php, i know basics, and some intermediate stuff but not much into mysql, so ull have to explain what im replacing that with =\

i get what your saying, im jsut a bit confused.

right now the posts are timestamped using this when its posted.

[code]$date = date("Y-m-d");

$time = date("h:i:s A");[/code]

and yes, that is being stored in a VARCHAR field
[/quote]

You can remove the $date and $time variables and instead use NOW() after changing the column type of date to DATETIME when inserting.
[code]
ALTER TABLE tablename MODIFY columnname DATETIME
[/code]

You'd now use NOW() in the INSERT the way I showed in the previous post. If you're till having trouble post the script in greater detail showing where you do the insert.

Share this post


Link to post
Share on other sites
[code]

$title = $_POST["title"];

$message = $_POST["message"];
     
$date = date("Y-m-d");

$time = date("h:i:s A");


mysql_query("INSERT INTO messages
(username, title, message, date, time) VALUES('$username', '$title', '$message', '$date', '$time' ) ")
or die(mysql_error());
[/code]

so i replace "$date and $time" with "$datetime = DATETIME"      ?

what whaat else?


"$result = mysql_query("SELECT * FROM messages ORDER BY DATETIME desc")
or die(mysql_error());  " instead?

Share this post


Link to post
Share on other sites
[quote author=Berserk87 link=topic=109798.msg443491#msg443491 date=1159504399]
[code]

$title = $_POST["title"];

$message = $_POST["message"];
     
$date = date("Y-m-d");

$time = date("h:i:s A");


mysql_query("INSERT INTO messages
(username, title, message, date, time) VALUES('$username', '$title', '$message', '$date', '$time' ) ")
or die(mysql_error());
[/code]

so i replace "$date and $time" with "$datetime = DATETIME"      ?

what whaat else?


"$result = mysql_query("SELECT * FROM messages ORDER BY DATETIME desc")
or die(mysql_error());  " instead?
[/quote]

Don't replace the variables $date and $time. Remove them. DATETIME is the type that the "date" column should be. The only time you'll use the keyword DATETIME is when changing the column type. You can use the "ALTER TABLE" example I posted earlier to change the type of the column.

When inserting you'd use something similar to the following
[code]
INSERT INTO tablename (column1, column2, date, column3) VALUES('val1', 'val2', NOW(), 'val3');
[/code]

Note the use of the MYSQL function NOW() to insert the current date and time in column "date" which should be of type DATETIME.

http://dev.mysql.com/doc/refman/4.1/en/datetime.html
http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html

Share this post


Link to post
Share on other sites
ohhhhhhhh

ty, i understand now.

whats the difference between 'column' and 'VARCHAR'?

or were u just using column as an example?

Share this post


Link to post
Share on other sites
[quote author=Berserk87 link=topic=109798.msg443886#msg443886 date=1159555791]
ohhhhhhhh

ty, i understand now.

whats the difference between 'column' and 'VARCHAR'?

or were u just using column as an example?
[/quote]

I don't see where I've made a reference that would confuse my use of "column"(such as "the 'date' column") and the use of VARCHAR(such as "the 'date' column is of type VARCHAR"). If there's a line in one of my posts that you don't quite understand, point it out and I'll try to explain it more clearly.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.