Need Suggestions

I need some suggestions on how to complete a tasks in an application
I
am working on.

I have an order form that several users will be using on-line, which
dumps data into my MySQL Database.


I need to pull the next order number from the order table, which I
can
do successfully.


My problem is when more than one user is entering orders and they
save
the order there are then two records with the same order number in
the
system.


It doesn't duplicate the record since I have a unique field called
RecordID which is generated by the database.


So how can I insert the order number into the database when they open
the order form so if some else opens the form they get the next
number. I know I will proably run into problems with the numbering
if
they cancel the order and someone else as already created a new
one...but I don't think they will really care about that.


I have this code inserted into my order page:


-------------------CODE STARTS HERE-----------------------------
$query = "SELECT max(OrderNum) + 1 as OrderNum FROM
neworders";
if ($r = mysql_query($query))
{
while ($row = mysql_fetch_array($r))
{
$OrderNum = $row['OrderNum'];
echo $OrderNum;


}
}


$sql = "INSERT INTO neworders (OrderNum) VALUES ($OrderNum)
";
if (@mysql_query($sql))


mysql_close();
-------------------CODE ENDS HERE---------------------------


Which almost does what I want, except everytime the page is refresed
it pulls a new number, or when the push the next button on the form
it
pulls the next number and udpates all the information for that order
to that number, so then I have an empty order in the table.


Any help or suggestions would be greatly appreciated.


Thanks for all your help.


~John

why are you trying to get the order number BEFORE you submit the
order?

If you are using an auto incrementing field this is not necesarry (as
it will auto-increment ;-) ) when you add a new record!
which you could then extract with the last insert id function.


But if you really have to do it this way, maybe you should consider
locking the table?

John wrote:
> I need some suggestions on how to complete a tasks in an application
> I
> am working on.
>
> I have an order form that several users will be using on-line, which
> dumps data into my MySQL Database.
>
>
> I need to pull the next order number from the order table, which I
> can
> do successfully.
>
>
> My problem is when more than one user is entering orders and they
> save
> the order there are then two records with the same order number in
> the
> system.
>
>
> It doesn't duplicate the record since I have a unique field called
> RecordID which is generated by the database.
>
>
> So how can I insert the order number into the database when they open
> the order form so if some else opens the form they get the next
> number. I know I will proably run into problems with the numbering
> if
> they cancel the order and someone else as already created a new
> one...but I don't think they will really care about that.
>
>
> I have this code inserted into my order page:
>
>
> -------------------CODE STARTS HERE-----------------------------
> $query = "SELECT max(OrderNum) + 1 as OrderNum FROM
> neworders";
> if ($r = mysql_query($query))
> {
> while ($row = mysql_fetch_array($r))
> {
> $OrderNum = $row['OrderNum'];
> echo $OrderNum;
>
>
> }
> }
>
>
> $sql = "INSERT INTO neworders (OrderNum) VALUES ($OrderNum)
> ";
> if (@mysql_query($sql))
>
>
> mysql_close();
> -------------------CODE ENDS HERE---------------------------
>
>
> Which almost does what I want, except everytime the page is refresed
> it pulls a new number, or when the push the next button on the form
> it
> pulls the next number and udpates all the information for that order
> to that number, so then I have an empty order in the table.
>
>
> Any help or suggestions would be greatly appreciated.
>
>
> Thanks for all your help.
>
>
> ~John
>

I would suggest that you use the 'RecordId' as the record number
using a command like sprintf or such to format it to your/the users
liking. Once the order is created, get the RecordId via the
last_insert_id command and store it in a session variable so that all
changes are done to the correct record. Something like:

1) user creates/retrieves record
2) get the last_insert_id (before the script ends) and store it in
$_SESSION['RecordId']
3) user makes changes/etc.
4) if $_SESSION['RecordId'] is set (not NULL or not 0 for example) then
perform an UPDATE
5) else perform an INSERT (for a new record)

....use the LAST_INSERT_ID() function from within an SQL statement, not
the mysq_last_insert_id() command as it may not return the proper data
under some circumstances.

$sql = "select LAST_INSERT_ID()";

Norm