- Recommendation Types
- Choosing an Approach
- Providing Manual Recommendations
- Providing Automatic Recommendations
- Final Thoughts
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Providing Manual Recommendations
If it makes sense to go the “manual” recommendation route, you would need to add a new table to the database:
CREATE TABLE `product_recommendations` ( `product_id1` int(10) unsigned NOT NULL, `product_id2` int(10) unsigned NOT NULL, PRIMARY KEY (`product_id1`, `product_id2`) )
The product_recommendations table would simply store every recommended product. It’s actually a junction table, for managing the many-to-many relationship that would exist between products and products! With the table defined as it is, it can be used in a couple of ways.
First, the recommendations could be used in both directions. If one row of data combines product A and product B, then product A is a recommendation for product B, and product B could also be used as a recommendation for product A.
Alternatively, if you’d rather, you could treat the table so that recommendations only go in one direction: product_id2 is a recommendation for product_id1, but not the other way around. If both recommendations are required, two rows would have to be inserted. (Admittedly, this last use of the table would prevent the site from accidentally downselling.)
Next, you’ll need to add PHP code to the site’s administration for the process of creating, editing, and deleting recommendations. On the page for adding products, you could create a select menu that allows for the selection of multiple items:
echo '<select name="recommendations" multiple="multiple">';
$q = 'SELECT product_id, product_name FROM products';
$r = mysqli_query($dbc, $q);
while ($row = mysqli_fetch_array($r, MYSQLI_NUM)) {
echo "<option value=\"$row[0]\">$row[1]</option>\n";
}
echo '</select>';
After the form has been submitted and validated, create a new record in the product_recommendations table for each selection:
$product_id1 = mysqli_insert_id($r); // Just created product's ID!
foreach ($_POST['recommendations'] as $product_id2) { // Fetch every recommendation.
if (filter_var($product_id2, FILTER_VALIDATE_INT, array('min_range' => 1))) { // Validate!
$q = "INSERT INTO product_recommendations VALUES ($product_id1, $product_id2)";
$r = mysqli_query($dbc, $q);
}
}
The edit page is trickier in that you’d need to add code that pre-selects the appropriate options in the recommendations menu. Upon form submission, the easiest way to correctly populate the product_recommendations table would be to first delete every record for the current product, then replicate the INSERT code just shown.
When displaying products to the customers, to fetch other recommended products, you would run this query (assuming that $pid is the product ID for the item currently being viewed):
SELECT p.* FROM product_recommendations AS pr JOIN products AS p WHERE (pr.product_id2=p.product_id AND pr.product_id1 = $pid) OR (pr.product_id1=p.product_id AND pr.product_id2=$pid)
That query assumes that the recommendations are being used in both directions. If that’s not the case for your site, the query would be simpler:
SELECT p.* FROM product_recommendations AS pr JOIN products AS p WHERE pr.product_id2=p.product_id AND pr.product_id1 = $pid
You would also want to change what columns are returned based upon what would actually be used: the product’s name, price, image, etc.