How to index a DB row with the same value

[chrima]

Hi all,
I currently have a table named orders in a mySQL DB. The table has a column named order_id. Here is the table structure:

CREATE TABLE orders (
  order_id int(10) unsigned NOT NULL auto_increment,
  customer_id int(10) NOT NULL default '0',
  movie_id varchar(5) NOT NULL default '',
  movie_title varchar(50) NOT NULL default '',
  movie_current_price varchar(10) NOT NULL default '',
  qty char(3) NOT NULL default '',
  order_date date NOT NULL default '0000-00-00',
  processed char(1) NOT NULL default '',
  PRIMARY KEY  (order_id),
  UNIQUE KEY order_id (order_id)
) TYPE=MyISAM;

At the moment when i add an order it obviously auto indexes the order_id row with a unique number.
Now for the question. What if a person orders two or more items. Currently the order gets added with a new table row even if the order is from the same client on the same day.
Here is the script that adds it to the DB:

$result_2 = mysql_query("insert into orders(order_id, customer_id, movie_id, movie_title, movie_current_price, qty, order_date) 
values ('NULL', '" . $_SESSION['customer_id'] . "', '$movie_id', '$movie_title', '$movie_current_price', '$qty', '" . date('Ymd') . "')");

What do i need to add to my order table or script to allow the order_id column to insert the same order_id if the client has ordered at that same time.

[Mordecai]

You can't use auto_increment on a primary key for this. Instead, you'd have to just inc an INT via PHP. (Ooh, count the <=3 letter words!)

[laserlight]

The solution would be to add a column for the current time or current date.

Then, to obtain the orders made by the same client on the same day, you simply select based on customer_id and on the given date

[trooper]

Another alternative is to hold all the products in a session and then when the person adds an item simply add all the the items details in the "cart" array in the session. Then as the user merrily wanders around they will fill up their "cart" array in the session.

Upon "checkout" simply write all that information (that is in the session) into the db (remembering to serialize the array that contains all the session "cart" array details). That will then allow you to have 1 record per user as opposed to record per item per user

If i've explained it badly then i'll try again

HTH

GM