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

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 »

Sorry I Cant Accept Your Thanks Cause I am Not Satisfied With My Code
neel_basu wrote:Now I Cant Decide Ho To Arrange The $curr_arr According To $num_arr
To Sort The Products
Please Anyone Solve It

No This Code Will Not Get Slower That Much
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 are trying to change separator of $hld_key_stat
Thats If You Want To Make This
atin bag(5) classy(9) Dark(7) Navy(4) Fabric(15)
Like
atin bag(5),classy(9),Dark(7),Navy(4),Fabric(15)
Just replace

Code: Select all

$hld_key_stat .= $curr_key."(". $num_match .")"." ";
With

Code: Select all

$hld_key_stat .= $curr_key."(". $num_match .")".",";
isaac_cm
Forum Commoner
Posts: 44
Joined: Wed May 17, 2006 8:47 am

Post by isaac_cm »

ok, any other ideas !!
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 »

isaac_cm wrote:I would rather use two dimensional array to store the result like this
Would you Please Specify The Variable Name of That Array ??
isaac_cm
Forum Commoner
Posts: 44
Joined: Wed May 17, 2006 8:47 am

Post by isaac_cm »

I do not understand you :?:

like this:

$res = [keyword, number of occurrence]
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 »

But $res is The MySql Query Variable
Look
$num_arr Holds The occurence Of x number Keyword in Your DB
$curr_arr Holds The Keword Holds The x th Keyword

After Using
array_multisort($num_arr, SORT_DESC);
$num_arr Get Arranged In Descending Order
neel_basu wrote:Now I Cant Decide How To Arrange The $curr_arr According To $num_arr
To Sort The Products
But The $curr_arr Doesn't Get Arrenger In Descending Order

So If You Remofe The First 3 From Your Conditions Thats It Will Show All The Matching Records of The Keyword Of The Current Product
isaac_cm wrote:fetch me 3 results of the higher 3 products
It Will Be Easier To Handel
isaac_cm
Forum Commoner
Posts: 44
Joined: Wed May 17, 2006 8:47 am

Post by isaac_cm »

So If You Remofe The First 3 From Your Conditions Thats It Will Show All The Matching Records of The Keyword Of The Current Product
I really did understand what you mean by this sentence

After I get result like this

['satin bag',5], ['classy',9], ['Dark',7], ['Navy',4], ['Fabric',15]

how I can get 3 product do I select them randomly from ['Fabric',15] or what , I can access products by key only "prod_id"

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 »

So Here Is The 2d Array Version Of The Script
===============================

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);
    $prod[$i][0] = $curr_key;
    $prod[$i][1] = $num_match;
  }
//Here $prod[$i][0] holds The Keyword And $prod[$i][1] Holds The Number Of Occurences
//**********************************************************************//
//**************It Is Not Arranged In Descending Order******************//
//**********************************************************************//
foreach($prod as $c)
  {
    while(list($k,$v) = each($c))
      {
        echo "$k .... $v";
      }
  }
?>
EDITED | Replaced $prd_m With $prod
Last edited by neel_basu on Wed Dec 13, 2006 10:48 am, edited 2 times in total.
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 »

With This Code You Can Select Either Random Or By index of The Arrays

But If You Wanna Show The Top 3 Results Thats In Descending Order Some Extra Codes are Required
isaac_cm
Forum Commoner
Posts: 44
Joined: Wed May 17, 2006 8:47 am

Post by isaac_cm »

ThANKS

random way would work, but for learning sake please show me how to get the higher 3 products
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 »

isaac_cm wrote: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
Look At The Code Carefully
Solition of 1-
Line 3 $keyw Contains The Current Product You Are Viewing
Solution 2-
Line 14 $sql is The sql Command To get the Keword List From The DataBase Table Separated By comma Or Space
And Then $res Holds That Result in single Line Separated By Some Separators
Line 18 $curr_key Makes An Array The Keywords
and Now Look In satin bag(5), classy(9), magnetic clasp(2), Dark(7), Navy(4), Fabric(15) 5,9,2,7,4 and 15 shows The Occurence Of That Very Keyword In The Database Table
Line 21 $sql_loop is the sql to search For The Occurences of each Keyword One After Another In A loop
Line 25 $num_match Holds The number Of Occurences Of That Keyword in that DataBase Table And It Does This Job By mysql_num_rows its a Trick look At This Line CaraFully
And Then $prod Is a 2d Array That Simply Stores It
==========================================================
The Best Would Be You Test This Script Then You'll Understand What It Does
User avatar
neel_basu
Forum Contributor
Posts: 454
Joined: Wed Dec 06, 2006 9:33 am
Location: Picnic Garden, Kolkata, India

Your Compleate Final Script

Post by neel_basu »

So Now The Final Compleate Script
========================

Code: Select all

