Jump to content


Photo

manual auto-increment (sort of)


  • Please log in to reply
1 reply to this topic

#1 michaellunsford

michaellunsford
  • Members
  • PipPipPip
  • Advanced Member
  • 1,023 posts
  • LocationLouisiana, USA

Posted 15 May 2006 - 03:53 PM

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:

<?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)."')");
?>

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?

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 16 May 2006 - 02:33 PM

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

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

or:

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

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users