This was just unearthed in an application someone else (who shall remain nameless) built just about two years ago. It’s reformatted to fit here, but otherwise unchanged.
$result = mysql_query("SELECT * FROM table_name"
. " WHERE id >= '" . mysql_real_escape_string($_POST['id'])
. "' ORDER BY `id` ASC LIMIT 0 , 1");
if ( !($row = mysql_fetch_array($result, MYSQL_ASSOC)) ) {
$result = mysql_query("SELECT * FROM table_name"
. " WHERE id >= '1'"
. " ORDER BY `id` ASC LIMIT 0 , 1");
$row = mysql_fetch_array($result, MYSQL_ASSOC);
}
So… we start by selecting all rows with id >= 27, then limit the results to only the first one… which should be id = 27.
Of course, if I ask for an id that’s not really in the database, I’ll now get some arbitrary other row that happens to be numbered next. Plus, if we didn’t get any rows from the first query, we try again starting from ‘1’, thus returning whatever row we happen to find.
This is just awesome.
Does LIMIT apply before or after the ORDER BY clause? After, I hope. Anyway, I notice the second query is to another table (map_info), though that may be your editing since the first query’s “table_name” is an unlikely actual table. (OK, some people like to include the object type in its name: table_name, table_address, table_people_to_get_even_with.) If it’s a different table, the sequence makes some sense: if the data isn’t in “table_name”, try a variation in “map_info”.
I also notice the WHERE clause compares ID to a string, implying ID is a CHAR or VARCHAR or somesuch. Does mysql do implicit data conversions; i.e. from string ’27’ to int 27?
So what have we learned? The most important lesson is not to let you see my code, ’cause you’ll plaster it all over the internet.
Today’s blog has been brought to you by the letter chr$(48) and the number atoi(“0x4b”).
Both queries were on the same table; I just picked two different “alternate” (i.e., author-protecting) names for my post. Oops. (I’ve since fixed that.)
The SQL implications are interesting. First, ORDER BY does indeed apply before LIMIT.
But the strings… oh, the strings. The ID column is an integer, but MySQL does some gleeful type coercion in the background. In this case, ‘1’ will just become 1. That’s no problem.
When it starts declaring that ‘3,4,5,6,7’ = 3 is TRUE, well, that’s where it starts getting crazy.
[bobbojones@srvtest (none)] SELECT ‘2,1’ = 2 \G
*************************** 1. row ***************************
‘2,1’ = 2: 1