Creating a unique order number using auto increment.

[badvoc]

Hi,

I have had some good fortune on this site so I am back and I must iterate I am a beginer.

I am having some problems getting to grips with the right technique to manage variables and adding a unique order number to a customer order form.

I have 4 stages to my order form.

Stage 1 involves the user selecting a link based on what they want. The link then sets 2 variables. $type and $fault based on the link they have chosen.

In stage 2 I have used

[PHP]
<?php
$type = $_GET['$type'];
$fault = $_GET['$fault'];
?>

[/PHP]


and echo these details on the order form (in a read only input box to make sending them on easier, well for me anyway) and this works fine.

Would I still need to do this if I used sessions to handle my variables.

In stage 2 the user then has to fill in their details on a form and submit the from. I have used post as the action.

In stage 3 all the details are displayed correctly.

It is here where my problem really exists.

I want to add a unique order number, I have done this for the first order but cant get the order number to then auto increment. I have all the tables set up and am connecting to them fine.

The code for this is as follows

[PHP] <?php
$sql="SELECT * FROM i_counter"; // This table manages the incrementing counter. It contains 1 row called cnt.
$result=mysql_q uery($sql,$db);
$row = mysql_fetch_arr ay($result);
$cnt= $row["cnt"];
$cnt++;
$order_id="IPRD " . $cnt; //prefixes the order number with company ID

// Update counter for order id's

$sql = "UPDATE i_counter SET cnt = $cnt";
$result = mysql_query($sq l);

// Add a new temp database entry

$sql = "INSERT INTO i_orders (id, ordernumber) VALUES ('', '$order_id')";
$result = mysql_query($sq l);
?>
[/PHP]

The code is currently sitting at the start of my file just after 2 include statements, one for db connection and the other for the page header.

Though I will probably more on to sessions soon the issue I really need help with is the auto incremneting order number.

Many thanks

[badvoc]

[quote=badvoc]// Update counter for order id's

$sql = "UPDATE i_counter SET cnt = $cnt";
$result = mysql_query($sq l);

[/PHP]

I seem to have solved this by using insert into instead of update.

But thanks if you have stopped by to help.

[kovik]

Why would you use a separate table for a counter? Have you ever heard of AUTO_INCREMENT?

[pbmods]

Heya, Badvoc.

Glad to hear you got it working! Good luck with your project, and if you ever need anything, post back anytime :)

Please don't use the phrase 'Help!!' in your thread title in the future. It violates the, Posting Guidelines, makes more work for me and in general actually turns people *off* to your thread before they've even read it.

[badvoc]

Sorry about the title, I did try to add some detail but now see that it is annoying to see help in titles.

I'm sure I'll be back.

Thanks

[badvoc]

Why would you use a separate table for a counter? Have you ever heard of AUTO_INCREMENT?

Yeah I have the cnt set to auto incremant. i am using a seperate table as I couldn't get it to work so tried it as an option and it is still there.

I also want to store the actual order number which is prefixed with a 4 letters thus making it different to the id.

I suppose now its working I could just go back to the one table and use the id column and auto increment that..

Cheers

[kovik]

You could also use the regular table and get the latest id from that table, then continue your incrementing.

[pbmods]

Heya, Badvoc.

Consider separating your ID from your ID prefix.

For example, if you have to items with these IDs:

• ffr002
• mcv003

Consider separating the two parts:

• id=2, prefix=ffr
• id=3, prefix=mcv

You can easily format the 'human-readable' version of the id on the PHP side (or you can even index it as a separate field on the MySQL table itself). Plus, AUTO_INCREMENT just works, and you have a separate field dedicated to storing item prefixes, which makes searching faster.

You also may want to consider mapping a many-to-one relationship between the two data:

CREATE TABLE `items`( `itemid` INT ZEROFILL UNSIGNED ... AUTO_INCREMENT, `prefixid` INT UNSIGNED ... );
 
CREATE TABLE `prefixes`( `prefixid` INT UNSIGNED ... AUTO_INCREMENT, `prefix` CHAR(3) NOT NULL UNIQUE ... );
 

[badvoc]

After messing about with my design I have lost the section where I generate my order number so I am starting again and using the one table, as suggested, with an auto incrementing column to generate the ID. I then have an order_number column that stores the full order number.

I am unsure of how to call the last id used and add one to it to create the next value. I know about the auto increment feature but as I am not using that number directly as an order number I am unsure of how to do it.

The format for my order number is ABCD0000ID

ABCD are the initials to be placed in front of the every order number.
0000 are 4 zeros I added as I stumbled accross the code by chance and thought I would add it.
ID is the auto incremented number from the table.

The way I see it is that I have to get the last known value for ID then increase it by one and include the new ID when creating the order number and re submit the new ID to the table. Is there an easier way it can be done using the auto increment to better use.

Cheers

[badvoc]

Right I seem to have done it again. I have sort out the auto increment an am using 1 table.


The issue i have is with my 4 zeros. I want the order number to be made up of 4 letters and 6 numbers, so the 4 zeros are included in the number not just added to it as I have done.

[PHP] $order_id="IPRD " .sprintf("%04d" , $order_id) . $cnt;[/PHP]

This is what I have but am sure it was different to this yesterday.

Any ideas?

Thanks