If You Like This, Then You Will Like This

[n8w]

I am trying to figure out the logic / mysql query I need to figure out "related suggestions"

 

So for example I have these illustrators http://www.illustrationmundo.com/illustrators.php

and if you like illustrator A you will probably like illustrator B,D,Z based on a table that contains people's favorite illustrators

 

the illustrator favorite table looks like this

 

illustrator_favorites

• user_id=the user who is favoriting the illustrator
  
• illustrator_id=the illustrator that is being favorited
  

So I assume I need to figure out people that like illustrator 12547 usually also likes illustrators 124,125478 and 15429

 

I have no idea how to do this .. any help is greatly appreciated

[Barand]

Have a look at this

http://forums.phpfreaks.com/topic/267573-collaberative-filtering/?do=findComment&comment=1372451

[n8w]

Thanks Barand .. that was a ton of help

 

Now I added a varchar field to my illustrator_table called related_illustrators which I plan to store a string of the related illustrator ids

example

123,23454,2345,1232

 

I want to run a cron job once a day to store these values

 

here is my cron job and I know it's super inefficient .. could you take a look at it to help me optimize it? Any help greatly appreciated

 

$start ="";
$querytime="";
$start = microtime(TRUE);



#############################
# BEGIN: related illustrators
#############################
$sql = 'UPDATE illustrators_table SET  related_illustrators=""'; //initialize
$retid = mysql_query($sql) or die(mysql_error());
$sql = 'SELECT illustrator_id, COUNT(*) as favorite_count FROM favorites GROUP BY illustrator_id';
$retid = mysql_query($sql) or die(mysql_error());
# check for errors
if (!$retid) {
die(mysql_error());
} else {
while ($row = mysql_fetch_array($retid)) {
 $illustrator_id = $row[illustrator_id];
 $favorite_count = $row[favorite_count];
 if($favorite_count>0){
  $sql2 = 'SELECT p.illustrator_id, count(*) as favorite_count FROM favorites p
 INNER JOIN (SELECT user_id FROM favorites WHERE illustrator_id = '.$illustrator_id.') as X using (user_id )
 WHERE illustrator_id  <> '.$illustrator_id.' GROUP BY illustrator_id
 ORDER BY favorite_count DESC LIMIT 17';

 $retid2 = mysql_query($sql2) or die(mysql_error());
 while ($row = mysql_fetch_array($retid2)) {
  $related_illustrators .=", ". $row[illustrator_id];
 }



 $sql3 = 'UPDATE illustrators_table SET'
 . ' related_illustrators = "'.$related_illustrators.'"'
 . ' WHERE'
 . ' illustrator_id = "'.$illustrator_id.' " LIMIT 1';
 //$retid3 = mysql_query($sql3) or die(mysql_error());
 }
}
}


$querytime = microtime(TRUE) - $start;
echo $querytime;

[Barand]

I want to run a cron job once a day to store these values

 

My advice is don't bother. Just run the query when you need it for a product or user.

[n8w]

good call that works great thanks!