Increasing Number Generator..

[SephirGaine]

Well, here I am again. I've gotten tons and tons of help from this forum thus far, but once again I find myself stumped.

Here's my situation. I've got a Work Order Request form up and working with everything except for something that can generate a Work Order number. I figure the simplest way to begin doing this is to use a script that starts with 1, and each time that the "Submit" button is used (and the script is ran successfully), it increases that number by 1, and then displays it on the final product.

Hope it makes sense. I figure it would be easiest to do within PHP.. however I'm not too sure what other language would be possible. Maybe HTML, though I'm not positive. Thanks for any help in advance.

[ukgeoff]

You can't do what I think you are thinking because there is no comunication, as such, between one works order and the next.

You need to store the number in a database or in a flat file on your server.

Each time a works oreder is generated, you extract the stored number, add one and use it and return the new number to the store for next time.

You can devise a suitable number for example by preceding the 'generated' part by fixed characters, e.g.,

ORDER1234

[SephirGaine]

Alright, I gotcha. So, easiest way is to create a new table named, say, "WORNum". (Word Order Request Number).

From there I can recall that number, (somehow) add 1 to it, assign a variable to the new number, and then store the variable. Two questions for ya then..

First one, is how would I go about increasing a number? It sounds VERY simple, however I haven't had to do anything like this. Second, instead of using the INSERT command in my sql query, is there something I can use that will replace a current entry?

[ukgeoff]

Here are some concepts you need to incorporate into your code. I have not given you full detail just so you can paste and copy, otherwise you never learn. Use of php assumed.

$num = fetch from database;

$newnum = $num + 1;

use SQL command 'update' to return new value to database;

$ordnum = 'WORN'.$newnum;

Let us know how you get on.

[mtgmaster]

EDIT: Read below.

You should be able to do it all in one query:

Code: Select all

mysql_query("UPDATE WORNum SET num=num+1 WHERE id='1' ");

Might work, cant really think of it off the top of my head. Just have the table with 2 fields, id and num.

[guelphdad]

You should absolutely not do the above suggestions if you are using a database. If you are using mysql then you should use an auto_increment column. You would use last_insert_id() to fetch the last row inserted into that column.

If you are using a database other than mysql then you should look into sequences.

If you were to do the above suggestions then you would have to guarantee you were always the only user of the database, otherwise you could insert a number and before you insert it or update it another user could use the same value to update a table. Your value would then be wrong. Auto_increment and sequences avoid this problem.

[mtgmaster]

Originally Posted by guelphdad

You should absolutely not do the above suggestions if you are using a database. If you are using mysql then you should use an auto_increment column. You would use last_insert_id() to fetch the last row inserted into that column.

If you are using a database other than mysql then you should look into sequences.

If you were to do the above suggestions then you would have to guarantee you were always the only user of the database, otherwise you could insert a number and before you insert it or update it another user could use the same value to update a table. Your value would then be wrong. Auto_increment and sequences avoid this problem.

Never knew about that, havent even heard of last_insert_id(). Im gonna look it up, could be useful. I would of thought it would do the query too fast for things like that to happen.

So OP, disregard my suggestion if you aren't the only person using the site.

Originally Posted by guelphdad

If you are using mysql then you should use an auto_increment column. You would use last_insert_id() to fetch the last row inserted into that column.

Do you mean use the auto_increment column so when a new row is inserted it has a the next ID up? Because I didnt think he was inserting a new row each time.

[ukgeoff]

Originally Posted by guelphdad

You should absolutely not do the above suggestions if you are using a database. If you are using mysql then you should use an auto_increment column. You would use last_insert_id() to fetch the last row inserted into that column.

If you are using a database other than mysql then you should look into sequences.

If you were to do the above suggestions then you would have to guarantee you were always the only user of the database, otherwise you could insert a number and before you insert it or update it another user could use the same value to update a table. Your value would then be wrong. Auto_increment and sequences avoid this problem.

Whilst these are valid comments in the right cercumstances, I think you have misunderstood the requirements in this case and might be confusing the originator of the thread.

The originator of the thread is not looking, or so I assumed, to insert any new rows. They just want a record of the last number they used.

If SephirGaine would like to elaborate on their requirements then the suggestions might need to be reviewed.

[SephirGaine]

Alright, I went with the auto_increment column for this. Seems to work just fine, however is there any way to "reset" the numbers? Even if I delete the entries inside of my database (say, word order #6), the next one I generate will go back up to 7. Unfortunately this kind of makes testing rough, since we've already got 3 work orders in there, however if I delete the column and re-create it, my script seems to get messed up like no other. Kind of confusing..

But thanks for all the help. I ended up running this in mysql..

ALTER TABLE WOR ADD work_order_num INT UNSIGNED AUTO_INCREMENT, ADD PRIMARY KEY(work_order_num)

So the work_order_num is the row that contains the numbers. So far so good, no real complaints with it.

[ukgeoff]

If you are going to be working with databases regularly is suggest you get yourself a tool like SQLyog.

You will be amazed at how easy it is to construct and manage databases including deleting all your entries so far and auto increments starting from scratch again.

[SephirGaine]

I'll have to do so. Right now I'm using a browser-based phpMyadmin, which is provided by my hosting service.. works alright so far. And I found that just deleting the row and then re-making it works just fine.

[masonbarge]

Don't use your work order number as the primary key. You really shouldn't do it, anyway, as you are talking about two different concepts. Start the table with a primary key that has no other purpose and index the work order number column as "unique". If nothing else, your logs will be easier to decipher.

Have you thought about having a boolean "void" column, the way you would in a checkbook, so that you can have work order numbers with no work order?

[masonbarge]

Gah, sorry, you want to use auto-increment for the work orders, don't you? There are problems trying to use a second auto-increment column. I think it can be done in InnoDB but there might be complications. I just don't know, sorry.

My first try, I guess, would be to use the primary key for the work order number and have a boolean "void" column to indicate numbers without a work order. I really don't think I'd start trying to empty rows for retracted work orders.