Question on table join

dilbert

I have a query that need to get info from two tables as follows:

$query = "SELECT table1.title, table2.item_id, table2.feel, AVG(table2.feel) as avgfeel FROM table1,
table2 WHERE table1.item_id = table2.item_id GROUP BY table2.item_id ORDER BY table2.feel DESC";

$result = mysql_query($query) || die( mysql_error() );
while ($row = mysql_fetch_array($result)) {

$feel = number_format($row[avgfeel], 2);

echo "$row[title] - $feel<br>";
}

I keep getting this error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /path/to/script.php on line 51

Line 51 is this:

while ($row = mysql_fetch_array($result)) {

Ideas?

barand

you need to

GROUP BY table1.title, table2.item_id, table2.feel

When grouping, you need to group by all non-aggregated fields in the select statement.

hth

tomhath

The "die" should've stopped you from getting that far.

Change the "||" to "or" and see if that catches the error.

dilbert

This seems to have done the trick, thanks for the help!

dilbert

When the results are displayed the value is incorrect. For instance, I can see that a value should be 8.50 but is listed as 9.00. Anything noticeable in the code that would cause this?

barand

I wonder if it insists on returning an integer

What happens with AVG(table2.feel * 1.0)

dilbert

I got it working, I had a bad column reference and it is now fine so I think the query was correct.