Getting the highest value
Posted: Mon Sep 27, 2004 3:01 pm
Hi all,
here's my problem: I need to insert a record to the database. There is an `id` with auto_increment but there's also a `key` column. That can be filled by user but what i need is - if user doesn't entry anything, then I want `key` to be {highest value of `key` in whole table + 1}. I could do this via PHP query (getting the highest value) but is there a way how could I get the highest value directly in the MySQL query? Like this:
And if I'd have to do it through php: Isn't there a risk that someone will use this highest key before me? Example:
Thx
here's my problem: I need to insert a record to the database. There is an `id` with auto_increment but there's also a `key` column. That can be filled by user but what i need is - if user doesn't entry anything, then I want `key` to be {highest value of `key` in whole table + 1}. I could do this via PHP query (getting the highest value) but is there a way how could I get the highest value directly in the MySQL query? Like this:
Code: Select all
INSERT INTO table (key, text) VALUES (highest_number() + 1, 'Some text')Code: Select all
<?php
$sql = "SELECT key FROM table ORDER BY key DESC LIMIT 1"; // get the highest `key` in the table
$result = mysql_query($sql) or Die("Error<br>\r\n".mysql_error()."<br>\r\n$sql");
$row = MySQL_Fetch_Row($result);
$key = $row[0];
//
// Someone else stores something in the db with the same `key`
//
$sql = "INSERT INTO table (key, text) VALUES ($key, 'Some text');"; // key will NOT be unique now
$result = mysql_query($sql) or Die("Error<br>\r\n".mysql_error()."<br>\r\n$sql");
?>