Page 1 of 2
finding the higher 3 products from keywords
Posted: Sun Dec 10, 2006 5:58 pm
by isaac_cm
Hello,
I use MYSQL and I want to make "product recommendation" when a user select product for detail preview I need to display more 3 products to current selected one
I need to make sql that fetch me 3 results of the higher 3 products related to the current product based on keywords exists in every product
the keywords is stored in one field (mediumtext) separated by spaces
what is the sql for doing that
thanks
Posted: Sun Dec 10, 2006 6:40 pm
by impulse()
Maybe this would work:
Code: Select all
$query = mysql_query("SELECT * FROM 'yourTable'
ORDER BY 'yourField'");
while ($res = mysql_fetch_array($query)) {
$contents[$i] = $res['yourField'];
$i++;
}
for ($i = 0; $i <= 2; $i++) {
echo $contents[$i];
}
Posted: Sun Dec 10, 2006 7:12 pm
by isaac_cm
no this way I get only the first 3 products, it could be done using limit 3
I will explain what I need in steps:
1- get the current selected products keywords and store it in a variable or array
2- loop through all the keywords, in each loop I have to get the number of finding this keyword in all products of the current category (ex: bags)
(ex: keywords = "satin bag(5), classy(9), magnetic clasp(2), Dark(7), Navy(4), Fabric(15)"
3- compare all number and get the higher 3 results (Fabric(15), classy(9), Dark(7)) then get one product id from each of the 3 results to display as a recommended product and exclude the current one of course
thanks
Posted: Sun Dec 10, 2006 7:23 pm
by impulse()
I'm pretty sure
will be useful for this, but I'm unsure as to what PHP function will order them ascending or descending. I'm confident such function exists.
Posted: Mon Dec 11, 2006 12:19 am
by feyd
May I ask why keywords are stored in a single field instead of a separate table?
Posted: Mon Dec 11, 2006 6:25 am
by isaac_cm
Why I would need other table to store keywords, storing keywords in one field separated with a character is enough, do you think ?
Posted: Mon Dec 11, 2006 10:16 am
by feyd
I asked the question, which would suggest I believe a separate table is better.
Why?
- Well it's more normalized -- You can store FAR more tags. You're penalized for longer tags in a single field.
- It's easier to search -- With them in a single field you have to get a bit creative to find matches. As a separate table it's a simple join.
- Maintenance is easier -- You don't have to rebuild the entire field if you want to alter or remove a tag, just update or delete a simple record
Posted: Mon Dec 11, 2006 1:42 pm
by isaac_cm
ok, however I still need an answer to my first question
I need to build a product recommendation system, that when a customer view a product detail I want to give him 2 or 3 more recommended products
Posted: Mon Dec 11, 2006 3:12 pm
by feyd
Using a separate table would make the query a fairly simple JOIN request with potentially some aggregate functions.
Posted: Mon Dec 11, 2006 4:35 pm
by isaac_cm
AND...
please let me see what you mean , I need sql to do that , "impulse()" code did not help at all
Posted: Mon Dec 11, 2006 5:38 pm
by John Cartwright
isaac_cm wrote:AND...
please let me see what you mean , I need sql to do that , "impulse()" code did not help at all
Instead of giving you the code, I think it is more beneficial for you to try first, ask questions later. This is of course the spirit of which this forum was founded. Feyd has already given you a reference point, you may also find this to be very helpful
http://dev.mysql.com/doc/refman/4.1/en/join.html
Posted: Mon Dec 11, 2006 6:05 pm
by isaac_cm
ok,
I made another table name is "keys"
fields is pk (autoinc), prod_id (int), word (varchar)
and here is the sql
Code: Select all
SELECT
products.prod_id,
`keys`.word
FROM
products
RIGHT OUTER JOIN `keys` ON (products.prod_id=`keys`.prod_id)
WHERE
(products.prod_id = 2)
which prod_id = 2 is the current select product now I want to get another 2 products have the maximum similarity to product 2
thanks
Posted: Mon Dec 11, 2006 10:51 pm
by neel_basu
If You Use AJAX You Will Get a Better Performance
And The Codes May Be A Little bit Different in php
Posted: Tue Dec 12, 2006 12:06 am
by neel_basu
Code: Select all
<input type="hidden" name="product" value="" />
Code: Select all
<?php
//As You Are Separating Keywords By Space The Individual Jeywords Must Not Contain Spaces
$keyw = $_GET['product'];//You Can Also Use POST/Cookies Etc..
$host = "localhost";
$usr = "root";
$psw = "";
$db_name = "key"
$tbl_name = "shop"
$key_field = "key"
$product_field = "product";
$conn = mysql_connect($host, $usr, $psw) or die(mysql_error());
mysql_select_db($db_name) or die(mysql_error());
$sql = "select `$key_field` from `$tbl_name` where `$product_field` = '$keyw'";
$res = mysql_query($sql, $conn) or die(mysql_error());
//Assuming There Is No Repetation Of Product Name In the $product_field Coloumn of The Table
//So that is mysql_num_rows = 1
$curr_key = explode(" ", $res);
for($i=0;$i<=count($curr_key);$i++)
{
$sql_loop = "select `$key_field` from `$tbl_name` where `$product_field` like '$curr_key[$i]'";
$res_loop = mysql_query($sql_loop, $conn) or die(mysql_error());
$num_match = mysql_num_rows($res_loop);
$num_str .= $num_match.",";
$curr_str .= $curr_key." ";
$hld_key_stat .= $curr_key."(". $num_match .")"." ";
}
//Now $hld_key_stat Holds Like This "satin bag(5) classy(9) Dark(7) Navy(4) Fabric(15)" As You Requested
//Separatd By Spaces
//You Can echo It Or Use It In Other Purpouse
echo $hld_key_stat;
$num_arr = explode(",", $mun_str);
array_multisort($num_arr, SORT_DESC);
//Now $num_arr Holds Numbers Of Results In Dscending Order
$curr_arr = explode(" ", $curr_str);
//Now I Cant Decide Ho To Arrange The $curr_arr According To $num_arr
?>
Now I Cant Decide Ho To Arrange The $curr_arr According To $num_arr
To Sort The Products
Posted: Tue Dec 12, 2006 5:50 am
by isaac_cm
Many Thanks "
neel_basu"
This solution is what I am looking for, only few modification:
- I will make the separator character "," so I can use spaces in keywords
- I would rather use two dimensional array to store the result like this
Code: Select all
['satin bag',5], ['classy',9], ['Dark',7], ['Navy',4], ['Fabric',15]
and I have 3 questions:
- what is the function I can use to sort this 2 dim array based on the second element (desc sort of course)
- How to pick the recommended products based on those keywords using prod_id key field ?
- I am worried about the speed of this code , multi select statement in a loop may make the page load slower, isnt it ?
finally I wish if someone tell us how to solve this problem with join and aggregate functions
Thanks again