Jump to content

Why isn't this a valid MySQL result resource?


brucevader

Recommended Posts

I'm making a site with certain likenesses to imdb.com, but for dvds where people can enter their dvd collections as well as rate and comment the movies. I want to be able to display the titles as a "top ten" list, or at least show all of them ordered by their rating. However, this proved trickier than anticipated.

 

I want the rating score to be a combined average of the users' ratings for both the movie itself and the dvd production values, with the movie rating twice as important. This calculated score is not stored in my database, but every single rating is stored in a table named opinion as the int values filmgrade and dvdgrade (1 to 5).

 

After some failed attempts and follow-up research, I came up with a query that seemingly should return all dvds ordered by overall rating, but I still only get the warning message "mysql_fetch_object(): supplied argument is not a valid MySQL result resource". Can someone tell me what's wrong with my query?

 

Here it is:

 

SELECT id, title, edition, username, top.rating

FROM DVD, (

SELECT id, (Avg(filmgrade)*2+Avg(dvdgrade))/3 AS rating

FROM opinion

GROUP BY id

) AS top

WHERE DVD.id=top.id

ORDER BY top.rating DESC

 

Thanks

Link to comment
Share on other sites

welcome to the forums!

 

in a case like this, your best bet is to let mysql tell you the error itself. since we don't know your actual data, it's hard to trouble shoot... also, code snippets also help with the debugging process... try something like this for time being, though:

[!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--]

[span style=\"color:#0000BB\"]<?php

$sql [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#DD0000\"]\"SELECT id, title, edition, username, top.rating FROM DVD, (SELECT id, (Avg(filmgrade) * 2 + Avg(dvdgrade))/3 AS rating FROM opinion GROUP BY id) AS top WHERE DVD.id=top.id ORDER BY top.rating DESC\"[/span][span style=\"color:#007700\"];

[/span][span style=\"color:#0000BB\"]$result [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]mysql_query[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$sql[/span][span style=\"color:#007700\"]) or die([/span][span style=\"color:#0000BB\"]mysql_error[/span][span style=\"color:#007700\"]() . [/span][span style=\"color:#DD0000\"]\"<br /><br />Query: <i>$sql</i>\"[/span][span style=\"color:#007700\"]);

[/span][span style=\"color:#0000BB\"]?>

[/span]

[/span][!--PHP-Foot--][/div][!--PHP-EFoot--]

 

this will kill your script with a mysql_error() displayed and then show your query in italics afterward so that you can more easily debug. if this doesn't help, try to show a little of the relevant code surrounding the problem query.

 

good luck!

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.