<?php
function neelmax( $array )
  {
   foreach( $array as $value )
     {
       if( is_array($value) )
         {
           $subvalue = neelmax($value);
           if( $subvalue > $return )
             {
               $return = $subvalue;
             }
          }
           elseif($value > $return)
             {
               $return = $value;
             }
     }
   return $return;
  }
?>
<?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);
    $prod[$i][0] = $curr_key;
    $prod[$i][1] = $num_match;
  }
//Here $prod[$i][0] holds The Keyword And $prod[$i][1] Holds The Number Of Occurences
//**************************************************************************************//
//****************************It Is Not Arranged In Descending Order********************//
//**************************************************************************************//
$max = neelmax($prod);
for($i=0;$i<=count($prod);$i++)
  {
    if($prod[$i][1] == $max)
      {
        $top_prod_id = $i;
      }
  }
$sql_prod = "select `$product_field` from `$tbl_name` where `$key_field`` = '$prod[$top_prod_id][0]' && `$product_field` != '$keyw'";
$res_prod = mysql_query($sql_prod,$conn) or die(mysql_error());
while($db_arr = mysql_fetch_array($res_prod))
  {
    $top_prod_name = $db_arr[$product_field];
    echo $top_prod_name;
  }
if(mysql_num_rows($res_prod) == 0)
  {
    for($i=0;$i<=count($prod);$i++)
      {
        $prob_sql .= "`$key_field`` = '$prod[$i][0]' || ";
      }
    $prob_sql_final = substr($prob_sql,0,strlen($prob_sql)-3);
    $org_sql = "select `$product_field` from `$tbl_name` where ".$prob_sql_final."&& `$product_field` != '$keyw'";
    //echo $org_sql;//This Line Is To Test IfThe SQL Command Is Wrong To Test It Just Remove The Comment Before echo
    $res_prod_oth = mysql_query($org_sql,$conn) or die(mysql_error());
    while($db_arr_oth = mysql_fetch_array($res_prod_oth))
      {
        $prod_name_oth = $db_arr_oth[$product_field];
        echo $prod_name_oth;
      }
  }
?>
isaac_cm
Forum Commoner
Posts: 44
Joined: Wed May 17, 2006 8:47 am

Post by isaac_cm »

ok, I need to get product id in parent/child array

many 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 »

Well This Script Can Handle ID (Compleately Untested)
======================================

Code: Select all

<?php
function neelmax( $array )
  {
   foreach( $array as $value )
     {
       if( is_array($value) )
         {
           $subvalue = neelmax($value);
           if( $subvalue > $return )
             {
               $return = $subvalue;
             }
          }
           elseif($value > $return)
             {
               $return = $value;
             }
     }
   return $return;
  }
?>
<?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";
$product_id = "prod_id";

$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`,`$product_id` 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);
    $prod[$i][0] = $curr_key;
    $prod[$i][1] = $num_match;
    while($prod_id_wh = mysql_fetch_array($res_loop))
      {
        $prod[$i][2] = $prod_id_wh[$product_id];
      }
  }
//Here $prod[$i][0] holds The Keyword And $prod[$i][1] Holds The Number Of Occurences
//**************************************************************************************//
//****************************It Is Not Arranged In Descending Order********************//
//**************************************************************************************//
$max = neelmax($prod);
for($i=0;$i<=count($prod);$i++)
  {
    if($prod[$i][1] == $max)
      {
        $top_prod_id = $i;
      }
  }
$sql_prod = "select `$product_field`,`$product_id` from `$tbl_name` where `$key_field`` = '$prod[$top_prod_id][0]' && `$product_field` != '$keyw'";
$res_prod = mysql_query($sql_prod,$conn) or die(mysql_error());
while($db_arr = mysql_fetch_array($res_prod))
  {
    $top_prod_name = $db_arr[$product_field];
    $top_prod_id = $db_arr[$product_id];
    echo $top_prod_name;
  }
if(mysql_num_rows($res_prod) == 0)
  {
    for($i=0;$i<=count($prod);$i++)
      {
        $prob_sql .= "`$key_field`` = '$prod[$i][0]' || ";
      }
    $prob_sql_final = substr($prob_sql,0,strlen($prob_sql)-3);
    $org_sql = "select `$product_field`,`$product_id` from `$tbl_name` where ".$prob_sql_final."&& `$product_field` != '$keyw'";
    //echo $org_sql;//This Line Is To Test IfThe SQL Command Is Wrong To Test It Just Remove The Comment Before echo
    $res_prod_oth = mysql_query($org_sql,$conn) or die(mysql_error());
    while($db_arr_oth = mysql_fetch_array($res_prod_oth))
      {
        $prod_name_oth = $db_arr_oth[$product_field];
        $prod_id_oth = $db_arr_oth[$product_field];
        echo $prod_name_oth."...."$prod_id_oth;
      }
  }
?>
Post Reply