Retrieve Records In Reverse Order

bluwulf

Can someone say if there is an SQL query to retrieve records from a MySQL DB in reverse order ? I just want to reverse the order I do not want to order them on any field.

So inother words this not what I looking for :

SELECT column,column,column FROM some_table WHERE somthing = something ORDER BY column DESC

What I really want to do is list the latest added records in the DB (this data is to be list in a webbased table).

rachel2004

To get the id of the latest record added:

http://www.php.net/manual/en/function.mysql-insert-id.php

bluwulf

Thanx for the info. but there is some thing I don't understand...according to the site this is what mysql_insert_id() does :

mysql_insert_id() returns the ID generated for an AUTO_INCREMENT column by the previous INSERT query using the given link_identifier. If link_identifier isn't specified, the last opened link is assumed.

None of the columns in my DB are AUTO_INCREMENT columns.

And if I'm correct this function just returns the last inserted reccord (i.e the ID to the last inserted reccord)...but I'm not only intrested in the last reccord I'm intrested in all the reccords (in reverse order )...or is it that subsequent calls to this function returns the ID for the reccord before the last reccord that wuz queried ?

rachel2004

Can you provide more info on what you are using as a primary key for your table?

TheDefender

Best thing to do is to add an auto increment column as a Primary indexed key, and just add an ORDER BY primary_id DESC clause to your query...

Otherwise, it gets messy trying to do it within PHP, because you'd have to make a 3D array out of your array results, then rsort the outer array, and then output that... If you add the column, then you just pull it out in reverse order by the index... things get much less messy and much more easy.

bluwulf

Originally posted by rachel2004
Can you provide more info on what you are using as a primary key for your table?

My PK is submit_date and submit_time which are of data type DATE and TIME respectively

rachel2004

ORDER BY date_column DESC

BTW, is there a reason you have "date" and "time" broken into two separate columns instead of just using columns of type "datetime" or "timestamp"? Save yourself some work, friend.

bluwulf

Originally posted by rachel2004
ORDER BY date_column DESC

Ok I think that I understand that...thanx

Originally posted by rachel2004

BTW, is there a reason you have "date" and "time" broken into two separate columns instead of just using columns of type "datetime" or "timestamp"? [/B]

I'm not so sure if there is a reason.... ! If I followed your suggestion would I then be able to do queries that would give me info. relating to a particular time....in other words would I be able to isolate the time from the "datetime" and timestamp" and likewise be able to isolate the date ?

I saw the data types date and time when I was reading (yeh I kno I need to do more reading) and figuered perfect.

But I certainly do welcome any more advise you may have...thanx again

TheDefender

If you are storing dates that are after Jan 1, 1970, and won't ever store older dates, then use the strtotime() function to turn your date and time into a Unix Timestamp value, and store them in a column that is INT(12) type.

Otherwise, store your date in a DATETIME type field (YYYY-MM-DD HH:MM:SS)

Either way, it is sooooooo much easier to do date and time calculations when it is all in one instead of split between separate date and time type fields.

Look in the PHP Manual at the date(), strtotime(), strtftime(), time() and mktime() functions.

Also, look in the MySQL manual at using DATE_FORMAT when you extract data.

Ans yes, you can nail it down to the exact second if you need to:

For example:
echo date("s");
gives you the second on your computers clock at the moment the page loaded.

rachel2004

TheDefender has named them all off, so it's just a matter of looking in the manuals for each of the functions and their formal paramenters (if any).

I wanted to add that I have seen another regular poster to this forum swear up and down in several posts that it is faster to do everything in MySQL (or a db in general) than it is to do things in PHP--where it is possible to do things in MySQL, that is. This is hearsay, for now, since I haven't taken to benchmarking his suggestions, but he is pretty smart about databases, so I am not so quick to discount his/her suggestion. With that being said, the MySQL function date_format() is what I have been using lately to format my date/time results. When I say I am "using" it, it means I am trying to find myself in a situation where I can't do a format that I could do otherwise with a PHP function that TheDefender had already listed.

