Jump to content


Photo

Problems with "post by date desc/time desc"


  • Please log in to reply
11 replies to this topic

#1 Berserk87

Berserk87
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 28 September 2006 - 05:33 AM

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.

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.

take a look

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


$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> | ";








#2 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 28 September 2006 - 09:18 AM

Post the output of
SHOW CREATE TABLE messages

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

#3 Berserk87

Berserk87
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 28 September 2006 - 10:13 AM

<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...

#4 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 28 September 2006 - 11:42 AM

Post the output of

SHOW CREATE TABLE messages


and

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


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
date                  time
--------------------------------
2000-10-02    10:20:33
2000-10-02    11:21:33
2001-10-03    01:01:01

output
2001-10-03    01:01:01
2000-10-02    11:21:33
2000-10-02    10:20:33

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


#5 Berserk87

Berserk87
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 28 September 2006 - 06:35 PM

http://distortedminds.net/

i time stamped all the posts on the main page.


and heres a screen of 'php my admin'



[attachment deleted by admin]

#6 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 28 September 2006 - 07:14 PM

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()
INSERT INTO tablename (date, ...) VALUES(NOW(), ....);

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 DATE_FORMAT function.

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


#7 Berserk87

Berserk87
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 28 September 2006 - 07:49 PM

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.

$date = date("Y-m-d");

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

and yes, that is being stored in a VARCHAR field



#8 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 28 September 2006 - 10:52 PM

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.

$date = date("Y-m-d");

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

and yes, that is being stored in a VARCHAR field


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

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.

#9 Berserk87

Berserk87
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 29 September 2006 - 04:33 AM


$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());

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?

#10 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 29 September 2006 - 11:06 AM


$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());

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?


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
INSERT INTO tablename (column1, column2, date, column3) VALUES('val1', 'val2', NOW(), 'val3');

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...n/datetime.html
http://dev.mysql.com...-functions.html

#11 Berserk87

Berserk87
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 29 September 2006 - 06:49 PM

ohhhhhhhh

ty, i understand now.

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

or were u just using column as an example?

#12 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 30 September 2006 - 01:07 PM

ohhhhhhhh

ty, i understand now.

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

or were u just using column as an example?


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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users