Hello - Problem with mysql MAX

[panais]

Hello All,

I Develop a small e-shop and i am facing a problem with invoice number

I attach to an order the invoice number after client has successfully paid the order through Paypal, so i use the function below to retrieve the next inv num

 

function GET_NEXT_INV_NUM() {

 

$query= "SELECT MAX(inv_num) AS max_inv_num FROM orders";

$rs_query = mysql_query($query, $db) or die(mysql_error());

$row_query = mysql_fetch_assoc($rs_ws_mod_tvg_program_rec);

 

$Next_inv_num = $row_query['max_inv_num']+1;

 

return $Next_inv_num;

}

 

all looked to work fine but one day i realised that i have some invoice numbers that are missing, because in some cases instead of next inv to increase by one, is increased by two

 

for example:

 

INV NUMBERS:

============

101

102

103 --->PROBLEM HERE (MISSING 104)

105

106

107

108

109

110 --->PROBLEM HERE (MISSING 111)

112

113

114

115

116

...

 

I check all of my code 3-4 times and all looks ok

Can anyone help me please???

 

Thanks in advance

(i apologise for my english)

[shlumph]

You could make the invoice number auto increment via MySQL table settings, then you won't have to worry about having a function handle it.

[Muddy_Funster]

instead of

$query= "SELECT MAX(inv_num) AS max_inv_num FROM orders"

you could use

$query= "SELECT inv_num AS max_inv_num FROM orders ORDER BY inv_num DESC LIMIT 1"

 

And please use code/php tags for posting up any code

[PFMaBiSmAd]

You cannot (successfully) get the current maximum value and add one to it (unless you are willing to lock the table), because if there are concurrent visitors causing the same thing to happen, you will get wrong results (you will end up with two visitors using the same number.)

 

Do what shlumph has suggested and use an auto-increment field in your table.

[panais]

I can't use auto-increment to inv number field. I update that field when the transaction is completed succesfully. The record (order) is already created

 

What about a trigger? Can a trigger solve my problem?

 

Thanks for the quick responses