Lastly, the column types datetime and timestamp are NOT the same thing... I know you didn't explicitly say you thought that way, but I don't want any of my previous posts to get you thinking that they are interchangeable. If you have ONLY ONE timestamp column in your table, it will be updated AUTOMATICALLY each time a change is made to a record or when a new record is inserted into the table. If you have TWO timestamp columns, only the second column of the two will display said characteristics. Columns of type datetime are better for use when you want to capture "timestamp-like" info, without the auto-update features of type timestamp. For me, I use datetime when I do a column for things like "recordCreatedDatetime" and I use type timestamp for things like "recordLastModifiedDateTime". Yes, you could simply use two datetime columns for both things, but we're programmers and we're supposed to be lazy, yes?

bluwulf

BTW, is there a reason you have "date" and "time" broken into two separate columns instead of just using columns of type "datetime" or "timestamp"?[/quote]

Just thought of this question......doesn't "good" DB design "require" that fields are "always" in an atomic form....an is the very same reason we don't usually put a "name" feild in a DB that would hold the name "John Doe" (even though we have functions that can split "John Doe" into "John" "Doe") but instead we use first_name and last_name thus we now have "John" under first_name and "Doe" under the last_name column in our DB ??

rachel2004

Using date_format function or unix_timestamp function from MySQL, can you forsee where you are limited in pulling either the month, year, date, hour, minute, or second from a column of type datetime or timestamp?

If we take the idea of breaking dates/times up to its logical conclusion, then everyone would also just break up the parts of a date into a column for year, column for month, column for date, hour, minutes, and so on.

bluwulf

Originally posted by rachel2004
Using date_format function or unix_timestamp function from MySQL, can you forsee where you are limited in pulling either the month, year, date, hour, minute, or second from a column of type datetime or timestamp?

Well...no !

bluwulf

Tell me something though....I would be using "submit_DATE_TIME" (which is of datatype DATETIME ) as my primary key. Is it all conceivable that the system (I intend to use the system's date and time so the user wouldn't have any controll over this) could insert a reccord into the DB at the same "submit_DATE_TIME" and thus would violate it being the primary key in the DB and thus warrant me defining an auto_increment feild ?

TheDefender

Yep... Albeit a tiny possibility, it still exists. Therefore, you should define a separate field as the primary key. An auto-incremented INT(11) type should work. Or you could go with TINYINT(3) or something, depending on how many records you are planning to have.

rachel2004

bluwulf, I know you are researching these things about databases... I can tell from th fact you are asking me about normalization, but that's a good thing. That is how I learned, too... that, and many Amazon.com purchases. Listen, for now, whatever it is that you are reading that is telling you about db design probably has a suggestion similar to TheDefender's suggestion: use auto_increment. If it doesnt' say that anywhere in regards to a primary key, I would question the remaining info in your publication. That is why I asked you in one of my earlier posts what you were using as an primary key. That sent up a flag when you told me you didn't have an auto_increment column.

Looking back at this thread, what if you had done that to start with? Granted, we learn as we go--and trust me, I am in no way trying to insult your intelligence, but prod you in the right direction-- but now that you know, review this thread and see how things would have been nipped in the bud when I first suggested the use of the PHP function for retrieving the last inserted id.

bluwulf

Originally posted by TheDefender
Yep... Albeit a tiny possibility, it still exists. Therefore, you should define a separate field as the primary key. An auto-incremented INT(11) type should work. Or you could go with TINYINT(3) or something, depending on how many records you are planning to have.

Thanx for clearing that up ! I took it for ganted that that is a possiblity !

TheDefender

YW. The best way to remember it is like this:

You always want to ensure your primary key will be unique when entered to avoid the possibility... So, it's always better to let the MySQL DB manage the unique primary keys, rather than you trying to devise a scheme to create unique numbers.

When I build DBs, all of my tables have a primary key, auto-incremented, INT(11) type called tablename_id. Just makes it tons easier, especially when working with foreign keys, InnoDB and whatnot.

bluwulf

Thanx again for your advise!