multiple tables; one set of ids

emrys404

Hello,

I'm trying to figure out the best way of doing this:
I have multiple tables that need to have a consecutive order number. For instance, a customer purchases two products, their info for product A gets inputted into table product_A and their info for product B gets entered into table product_B. I want their order to have two separate Order Ids; so in this case product A gets assigned Order ID # 100 and product B gets assigned Order ID #101. I currently have it set up like this:

product_A {
id
orderid
etc...
}

product_B {
id
orderid
etc...
}

orders {
orderid
producttype
}

Where tables product_A and product_B both get their orderids from another table which divies out the order ids so they are unique. This works out of okay until i start to reference the data and i end up having to make two mysql calls: one to get the product type/table of the order id and then one to get the info from the product table. for example:

$result = mysql_query("SELECT type FROM orders WHERE orderid = '$orderid' ")
$tmp = mysql_fetch_assoc($result);

$result = mysql_query("SELECT * FROM ".$tmp['type']." WHERE orderid = '$orderid' ")

Can this be done more efficiently? Keep in mind the number of product tables will probably have no upper limit....

Thanks in advance,

-emrys

bradgrafelman

Why not just make one table like so:

products_ordered {
  order_id
  cust_id
  product_id
  product_type
}

instead of having multiple tables?

emrys404

Yeah, sorry for the misunderstanding. Instead of products they are services and the user supplies a bunch of info (talking about 30 fields or so per table) so each product/service has to be its own table.

Sorry for the confusion.

bradgrafelman

Ah, okay. In that case, I can't think of anything that doesn't involve two queries as you have now.

Unless you know for a fact that you will always use every single column in the table, the only efficiency gain I can recommend is specifying column names instead of your 'SELECT *' statement.