Jump to content

ORDER BY date


Sami
 Share

Recommended Posts

I have a problem.I'm using PHP running a guestbook, and it seem to order by the least first character (Maybe I'm wrong?).Anyway, that's not the main problem. How do I use the count() function while storing the data in the database? :)

Link to comment
Share on other sites

What do you want to use COUNT() for? Can you post your code?
Create IDs from 0 and up.That would make it posibille to use "ORDER BY id DESC"? Wouldn't it?Btw, I have currently not access to the codes I'm alredy using, because I use my own server, whitout open port 21, but you can find the codes I'm using here: http://w3schools.com/php/php_mysql_select.aspIf you don't use PHP, I can tell you that you find the SQL request inside the "sql_query". Edited by Sami
Link to comment
Share on other sites

You can create a field that will automatically generate unique IDs with the AUTO_INCREMENT attribute. You can ORDER BY any field though, such as the date.You can always just paste the code you have in your posts.

Link to comment
Share on other sites

You can create a field that will automatically generate unique IDs with the AUTO_INCREMENT attribute. You can ORDER BY any field though, such as the date.You can always just paste the code you have in your posts.
I'm alredy order it by the date.The posts made in example april will be shown over the posts in june, but they should be shown under.That's why I want an ID number for every post. And I don't know where to put the AUTO_INCREMENT.
INSERT INTO comments (date, name, comment, ip, orderby)VALUES('$d','$n','$r','$_SERVER[REMOTE_ADDR]','$dd')

Edited by Sami
Link to comment
Share on other sites

I'm alredy order it by the date.The posts made in example april will be shown over the posts in june, but they should be shown under.
Then just change the direction of the sort. It appears that you are currently ordering by date ascending, what you want is descending.You can do that with the ORDER BY clause:
ORDER BY date DESC

Link to comment
Share on other sites

Then just change the direction of the sort. It appears that you are currently ordering by date ascending, what you want is descending.You can do that with the ORDER BY clause:
ORDER BY date DESC

I'm already doing that!
Link to comment
Share on other sites

What do the dates in the database look like? Are they datetime values? Are they varchar? Are they int? You aren't giving us a lot of details to help fix the problem. Are you storing dates as text and then trying to order them chronologically?

Link to comment
Share on other sites

What do the dates in the database look like? Are they datetime values? Are they varchar? Are they int? You aren't giving us a lot of details to help fix the problem. Are you storing dates as text and then trying to order them chronologically?
All are text.
Link to comment
Share on other sites

If you want to keep them as text, then you need to use a conversion function when you sort. Or, you can change them to be an actual date value and then use a conversion function to format it how you want it to be printed. You can use str_to_date to convert your text to an actual date:http://dev.mysql.com/doc/refman/5.1/en/dat...ion_str-to-datee.g.:ORDER BY STR_TO_DATE(date_field, 'your format string')

Link to comment
Share on other sites

If you want to keep them as text, then you need to use a conversion function when you sort. Or, you can change them to be an actual date value and then use a conversion function to format it how you want it to be printed. You can use str_to_date to convert your text to an actual date:http://dev.mysql.com/doc/refman/5.1/en/dat...ion_str-to-datee.g.:ORDER BY STR_TO_DATE(date_field, 'your format string')
Day.Month.Year Hour:MinutteBut I've made an own field to order by. Should I just make it "date"?And would MySQL accept secounds? Wouldn't it be a lot easier whit a single ID number/counter?
Link to comment
Share on other sites

If you just want to print the items in the order they were inserted, the most common way is to give the table an auto-increment ID and then order by that. If you're adding a new date field, the date or datetime types are options, I usually store my dates as Unix timestamps and just use a regular int field.

Link to comment
Share on other sites

If you just want to print the items in the order they were inserted, the most common way is to give the table an auto-increment ID and then order by that. If you're adding a new date field, the date or datetime types are options, I usually store my dates as Unix timestamps and just use a regular int field.
How do I use the auto-increment ID?
$sql="INSERT INTO comments (date, name, comment, ip, orderby)VALUES('$d','$n','$r','$_SERVER[REMOTE_ADDR]','$dd')";

Link to comment
Share on other sites

You can't use a CREATE TABLE query with a table that already exists. If you want to change an existing table, you need to use ALTER TABLE. The syntax for that is in the manual.If you have a tool like phpMyAdmin available, you can use that to add a new field instead of manually using queries.

