Page 1 of 1

SQL: Using max()

Posted: Wed May 19, 2010 11:59 am
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.

Re: SQL: Using max()

Posted: Wed May 19, 2010 12:04 pm
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).

Re: SQL: Using max()

Posted: Wed May 19, 2010 12:34 pm
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

Re: SQL: Using max()

Posted: Wed May 19, 2010 12:36 pm
by Eran
pg_fetch_assoc() returns the entire row as an array. You need to extract the column from it.

Re: SQL: Using max()

Posted: Wed May 19, 2010 12:43 pm
by bla5e
solved