Problem Inserting to table using loop

ErthDawg

I'm pulling results from multiple tables and then using a loop to write the top 10 rows to a new table. If I echo out the variables to the screen during the loop, all of the data shows, but when I use the loop to insert to the new table it always skips the first row. Can someone tell me what I'm doing wrong, please? Thanks!

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

for ($i = 0; $i < $to; $i++) {
$row = mysql_fetch_assoc($result);
extract($row);
$pos=$i+1;

$loop="INSERT INTO 
oms_topten_working 
(pos, prevpos, woc, band, album, song, songid, rating)
Values
('$pos', 0, 0, '$name', '$title', '$song', '$id', '$rating'";
$update=mysql_query($loop);
}

NogDog

Do you execute any kind of mysql_fetch_*() command on this query result prior to this code? If so, you will need to do a [man]mysql_data_seek/man to rewind the result set pointer back to row 0 before starting your loop.

ErthDawg

The only mysql_fetch_*() is in the loop. Here's what preceeds the loop and the loop again. The variables are working, I can echo them out during the loop and the correct data displays. The first row just doesn't get written to the database from the INSERT INTO statement.

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

$num=mysql_numrows($result);

//truncate working table
$truncate=mysql_query("TRUNCATE
oms_topten_working");

echo "working table truncated";

//Loop inserts Top 10 to working table
if ($num>10) {
$to=10;
}else{
$to=$num;
}

for ($i = 0; $i < $to; $i++) {
$row = mysql_fetch_assoc($result);
extract($row);
$pos=$i+1;

$loop="INSERT INTO 
oms_topten_working 
(pos, prevpos, woc, band, album, song, songid, rating)
Values
('$pos', 0, 0, '$name', '$title', '$song', '$id', '$avgrating')";
$update=mysql_query($loop);

}

ErthDawg

Figured it out. It had nothing to do with the php code. There was a ' in a piece of the data that was being written. I guess when it got passed as a variable to be inserted it caused that row to drop. I'm brand new to this, so I'll try to figure out how to account for that kind of data. Sorry for the goose chase and thanks for the help.

NogDog

$loop = "INSERT INTO
oms_topten_working
(pos, prevpos, woc, band, album, song, songid, rating)
Values
('%s', 0, 0, '%s', '%s', '%s', '%s', '%s')";
$loop = sprintf(
   $loop,
   mysql_real_escape_string('$pos'),
   mysql_real_escape_string('$name'), 
   mysql_real_escape_string('$title'), 
   mysql_real_escape_string('$song'), 
   mysql_real_escape_string('$id'), 
   mysql_real_escape_string('$avgrating')
);
$update=mysql_query($loop);