Page 1 of 1

Mysql find highest value in table and + 1

Posted: Fri May 08, 2009 7:30 pm
by tomsace
Hey,
I am wanting to use this technique on my website to determine the game of the week, I am wanting to tick a checkbox on a certain game and it finds the highest value under 'gotw' and adds 1.
I can then list all previous games of the week. All games that have never been a game of the week will just have a default value of 0.
I have the database setup with 2 games of the week already, so I have loads of games with the value '0' under 'gotw' and 2 games with the values '1' and '2' so I would like to detect the highest value '2' and + 1 so it will update the row under 'gotw' with the value '3' :)

Any help will be much apprechiated, or just a general point in the right direction.
Thanks.

Re: Mysql find highest value in table and + 1

Posted: Fri May 08, 2009 7:51 pm
by requinix
So this number is supposed to keep track of the order these games were chosen?

Do it differently. Have the table keep track of whether it's been used yet. Like 0=has not, 1=has. Then have a second table where you enter each game as it gets chosen. The advantage is that MySQL will handle the numbering for you. All you do is enter the game number/identifier and MySQL will figure out the next number in the sequence.

Re: Mysql find highest value in table and + 1

Posted: Fri May 08, 2009 7:53 pm
by mdk999
I'm not sure what you are trying to do here but here is what I can offer, to find the highest value in a field you can do ..

Code: Select all

$sql = "select max(`points`) from games";


to update some field with max +1

Code: Select all

$sql = "update `games` set `points` = max(`points`)+1";
to find the row with the highest points it's ..

Code: Select all

$sql = "select * from `games` order by `points` DESC limit 1";
Hope that helps ..