MySQL – select row by number

This gets asked alot, so for posterity… Just use LIMIT E.g. to get the 100th row: SELECT whatever FROM table LIMIT 100, 1

PHP + MySQL – get table column names

$pdoInstance->query("desc table_name")->fetchAll(PDO::FETCH_COLUMN);

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 could use […]

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: SELECT * FROM ( SELECT * FROM table ORDER […]

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 had […]

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. You […]

MySQL – compare dates

Values within the last 5 minutes: SELECT value FROM table WHERE value > DATE_SUB(NOW(), INTERVAL 5 MINUTE) Values older than 1 month: SELECT value FROM table WHERE value < DATE_SUB(NOW(), INTERVAL 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 it. 1. Create a […]

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. SELECT <column>, COUNT(<column>) AS popularity FROM <table> GROUP BY <column> ORDER BY popularity DESC limit 10;

MySQL – Add Row Count to each Row of Result

SELECT *, @count:= @count+1 as count FROM , ( SELECT @count:=0 ) any_alias_here_is_fine