manual auto-increment (sort of)

[michaellunsford]

using PHP to generate a select box. Client wants the ability to change the order the items appear in the select box. Right now I must be going the long way around:

[code]<?php
$last_one = mysql_fetch_assoc(mysql_query("SELECT * FROM `table` ORDER BY `sort_by` DESC LIMIT 1"));

mysql_query("INSERT INTO `table` (`name`,`value`,`sort_by`) VALUES ('$name','$value','".($last_one['sort_by']+1)."')");
?>
[/code]

This works great, but I keep thinking there's a shorter way to do this. Is there a way to tell the insert query that I want the greatest value of `sort_by` + 1 without using a select query to find out what the last one is?

[fenway]

You have two options: use user variables or subqueries (4.1+).

[code]
SET @nextSortBy := SELECT MAX(sort_by) + 1 FROM `table`
"INSERT INTO `table` (`name`,`value`,`sort_by`) VALUES ('$name','$value',@nextSortBy)"
[/code]

or:

[code]
"INSERT INTO `table` (`name`,`value`,`sort_by`) VALUES ('$name','$value', (SELECT MAX(sort_by) + 1 FROM `table` ) )"
[/code]

In either case, you should be using max() and just getting back a single value, rather than * on the whole table with an order by & limit.

Hope that helps.