finding the higher 3 products from keywords
Moderator: General Moderators
finding the higher 3 products from keywords
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
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
-
impulse()
- Forum Regular
- Posts: 748
- Joined: Wed Aug 09, 2006 8:36 am
- Location: Staffordshire, UK
- Contact:
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];
}
Last edited by impulse() on Sun Dec 10, 2006 7:18 pm, edited 1 time in total.
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
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
-
impulse()
- Forum Regular
- Posts: 748
- Joined: Wed Aug 09, 2006 8:36 am
- Location: Staffordshire, UK
- Contact:
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.
Code: Select all
count- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
I asked the question, which would suggest I believe a separate table is better.
Why?
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
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
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.htmlisaac_cm wrote:AND...
please let me see what you mean , I need sql to do that , "impulse()" code did not help at all
ok,
I made another table name is "keys"
fields is pk (autoinc), prod_id (int), word (varchar)
and here is the sql
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
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)thanks
- neel_basu
- Forum Contributor
- Posts: 454
- Joined: Wed Dec 06, 2006 9:33 am
- Location: Picnic Garden, Kolkata, India
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
?>To Sort The Products
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
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
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]- 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