Page 1 of 1

Getting the highest value

Posted: Mon Sep 27, 2004 3:01 pm
by MarK (CZ)
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:

Code: Select all

INSERT INTO table (key, text) VALUES (highest_number() + 1, 'Some text')
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:

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");

?>
Thx

Posted: Mon Sep 27, 2004 3:08 pm
by timvw
the easiest is to use the AUTOINCREMENT option.

to get the maximum of something

Code: Select all

SELECT MAX(column)
FROM foo
If you use transactions (or something to fake it like row locking) a set of queries will be handled as if were 1 query. (and thus help you get round the concurrency problems)

Posted: Mon Sep 27, 2004 3:09 pm
by feyd
unless your version of mysql supports sub-queries, I don't remember a way to do it without 2 queries. Although I may be in a mind-fog :P

Posted: Mon Sep 27, 2004 3:33 pm
by MarK (CZ)
timvw wrote:the easiest is to use the AUTOINCREMENT option.
I cannot use that as I already use auto_increment on `id`. And I need `key` to be set by user in some cases.

Posted: Mon Sep 27, 2004 5:52 pm
by hedge

Code: Select all

insert into table (key, text)
select max(key)+1, 'Some Text' from dual;
That's Oracle syntax, dual is just a pseudo-table with a single row, you could create a dummy table to accomplish the same thing.

Posted: Mon Sep 27, 2004 5:55 pm
by feyd
a dummy table for 1 value is kinda a waste to me.. he's using mysql btw. :)

Posted: Wed Sep 29, 2004 8:33 am
by MarK (CZ)
hedge wrote:

Code: Select all

insert into table (key, text)
select max(key)+1, 'Some Text' from dual;
That's Oracle syntax, dual is just a pseudo-table with a single row, you could create a dummy table to accomplish the same thing.
Well, I'm running it without another table and I guess it works as it should.

Code: Select all

$sql = "INSERT INTO news (`key`) ".
       "SELECT MAX(`key`)+1 FROM news";