Add details from items table on order history page.

Before i put the site (which is very nearly complete) online i need to accomplish a couple of things. I'll begin with my first dilemma which is to do with the order history page. Basically i need to make some of the details from the items table available for the customer to view on there history page (only the items they've ordered that is)

I created the history and detail page using the DAssist wizard as described in the tutorial. I'm now thinking the best way to achieve the desired result is to make the orderID link to an items page rather than the details page. I've done this, i've created the items page with DAssist and changed the link. At the moment when you click on the link all items from the items table appear but obviously i need only the items ordered by the user to appear. I've tried altering the SQL statement and variables on the 2 pages to get this to work but i'm hopeless with complex statements so i could do with some help. Can you tell me the correct statements/variables needed.

It maybe the case that all 3 pages are required for this an to work, an order history page, details page and an items page. If this is the case thats fine as long as the items info from the items table can be viewed somewhere, thats the main thing. pls advise me. Thanks

This post has been deleted.

I think i have the answer, i'm just not sure how to implement it so i'll still need a bit of help.
I've deleted the history and details pages for the time being as i think i may need to add some extra columns in the orderdetails database table to accommodate further orderdetails from the items table. I'll use DataAssist to recreate the history pages later.

At the moment, when you add an item to the cart it includes the name and description, details which are taken from the items table and on checkout get added to the orderdetails table. They can then show up on the order details history page. I need data from some other columns in the items table to get added to the orderdetails table but without showing up in the shoppingcart. They can be there somewhere as long as the customer can't actually see this information i.e.hidden. They will only be able to view the details in the history area after payment has been made.

I realise that a remote checkout isn't ideal for this but i'm going to keep it like this at least until i know the site can cover the monthly paypal fee. Anyway, do you think the above is possible? I know there is a full description option with ecart4 so i may need to select this in order to get the extra item details. Once i've added new columns to the database, will i be able to go in and select the full description option from the relevant server behaviour or will i need to delete my cart pages and run the ecart wizard all over again and lastly can i make the name and description visible in the cart but have the rest of the data from items invisible to the customer. If its not possible to hide the extra order details from the customer so that its only available in the history area, i'll have to think of another solution. pls advise. thanks.

Pass the id from the items table into the cart and store that in the order details table, you can use that to pull any extra information you need by joining the order details table to the items table in your sql statement.

Thanks. Sounds like a good plan although i'm not sure i'd be able to pull this off without some major guidance. I did attempt a join but without much success. I think my best bet is to add some extra columns in the orderdetails table, give them similar names to the columns in the items table and then bind the data in the store details behaviour. i think this will work. i may have to run the ecart wizard again though in order to pick up the newly inserted columns. I realise now that i don't need to do anything with the full description option in the ecart object as this will show up in the cart which is not what i want. i think i might wait until tomorrow though before i make a start on this, just in case someone advises me not to do it. Thanks again.

A simple example of the code needed to join the orderdetails and items tables (assuming that you have based your database on the bkue sky music example and that you are storing ItemID in the orderdetails table as DetailItemID) would be:

SELECT orderdetails.DetailItemName, orderdetails.DetailItemDesc, items.ItemThumb FROM orderdetails JOIN items WHERE (orderdetails.DetailItemID = items.ItemID)

You can expand this to include other fields and tables as necessary. For more info on SQL to get you started see default.asp

Thanks for this. This statement worked so far as allowing me to insert items from the items table (ie. ItemThumb1) onto the history details page however, if i look at order 26, for an order of 3 different items it lists the same itemthumb 3 times as opposed to listing the three items that were actually ordered (which should all be different), maybe some sort of parameter or variable is needed so that it lists only the itemthumbs for that particular order. Is that right or should it work without any extra bits.

In addition to the above, i'm hoping to use data assist to create a searchpage for seaching the orderID from the orders table and a results page with images and details from the items table. so if i search order 26 it would bring up 3 different images and the relevant details from the items table would appear below the image.

I've already tried this but had the same problem as above. At the beginning of the wizard i chose the orderdetails table, i deleted all items to search on except orderID. When i tried out a search it showed the name and description correctly as these items are actually in the orderdetails table. But the details from the items table below each image were all the same, i figured the easiest way to get it to work properly would be to add new columns in the orderdetails table next to name and description but maybe it can be done by creating a join statement like the one you provided for the history details page?

I should probably try to get the history pages sorted first and if i can't, i guess i'll have to revert back to my original plan. pls advise.

Perhaps i'm putting the new recordset with the items join statement on the wrong page.
I placed it on the user_order_details page and not the user_order_history Page. Is this right?

Interestingly when i click on the recordset containing the new join statement and press test all the correct
items are in the itemthumbs column. Why they aren't going on the history details page like this is a mystery?

I've tried out my original idea of adding an items recordset on the confirm.php page and then binding one of the columns from the items table in the order details summary behaviour but i get the same result as with the join statement. The order details history page shows the same item on every row regardless as to what was ordered. There must be a way around this. If you can get the item 'name and description' to appear in the orderdetails table and on the order history page correctly, you must be able to add other details from the items table?? Pls help, once i've sorted this i can transfer all the files to the hosting server.

there is a tutorial here:
creating_order_history.php


for creating an order history page.