grab info from field names without knowing field names

DKY

I just can't wrap my brain around this. I have a database table that has product names and product ids.

I have another table that has orders and each product id has a field as well as a username field.

I'm trying to display product names (from my tblproducts) that have quantities associated with the product ids (form the tblorders) per a given username. I can't figure out how to do this. I'm stuck with just a connection and a select query. What next?

	$SavedOrdersQuery = "SELECT * FROM tblorders where username = '$UserName' ORDER BY OrderDate DESC LIMIT 5";
	$SavedOrdersResult = mysql_query($SavedOrdersQuery) or die(mysql_error().'<br />');
	while ($Row = mysql_fetch_assoc($SavedOrdersResult))
		{
}

niroshan

Hi DKY,

If I got your question correctly, are you trying to display an order details for an user right. Well if i were you i would have another intermediate table called 'orderDetails'. Why? because one order can have many products so if i put all the product details into the order table it might repeat the unwanted data which was specific to order (ex: order date, userId, total ....etc)

so first i would have a table structure as, (Please remember i have mentioned here only the fields which is very important to this example. actual table should have more fields (details))

products

id
name

orders

id
userId
orderDate
total

orderDetails

id
orderId
productId
qty
unitPrice

now any time if you want to list orders for a particular user you can look into 'orders' table.

and as for your original question if you want to list products for a particular user and for a particular order you needs to do is join the tables,

select a.productId, c.name, a.qty from orderDetails a left join orders b on a.orderId = b.id join products c on a.productId = c.id where b.userId = <userId> and b.id=<orderId>

Hope this help.

Best regards,
Niroshan

DKY

Thanks for your response niroshan. So, here's what I have (tablewise):

tblproducts
--categoryname
--productname
--ID

tblorders
--OrderID
--username
--Address
--City
--State
--Zip
--Comments
--AdditionalProducts
--44
--36
--21
--13
--43
......

Here's what happens, I've created a back-end so that the owner/operator of the site can add/remove categories and the products within the categories so every time a product is created it generates an ID in the tblproducts table that is propogated to the tblorders table to create a new field as that number, so the 44, 36, 21, 13, 43 are all associated with the ID field in the tblproducts.

Please let me know your thoughts so far on the structure of what I've got going vs. what you're suggesting as I'm open to learning a more efficient table structure technique.

With that said, I'm trying to do a select on the tblorders where for a particular OrderID if the value is not zero for a for each field then display it. Since the products may be added or removed at any time the code would need to be versatile enough that I don't have to hard-code every field for display.

niroshan

Hi DKY,

By looking at your table structure i have some suggestions of my own,

tblproducts

Never use names as a foreign key (categoryname) instead try to create a separate table for categories and link it via id. ex:

categories
- id
- name

products
- id
- productname
- categoryId

tblorders

so every time a product is created it generates an ID in the tblproducts table that is propogated to the tblorders table to create a new field as that number

you mean every time you create a product, you goto tblorders and manually create a field there??😕 why?

please refer to the sample table structure i have given you before.

what you needs to do is when ever user order some products first add the order details to the orders table. so any given time you have all the order details for your reporting. after inserting order details insert all the related product details to the orderDetails table. use order Id (primary key) to refer to orderDetails table (orderId - foreign key).

In this scenario its safe to use username as well since its unique. but always try to use IDs as reference.

Hope this helps,

Regards,
Niroshan

DKY

you mean every time you create a product, you goto tblorders and manually create a field there?? why?

I do this so that every time someone places an order the fields are populated. I also want to be able to save these orders for the customer to just come back and click on their saved order to place the order again. My thinking was that the only way for them to do that was to be able to have a field for each productID with the quantity that they ordered in it.
I can't comprehend how to do it any other way or that there would be another way. Still learning I guess.

In your example would I recreate the fields for each product ID in the orderDetails table? If not how would I recall the quantity for each product that they ordered?

FYI, this is just a quantity and product name type of thing, no price, credit cards, etc... it sends a simple email to the owner and he does all the rest on the side.

niroshan

Hi DKY,

I do this so that every time someone places an order the fields are populated

Imagine you have 1000 products.. are you going to create 1000 extra fields?? 😕
Like I said before if you do this then the whole point of a relational database is lost.

Lets get the objectives of yours first.

System can have any no of products.
Users can purchase any product with any quantity
user should be able to re-order the same order later on.

so solving part 🙂

System can have any no of products.

you can simply have a table called products with following fields.

products

id
name

so each time you add a new product a new record will be added to the table. REMEMBER ITS A RECORD NOT A FIELD. make sure you specify the id field as a auto number so when ever you add a product the table will generate an auto number for that product.

Users can purchase any product with any quantity

To simplify the process i recommend to have 2 tables as,

orders

id --------- auto number (primary key)
userId
orderDate

orderDetails

id --------- auto number (primary key)
orderId ----- (foreign key)
productId ----- (foreign key)
qty
unitPrice *

you can remove the unitPrice since you don`t want to store the prices. so when ever user place an order,

a) insert userId, date to orders table.
b) get the last insert id from orders table.
c) insert the product details one by one to the orderDetails table. make sure to use last insert id as orderId.

so now we have all the order details.

user should be able to re-order the same order later on.

This is simple. Since we have order details stored in our table for that perticular user all you need to do is search for all the orders place by that user in orders table using userID and get the details of that order from orderDetails table using orderId.

Hope this is clear for you.

Thanks,
best regards,
Niroshan

DKY

Oh, I get it! I thought it would be faster to go through the orderdetails table with the order id being the primary key and only finding one row then I did if it found several rows for some reason...

But when you split it up into two tables like that it seems to make more sense. I have a lot of work to do to re-work this so I'll get started on it right away. Since we're in different time zones I might have another post if I run into issues so I'll keep you updated, thanks!