MySQL

MySQL – select row by number

This gets asked alot, so for posterity… Just use LIMIT

E.g. to get the 100th row:

PHP + MySQL – get table column names

MySQL – select (or update, delete) all except most recent

I use this to manage temporary data quite a bit – for example, server logs of a chat, where you only want to keep the X most recent – after every write, or as a scheduled task, I delete all but the last 10. Here’s a query to do that simply (obviously you […]

MySQL – select with limit descending but output in ascending order

Say you want to select the most recent 10 records (ORDER BY date DESC, LIMIT 10), but display them in ascending order… Just put the select as a temporary table in a subquery, and select from that result set with the order you want them returned:

MySQL – join a single table on multiple columns

This might be obvious to folks who spend more time in MySQL than I do, but this came up recently when working on a messaging system, and might be useful to somebody.

Say you have a table with multiple foreign keys to the same table… In my case, I had a messages table that […]

MySQL – Select conditional boolean expression

And uncommon but useful feature of MySQL includes selecting the results of a conditional or boolean expression.

E.g., say you have a Users table where you’re tracking if the user is actually logged in, and whether he wants to be shown as logged in, but don’t want to show both values in the response. […]

MySQL – compare dates

Values within the last 5 minutes:

Values older than 1 month:

JavaScript + PHP + MySQL – determine if user is online or logged in

This is a pretty common task that doesn’t have a perfect answer. It’s not possible to determine if a user disconnects, leaves the page, closes the browser, etc (besides window unload methods, which aren’t practical for this use case). So we fake it as best we can. Here’s how I do […]

MySQL – get most frequent values

here’s a little query that’ll return the 10 most common values for any particular field in a MySQL table.

MySQL – Add Row Count to each Row of Result