loop problem

[RyanSF07]

Hi All,

 

I got this select statement to work using phpadmin as advised by cooldude yesterday.

 

$sql = "SELECT video.id, title, description_text, category_text, level_text, pass_text,
user_name, item_name, AVG( rating ) AS totalRating FROM video, registered_users, 
rating WHERE user_id = registered_users.id AND video.category_text = 'English' 
AND level_text = 'low-intermediate' GROUP BY rating ORDER BY totalRating DESC";

 

Now the trouble is that only the first record in the database is retrieved and then repeated a number of times. I think the number of times it is repeated corresponds to the number of items in the database with the same rating. So, for example, if 5 items in the database are rated with three stars, that first record in the database retrieved via the Select query is repeated 5 times -- rather than the 5 actual entries with the 3 star rating. Anyways, I think now the problem is with this, which directly follows the above:

 

$query_result = mysql_query($sql);
while ($row = mysql_fetch_array($query_result)) {

$videoID = $row["id"];
$ratingData = Rating::OutputRating($videoID);

$content .= "
<a class=\"bl\" href = \"quiz_int1.php?id=$row[id]\" target='_self'> $row[title]</a>$ratingData";
}

 

Any ideas on how to fix this?

 

Thanks much,

Ryan

[Ken2k7]

In the query, you see how you said video.id? Well do that for the other fields. SQL needs to know what table you're selecting those fields from.

[RyanSF07]

thanks. I changed it to:

 

$sql = "SELECT video.id, video.title, video.description_text, video.category_text, video.level_text, video.pass_text, registered_users.user_name, rating.item_name, 
AVG( rating ) AS totalRating FROM video, registered_users, rating 
WHERE user_id = registered_users.id AND video.category_text = 'English' 
AND level_text = 'low-intermediate' GROUP BY rating ORDER BY totalRating DESC";

 

but still see the same results.

 

Also, when I run this query in phpmyadmin, it returns the correct results. So I know the Select statement is working, it's just that the info isn't being displayed correctly -- something in the loop is fixating on one record and displaying it over and over.

 

Any other ideas? Thanks!

[redarrow]

 

try mysql_fetch_assoc....

<?php


$sql = "SELECT video.id, video.title, video.description_text, video.category_text, video.level_text, video.pass_text, registered_users.user_name, rating.item_name, 
AVG( rating ) AS totalRating FROM video, registered_users, rating 
WHERE user_id = registered_users.id AND video.category_text = 'English' 
AND level_text = 'low-intermediate' GROUP BY rating ORDER BY totalRating DESC";

$query_result = mysql_query($sql);

while ($row = mysql_fetch_assoc($query_result)) {

$videoID = $row["id"];
$ratingData = Rating::OutputRating($videoID);

$content .= "
<a class=\"bl\" href = \"quiz_int1.php?id=$row[id]\" target='_self'> $row[title]</a>$ratingData";
}

?>

[Ken2k7]

 

try mysql_fetch_assoc....

<?php


$sql = "SELECT video.id, video.title, video.description_text, video.category_text, video.level_text, video.pass_text, registered_users.user_name, rating.item_name, 
AVG( rating ) AS totalRating FROM video, registered_users, rating 
WHERE user_id = registered_users.id AND video.category_text = 'English' 
AND level_text = 'low-intermediate' GROUP BY rating ORDER BY totalRating DESC";

$query_result = mysql_query($sql);

while ($row = mysql_fetch_assoc($query_result)) {

$videoID = $row["id"];
$ratingData = Rating::OutputRating($videoID);

$content .= "
<a class=\"bl\" href = \"quiz_int1.php?id=$row[id]\" target='_self'> $row[title]</a>$ratingData";
}

?>

 

The function mysql_fetch_array() by default returns both an associative and numeric indices.

 

Change this:

$content .= "
<a class=\"bl\" href = \"quiz_int1.php?id=$row[id]\" target='_self'> $row[title]</a>$ratingData";

 

To:

$content .= "
<a class=\"bl\" href = \"quiz_int1.php?id={$row['id']}\" target='_self'>{$row['title']}</a>$ratingData";

[RyanSF07]

nope. unfortunately, that didn't work either.

 

Also, when I remove these parts:

 

"rating.item_name, AVG( rating ) AS totalRating" and "GROUP BY rating ORDER BY totalRating DESC"

 

all of the items display correctly -- they just aren't listed in order from highest average to lowest.

 

So the loop does work correctly without the above, and the whole SELECT query works correctly in phpadmin (returns all of the correct data).

 

:-\

 

All ideas greatly appreciated.

[RyanSF07]

I've had no luck with this.

 

Any other ideas?

 

Thanks