Professional Web Applications Themes

Need Suggestions - MySQL

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 ...

  1. #1

    Default 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

    John Guest

  2. #2

    Default Re: Need Suggestions

    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?

    macca Guest

  3. #3

    Default Re: Need Suggestions

    John wrote: 

    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 cirstances.

    $sql = "select LAST_INSERT_ID()";

    Norm
    Norman Guest

Similar Threads

  1. need suggestions+help
    By GilesW in forum Macromedia Flash Sitedesign
    Replies: 0
    Last Post: April 20th, 10:09 AM
  2. Need suggestions on where to get help
    By TurboDuster in forum ASP
    Replies: 8
    Last Post: August 29th, 01:19 PM
  3. Any Suggestions?
    By Hello123 in forum Adobe Photoshop 7, CS, CS2 & CS3
    Replies: 8
    Last Post: July 25th, 07:35 PM
  4. Any suggestions??
    By jln32 in forum Adobe Photoshop 7, CS, CS2 & CS3
    Replies: 18
    Last Post: July 11th, 08:07 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139