Link to comment
Share on other sites

You can't use a CREATE TABLE query with a table that already exists. If you want to change an existing table, you need to use ALTER TABLE. The syntax for that is in the manual.If you have a tool like phpMyAdmin available, you can use that to add a new field instead of manually using queries.
Can you give me an example?
Link to comment
Share on other sites

From what I see in the manual there it actually looks like it's not possible to add an auto-increment field to an existing table, because all fields added to a table by default have a null value, and an auto-increment field cannot have null values. So, the best way to add a new ID field to your table is to create a new table, and then copy the data to it. So create a new table exactly like your current table, except add an auto-increment field. This was the example in the manual:

CREATE TABLE animals (	 id MEDIUMINT NOT NULL AUTO_INCREMENT,	 name CHAR(30) NOT NULL,	 PRIMARY KEY (id) );

So there are 2 important parts there. First it adds the field as a mediumint and specifies auto_increment for the field, and then at the bottom there it says the id field is the primary key. That's exactly what you want, you want a field called ID with auto_increment, and you want it to be the primary key for the table. It's your choice whether you want to use mediumint or int, the difference is the maximum value allowed. A mediumint is probably big enough, but if you add too many comments you'll need to change it to an int or bigint. Once you create the table, you can use a query like this to copy the data over:INSERT INTO comments_new (field1, field2, field3) SELECT field1, field2, field3 FROM commentsThat will copy the data from comments to comments_new. Make sure to list all of the fields in the comments table. Do not list the new ID field, the value will be filled in automatically. Make sure also to ORDER BY whatever field you want to make sure the comments get copied in the correct order. Once that's done, the first comment in the table will have an ID of 1, the second will be 2, etc. You can order by that field to list them in order.Once the data is copied you can delete the old table and rename the new one.

Link to comment
Share on other sites

From what I see in the manual there it actually looks like it's not possible to add an auto-increment field to an existing table, because all fields added to a table by default have a null value, and an auto-increment field cannot have null values. So, the best way to add a new ID field to your table is to create a new table, and then copy the data to it. So create a new table exactly like your current table, except add an auto-increment field. This was the example in the manual:
CREATE TABLE animals (	 id MEDIUMINT NOT NULL AUTO_INCREMENT,	 name CHAR(30) NOT NULL,	 PRIMARY KEY (id) );

So there are 2 important parts there. First it adds the field as a mediumint and specifies auto_increment for the field, and then at the bottom there it says the id field is the primary key. That's exactly what you want, you want a field called ID with auto_increment, and you want it to be the primary key for the table. It's your choice whether you want to use mediumint or int, the difference is the maximum value allowed. A mediumint is probably big enough, but if you add too many comments you'll need to change it to an int or bigint. Once you create the table, you can use a query like this to copy the data over:INSERT INTO comments_new (field1, field2, field3) SELECT field1, field2, field3 FROM commentsThat will copy the data from comments to comments_new. Make sure to list all of the fields in the comments table. Do not list the new ID field, the value will be filled in automatically. Make sure also to ORDER BY whatever field you want to make sure the comments get copied in the correct order. Once that's done, the first comment in the table will have an ID of 1, the second will be 2, etc. You can order by that field to list them in order.Once the data is copied you can delete the old table and rename the new one.

It don't look like I can use phpmyadmin for rename it, and I don't know any command that does. :)
Link to comment
Share on other sites

In phpmyadmin select your database, then select the table to rename, select operations tab from along top, enter new table name in "Table options" rename table to:Edit: Do you mean you don't accees to phpmyadmin at all?

Edited by dsonesuk
Link to comment
Share on other sites

these are the code that may help you, but please make backup before...just in casechange table nameALTER TABLE autothis RENAME TO autothis_new;add new column indexed autoincrementALTER TABLE autothis ADD COLUMN id INT NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (id);amend column to indexed autoincrementALTER TABLE autothis CHANGE id id INT NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (id);

Link to comment
Share on other sites

In phpmyadmin select your database, then select the table to rename, select operations tab from along top, enter new table name in "Table options" rename table to:Edit: Do you mean you don't accees to phpmyadmin at all?
Sure I have access to phpmyadmin. Show should I else access the database easily?
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...