Order mysql results without id
Normally I would have a table field named ID when auto-added. So I could order the use of that field, etc.
However, I have no control over the structure of the table and am wondering how to get the results in reverse order by default.
I am currently using
$q = mysql_query("SELECT * FROM ServerChat LIMIT 15");
However, as I said, there is no field that I can order on, so is there a way to tell mysql to change the order in which the results are fired? 1.e the last line for the first line instead of the default.
a source to share
MySQL supports ordering by column order:
SELECT * FROM ServerChat ORDER BY 1 DESC LIMIT 15
But IIRC this usage ORDER BY
is deprecated in the SQL standard. Don't be surprised if some RDBMS vendors stop supporting it (eventually).
In general, it's best to know the structure of your table.
a source to share
In fact, you get your results in a so-called "table order", which may look like the data is added to the table, but this order is unstable. There are a number of operations that can change the order in which the results are obtained without changing the data in the table itself.
To reproduce the kind of order you see, I would suggest adding a column to a table like ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
. This will give you the column for the order and hence overrides that order. You should probably add an index to this column if this operation is frequent.
a source to share
According to this article , SQL-92 allows the user to query table structure information from a "well-known" view or table called INFORMATION_SCHEMA, SQL-92 is supported by MySQL 5.0 and above.
Example / Excerpt:
SELECT table_name, column_name, is_nullable, data_type, character_maximum_length
FROM INFORMATION_SCHEMA.Columns
WHERE table_name = 'employees'
So you can use a list of column names so that the user can select which column he ordered and then use this SO answer to figure out how to build dynamic SQL to execute the query correctly.
I haven't tried it with MySQL, but the method certainly makes sense to me.
a source to share
The table must have a unique index pointer. It doesn't have to be a named ID, but it is usually required and is probably what defines the order currently being returned. What is it? Anyway, I understand that you should be able ORDER BY ... DESC
(or if that doesn't work, ASC
) like this example, with a unique identifier hash
:
$q = mysql_query("SELECT * FROM ServerChat ORDER BY `hash` DESC LIMIT 15");
a source to share