finding the higher 3 products from keywords

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

isaac_cm
Forum Commoner
Posts: 44
Joined: Wed May 17, 2006 8:47 am

finding the higher 3 products from keywords

Post 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
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Post 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];
  }
Last edited by impulse() on Sun Dec 10, 2006 7:18 pm, edited 1 time in total.
isaac_cm
Forum Commoner
Posts: 44
Joined: Wed May 17, 2006 8:47 am

Post 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
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Post by impulse() »

I'm pretty sure

Code: Select all

count
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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

May I ask why keywords are stored in a single field instead of a separate table?
isaac_cm
Forum Commoner
Posts: 44
Joined: Wed May 17, 2006 8:47 am

Post 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 ?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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
isaac_cm
Forum Commoner
Posts: 44
Joined: Wed May 17, 2006 8:47 am

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Using a separate table would make the query a fairly simple JOIN request with potentially some aggregate functions.
isaac_cm
Forum Commoner
Posts: 44
Joined: Wed May 17, 2006 8:47 am

Post by isaac_cm »

AND...
please let me see what you mean , I need sql to do that , "impulse()" code did not help at all
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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
isaac_cm
Forum Commoner
Posts: 44
Joined: Wed May 17, 2006 8:47 am

Post 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
User avatar
neel_basu
Forum Contributor
Posts: 454
Joined: Wed Dec 06, 2006 9:33 am
Location: Picnic Garden, Kolkata, India

Post by neel_basu »

If You Use AJAX You Will Get a Better Performance
And The Codes May Be A Little bit Different in php
User avatar
neel_basu
Forum Contributor
Posts: 454
Joined: Wed Dec 06, 2006 9:33 am
Location: Picnic Garden, Kolkata, India

Post 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
isaac_cm
Forum Commoner
Posts: 44
Joined: Wed May 17, 2006 8:47 am

Post 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
Post Reply