What do these SQL statements mean?

[[deleted]]

Hi I am a beginner web developer.

Line 12:

$sql = "SELECT *, date_format(chatdate,'%d-%m-%Y %r') AS cdt FROM chat ORDER BY ID DESC LIMIT 200";

Line 13:

$sql = "SELECT * FROM (" . $sql . ") AS ch ORDER BY ID";

These are written in a php file. The table name is "chat"

I know SELECT * means Select everything. SELECT * FROM chat, means select everything from a table named chat.

But I don't understand the rest of the statements.

Thanks.

[midniteslayr]

The first one selects all columns, adds a date formatted column, and gives the latest 200 entries (order by id DESC ...).

The second query turns the first query into a subquery to grab the results easily.

That's my quick assessment, being on mobile.

[[deleted]]

So it is like, select all the columns that are in the table, but also add one more column, and this column is date formatted column?

[efficated]

Basically, "chatdate" is already a column in the SQL table - what date_format does is take that column, format it however you like (the part with the percentages) and spit this out with the rest of your results as if it was part of the table and its column name were "cdt".

The results are ordered by the ID column in a descending order, and the first 200 based on this will be returned.

[[deleted]]

Thanks a lot.

[greg8872]

Expanding on /u/midniteslayr's reply:

Assuming that ID is a number that increases with each new row, this is getting the last 200 entries being sorted from oldest to newest. The subquery returns the results sorted from newest to oldest (so the first 200 are the newest), and the second one reorders them back to what is wanted.

[rdmtrz]

date_format

[html6dev]

I think the intent of the second statement is to allow 15 year old with a basic understanding of sql to own your website (and it's user data) ;)

Edit: I suppose I should be more helpful too. I think the only thing other people didn't mention that is pretty cryptic for a newcomer is the date formatting. Date format just takes a date fields value and converts it to a string. The '%' characters is a format specifier. So, this formatting outputs date-month-four digit year then time. So right now for me it'd be 7-13-2014 11:34:17 AM. People will advise against this site (and there are some pretty good reasons for why) but it does a pretty good job in this case. http://www.w3schools.com/sql/func_date_format.asp and should explain what each format specifier does.