Newbie needs help with updating db with loop

ErthDawg

This is my first attempt at ANY coding, so sorry if this is obvious; but I can't figure it out. I'm taking data from multiple tables and then sorting the results in descending order based on a "rating".

$query="SELECT B.name, A.title, S.song, C.contentid, C.prevposition, C.weeksonchart, 
R.rating_sum / R.rating_count as 'avgrating'
FROM
jos_musicboxsong S
JOIN jos_musicboxalbum A ON A.id = S.albumid
JOIN jos_musicbox X ON X.idalbum = A.id
JOIN jos_musicboxsinger B ON B.id = X.idauth
JOIN jos_musicbox_rating R ON R.content_id = S.id
JOIN jos_mbox_charttracking C ON C.contentid = S.id
ORDER BY avgrating DESC";
$result=mysql_query($query);

I then need to update a single table with the "top 10" results. I've been trying to do this using a loop, but it's not working.

if ($num>10) {
$to=10;
}else{
$to=$num;
}

$i=0;
while ($i < $to) {

$position=$i+1;
$songid=mysql_result($result,$i,"C.contentid");
$prevpos=mysql_result($result,$i,"C.prevposition");
$woc=mysql_result($result,$i,"C.weeksonchart");
$band=mysql_result($result,$i,"B.name");
$album=mysql_result($result,$i,"A.title");
$song=mysql_result($result,$i,"S.song");
$rating=mysql_result($result,$i,"avgrating");

$loop_update=mysql_query("UPDATE oms_topten_working
SET pos='$position',
prevpos='$prevpos', 
woc='$woc'+1, 
active=1
WHERE songid='$songid'");


$i++;
}

The loop works to display the data, but not to update the table. I'm sure there's an easier way to do this, but I'm brand new to all of this. Also, there will be scenarios where the songid doesn't exist in the table being updated. In that case, I would need to insert a row.

I believe I need to use an array for this, but again I'm brand new to all of this. Any help would be greatly appreciated! Thanks!

laserlight

Are you trying to cache the top 10 results? If not, you are better off either using the results immediately, or creating a view.

If you are trying to cache the results, then perhaps it would be simpler to truncate the table, then insert the 10 rows.

ErthDawg

I'll be caching the results. I'll be taking the immediate results and then storing to a table to display a list of "Top 10 songs" on a page. The results will be updated once per week.

Your idea works except there will be a value for "weeks on chart" and "previous position" and I'm not sure how to handle that. I could pull from the cache table for that, but there will be cases where a song from the immediate results isn't in the list of cache results. I'm sure there's a simple solution to this, too.

Thanks for the suggestion, it gives me some ideas.