Ok, here are some ideas
Upselling - Suggesting a better or higher value alternative
Simple
Add a field to your product table of the same data type as your product id
Use this field for other product id’s which you ‘recommend’, or are of higher value/margin
Advanced
Create a new table for recommended products.
Need a minimum of 2 fields (if using a compound key) or 3 fields if using DataAssist.
DataAssist table example structure, ID (PK), MainProductID, RecommendedProductID.
Link this table to your products table in your select query.
Good place to use this is on product detail pages, (where you are already retrieving productid as a URL parameter), so you can filter the recordsets above.
Cross Selling – Suggesting additional or related products to increase customer spend per transaction
Both methods above still apply for cross selling, depending on the recommended products added.
Additional ways
Items recently viewed, (covered in this thread)
Reminds a customer what they were considering in this session.
Sometimes referred to as ‘Customers who bought x also bought y’
Analyse the order details table, to see what items were ordered at the same time as the currently selected product. Collate this data into descending order of quantity purchased, perhaps limiting to a few rows, and display on the product detail and/or cart pages. (This is only applicable when you have some order history.)
The benefit of this approach is that the data is always based on your order history, so will update itself.
That should get you started
