Question about SELECT

Databases
1

Hi everybody and Happy New Year!

I have created a dropdown menu. When you hover above it it will fetch some data from a mysql database
I use this string:

SELECT * FROM node LIMIT 3

That works great. Now, I just don’t want fetch some info, I want the latest info to poplate the menu. I’ve tried to add Date datetime and more, but it will not work.
Examples:
SELECT * FROM node ORDER BY Date DESC, LIMIT 3
SELECT * FROM node LIMIT 3 ORDER BY Date DESC
SELECT * FROM node ORDER BY date(created) DESC LIMIT 3

‘node’ is a table in the database and ‘created’ a field. The website is Drupal 9.
etcetera.
What is wrong with my syntax?

2 
SELECT * FROM node ORDER BY Date DESC, LIMIT 3

Comma after DESC.

SELECT * FROM node LIMIT 3 ORDER BY Date DESC

Sections placed wrong and Date without backticks.

SELECT * FROM node ORDER BY date(created) DESC LIMIT 3

Loooks normally. What is error you’ve got?

3

No errors. The menu just does not do anything.

Your last example works, though. But it does not fetch the data in date order. When I check the database in phpmyadmin everything is sorted in the correct date order.
There’s a field in the database that just numbers the content. It’s called ‘nid’, but I don’t know if I can use ut or how.

4

Something beginning with “N” (node I suppose) ID, is my guess. Sounds like the primary index or ID column of the table. Most tables have one to uniquely identify each row (any table will have something to uniquely identify each row).
Typically it would be used to select a particular row by its ID number.

SELECT * FROM node WHERE nid = '321'
5

Can you use it for sorting purposes?

6

You can sort by ID certainly, if you have a use for that.
That column is generally set to auto increment, so when a new row is added, it is automatically assigned the next sequential number.
Though a table will often have gaps in the ID numbers, where things have been deleted, but there is always an order.
Though the ID will most likely follow the same order of creation date, the date will be a more reliable record to sort by.

7

This get the data in correct order: SELECT * FROM node ORDER BY vid DESC
This does not: SELECT * FROM node ORDER BY ‘vid’ DESC

Now, if I want to use date I must write: SELECT * FROM node ORDER BY created DESC

So, problem is SOLVED.
Happy New YEAR!

8

Use back-ticks around column names, not quotes.

SELECT * FROM node ORDER BY `vid` DESC

or

SELECT * FROM node ORDER BY `created` DESC

Though as you found, it is not always necessary.

9

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.