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;
