SQL: Using max()

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
bla5e
Forum Contributor
Posts: 234
Joined: Tue May 25, 2004 4:28 pm

SQL: Using max()

Post by bla5e »

Code: Select all

 $position = pg_fetch_assoc(pg_query($db, "SELECT max(position) FROM bonuses_pubs_associated WHERE pub='msp'"));
 echo $position;
how do i get the greatest # of positions from a row, so that when i add a new record to the table, it will have the maxposition+1.
I cant use auto incremeent, so using this i thought would be better.. but i cant get it to echo anything.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: SQL: Using max()

Post by Weirdan »

bla5e wrote:I cant use auto incremeent

Why?
bla5e wrote:so using this i thought would be better..
No, this is far worse, because between the time you fetched max(position) from the database and inserted the row there could be another thread that managed to fetch + store another row with this position. As a net result you'll get duplicate positions (or errors if position is a unique key).
bla5e
Forum Contributor
Posts: 234
Joined: Tue May 25, 2004 4:28 pm

Re: SQL: Using max()

Post by bla5e »

Weirdan wrote:
bla5e wrote:I cant use auto incremeent

Why?
bla5e wrote:so using this i thought would be better..
No, this is far worse, because between the time you fetched max(position) from the database and inserted the row there could be another thread that managed to fetch + store another row with this position. As a net result you'll get duplicate positions (or errors if position is a unique key).
i have a check inplace for that, i just need this query to work
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: SQL: Using max()

Post by Eran »

pg_fetch_assoc() returns the entire row as an array. You need to extract the column from it.
bla5e
Forum Contributor
Posts: 234
Joined: Tue May 25, 2004 4:28 pm

Re: SQL: Using max()

Post by bla5e »

solved
Post Reply