Autogenerate ID code continuing from last DB entry but not writting to it

chet139 · February 23, 2008

I have an order page for which I want the user to be able to generate an orderid for a particular order before starting to add items to the order.

Now this is not a customer system and is intended to be used only by internal staff.

Basically all I am after is a button, which once the user presses it should supply an ordernumber BUT which is not already in the database. So if 1,2,3,4 are in already in 5 should be returned, but not added to the DB. I only want to use it as a variable for now.

If this does not make sense please ask..... I am new to this LOL....

fooDigi · February 23, 2008

i think i understand. i deal with very similar things. but what if, while that internal user is using that id, someone else comes in and gets the same one generated for them. how would you handle that? sorry, kinda besides the point

fooDigi · February 23, 2008

i use a tmp table with a tmp order id, which is imported to the main order table that has a main orderid, but also a separate field that can reference the tmp order id, if needed. im actually curious of a better way myself.

Bauer418 · February 23, 2008

What's wrong with something simple, such as:

SELECT MAX(order_id) + 1 FROM orders

Which will take the highest order ID available, add one, and use it.

chet139 · February 23, 2008

Thanks for the responses.

Firstly for the purpose of this project there is no issues of multiusers, but certainly a valid point. Thank you.

Bauer418 - your idea is exactly what I need. But being new to this I am at a lost of how to put that into code - Though it is likely quite simple. I would run the query which in mycase would be:

SELECT MAX (orderId) + 1 from order

But from that all I would want is the orderId+1 result stored in some var as I would not be adding to the DB until later on......can you help?

Thanks!

Bauer418 · February 23, 2008

<?php
$query = "SELECT MAX (orderId) + 1 AS newOrderId FROM order";
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_assoc($result);
$new_orderid = $row['newOrderId'];
?>

$new_orderid now contains your new order id.

chet139 · February 23, 2008

Yes I think thats it.

Further to this, if the DB is empty so no orderId is there, what would happen? Would it fall over? or just set the new_orderId to '1'?

Thanks again

Bauer418 · February 23, 2008

New orderID should be set to 1 in that case.

chet139 · February 23, 2008

It does not work, I have had to make a few naming changes and i realised its ordId not orderId - but thats not the problem.

$query = "SELECT MAX (ordId) + 1 AS newOrderId FROM order";// query to generate new order ID
	$res = mysql_query($query) or die(mysql_error());
	$row = mysql_fetch_assoc($res);
	$new_orderid = $row['newOrderId'];

then to use the new var i have used it as so..

Order ID:</b><br/> <input type ="text" value ="<?php $new_orderid?>" name="orderId">

but upon load this page (its all on one page the following error is return)

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order' at line 1

NOTE: on line 1 there is nothing apart from "<html>".

I tried running the query from mysql query browser and it does not work there either....

Bauer418 · February 23, 2008

It's because "order" (your MySQL table name) is also a MySQL keyword. Enclose it in back ticks, so it looks like: `order`

chet139 · February 23, 2008

Nope still no joy

tried

$query = "SELECT MAX (ordId) + 1 AS newOrderId FROM 'order'";// query to generate new order ID
	$res = mysql_query($query) or die(mysql_error());
	$row = mysql_fetch_assoc($res);
	$new_orderid = $row['newOrderId'];

Gives same error.

Also tried but with no joy -

$query = "SELECT MAX (ordId) + 1 AS newOrderId FROM furniture.order";// query to generate new order ID
	$res = mysql_query($query) or die(mysql_error());
	$row = mysql_fetch_assoc($res);
	$new_orderid = $row['newOrderId'];

But gives following error:

FUNCTION furniture.MAX does not exist

Bauer418 · February 23, 2008

You placed standard ticks. You need to use back ticks. It's the key to the left of the number 1, underneath escape.

chet139 · February 23, 2008

SELECT MAX (ordId) + 1 AS newOrderId FROM `order`

