Here’s a feature that I’ve added product detail pages on iHTML Merchant Enterprise websites for a number of clients. It’s the Classic "Customers who bought this item also bought" feature that appears on many eCommerce websites. It selects and displays the top selling products according previous sale order history of other products ordered.
The above example is using a one row HTML table layout placed. You can display products in a bulleted ordered list or however you like. I’m going to share with you the basics of how to code this feature. The rest is up to you where you position and place things. I generally place it after the related products code.
Here’s the SQL code involved
And away we go….
This code works with Microsoft SQL Server
(no promises how it works with mySQL, Postgres or others)
SELECT top 3 p.id, p.name, p.sell, p.price2, p.thumbnail, SUM(od2.qty - od2.refundqty) AS total FROM orderdetail od,orderdetail od2, products p, orders o WHERE o.receiptnum != 'rec0000' AND od.pid=:pid AND od2.ordid=od.ordid AND od2.pid<>:pid AND o.id=od2.ordid AND od2.pid=p.id AND p.active=1 AND od2.storeid=:storeid AND p.storeid=:storeid GROUP BY p.id, p.name, p.sell, p.price2, p.thumbnail ORDER BY total DESC
I’m only selecting 3 products to display using "top 3", feel free to change this to the desired number of product records you desire to show.
There are 4 database tables involved
- Orders
- Orderdetail (used twice with different table name aliases)
- Products
Make certain invalid orders are filtered out the where clause using (o.receiptnum != ‘rec0000′).
Since this feature is display on the products detail page, I’m going to pull up a list of orders based on order details that the product is in. I’m going to fetch all the other products that people ordered. I take the SUM of product qty ordered minus any refunded qty and get the total qty amount. I use the total in the ORDER by clause in desc order.
One thing to note: If you change fields in the SELECT statement be certain to make similar changes to the GROUP BY clause. If you remove p.thumbnail remove it from the GROUP BY clause. If you wish to add another field from the products table be certain to include it in the GROUP by clause.
In the example code below there is a modified version of the SQL to support the Show Zero stock configuration value. Extra code to filter out products that have reached Zero stock levels.
EXAMPLE CODE
<irem —- Get Show Zero stock setting from store configuration —.
<iSQL ALIAS="sp" DBNAME=":datasource" LOGIN=":loginname" SQL="SELECT cvalue FROM config WHERE cname=’SHOWZEROSTOCK’ AND storeid=:storeid">
<iSQLFETCH ALIAS="sp">
<irem —- Selects which SQL Query to use based on show zero stock value (true|false —>
<iIF ALIAS="show_zero_stock_item" COND=":sp_cvalue">
<iSQLMORE ALIAS="sp" SQL="SELECT top 3 p.id, p.name, p.sell, p.price2, p.thumbnail, SUM(od2.qty – od2.refundqty) AS total FROM orderdetail od,orderdetail od2, products p, ordersoWHEREo.receiptnum != ‘rec0000′ AND od.pid=:pid AND od2.ordid=od.ordid AND od2.pid<>:pid AND o.id=od2.ordid AND od2.pid=p.id AND p.active=1 AND od2.storeid=:storeid AND p.storeid=:storeid GROUP BY p.id, p.name, p.sell, p.price2, p.thumbnail ORDER BY total DESC">
<iELSE ALIAS="show_zero_stock_item">
<iSQLMORE ALIAS="sp" SQL="SELECT top 3 p.id, p.name, p.sell, p.price2, p.thumbnail, SUM(od2.qty – od2.refundqty) AS total FROM orderdetail od,orderdetail od2, products p, ordersoWHEREo.receiptnum != ‘rec0000′ AND od.pid=:pid AND od2.ordid=od.ordid AND od2.pid<>:pid AND o.id=od2.ordid AND od2.pid=p.id AND p.active=1 AND (p.isstock=0 or (p.isstock <> 0 AND p.stock > 0)) AND od2.storeid=:storeid AND p.storeid=:storeid GROUP BY p.id, p.name, p.sell, p.price2, p.thumbnail ORDER BY total DESC">
</iIF ALIAS="show_zero_stock_item">
<irem — fetch top 3 products based on previous order history —>
<iSQLFETCH ALIAS="sp">
<iIF ALIAS="show" NOTCOND=":i_sqlempty">
<table><tr>
<iWHILE NOTALIAS="i_sqlempty">
<td>
<irem — Product HTML code to suit your needs goes here —->
:sp_id
:sp_name
:sp_sell
:sp_price
:sp_thumbnail
</td>
<iSQLFETCH ALIAS="sp">
</iWHILE ALIAS="i_sqlempty">
</tr></table>
</iIF ALIAS="show">
Comments
This code works with Microsoft SQL Server 2000 on up. I’m not certain about MS SQL Server 7.x since it’s been years since I worked with it. For other database servers, it should be work fine it you are allowed to alias a database table twice in your SQL queries.