Same error is returned:

FUNCTION furniture.MAX does not exist

Even in query browser aswell as page.

I tried it with my customer table and it seems that it does not like 'MAX'..but I dont know.

Bauer418 · February 23, 2008

Take the space out between the word MAX and the parenthesis, it should say MAX(ordId) not MAX (ordId). Makes a huge difference in SQL.

mem0ri · February 23, 2008

Two possible problems:

1--Your MySQL doesn't like the space you're including between MAX and (ordid)...so try MAX(ordid)...yes, it's ridiculous, but I've seen more ridiculous things.

2--Your version of MySQL does not support the MAX function (which is what the error seems to be saying). At that point, you could always do something like:

SELECT id FROM order ORDER BY id DESC LIMIT 1[code=php:0]

...which will give you the highest order number on the table. Then...just add 1 before display.

chet139 · February 23, 2008

Great,

It worked it didnt like the space!.

Thanks for that you two were both good help thanks again!

[solved]

chet139 · February 28, 2008

Further to this,

I cleared by whole DB table and it transpired that once the DB is empty the orderId is not autoset to 1.

Any help appreciated the code is as below.

Like I said works fine accept it wont generate new ID if DB is empty.

                $query = "SELECT MAX(custId) + 1 AS newcustId FROM `customer`";// query to generate new order ID
	$res = mysql_query($query) or die(mysql_error());
	$row = mysql_fetch_assoc($res);
	$new_custid = $row['newcustId'];

chet139 · February 28, 2008

*bump*

Dont want it getting buried

Bauer418 · February 28, 2008

At the end of the code you posted above you can simply add:

if (!$new_custid || !is_integer($new_custid))
{
     $new_custid = 0;
}

premiso · February 28, 2008

Ok, so I skimmed over this and sorry if it is totally off topic but here it goes:

To me the database designed is flawed. Usually with a cart system you have two tables for orders, "orders" which contains the order information IE who, when, where email etc. The second table, which uses the orderid from the "orders" table is called "orderitems" which contains an orderitemid, orderid, itemid. From there you can create order ids and then dynamically add items to the order by simply using that id, and removing items in a similar fashion. I am sure there was a tutorial on who to set it up in depth, but what you are essentially doing with the logic I put above is making the database in 3rd Normal Form, which is the standard for a database to be in and the preferred/efficient method.

Again sorry if I mis-read this completely.

chet139 · February 29, 2008

Thanks for your input again Bauser I will give that a try later today

The database design is not flawed and is normalised.

An order can have many products and a product can be on many orders. Therefore you have a many to many relationship. IN terms of long term storage, with regards to the order, there is an order table which holds no product details of an order ! as that would not be a good idea!

So an orderline table will be created.

Thanks

chet139 · February 29, 2008

Ok,

So I used

if (!$new_orderid || !is_integer($new_orderid))
	{
		$new_orderid = 1;
	}

(Excuse the change of Var names)

Instead of 0 i wanted 1 so i changed it. It works however once a record has been added with ID of 1, the code still returns 1 as next ID.

The code I have now is below:

                $query = "SELECT MAX(ordId) + 1 AS newOrderId FROM `order`";// query to generate new order ID
	$res = mysql_query($query) or die(mysql_error());
	$row = mysql_fetch_assoc($res);
	$new_orderid = $row['newOrderId'];
	if (!$new_orderid || !is_integer($new_orderid))
	{
		$new_orderid = 1;
	}

chet139 · February 29, 2008

*bump*

chet139 · March 1, 2008

*bump

Bauer418 · March 2, 2008

$query = "SELECT MAX(ordId) + 1 AS newOrderId FROM `order`";// query to generate new order ID
$res = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_assoc($res);
$new_orderid = $row['newOrderId'] ? $row['newOrderId'] : 1;

That should do it...

Sign In

Autogenerate ID code continuing from last DB entry but not writting to it

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Archived

Important Information