[SOLVED] Complex Order BY

[cooldude832]

I have a system were users input "items" into the database into the table "items".

Users can them rate them and the rating table has

ItemID UserID Rating

 

so to get ratings I  do a select avg(Rating) from `Ratings` Where ItemID = $itemid

now I have a page where I list out some items my query looks like

<?php
$q = "Select "* from `".DEALS_TABLE."` Where Date_start >= '".date("Y-m-d G:i:s", strtotime(date("Y-m-d")))."' ORDER BY  Date_posted DESC";
?>

 

then I have to recover the rating so that is a second query in my loop

and then  i need that users name who listed it so its a third query in the loop

 

is there a way to reduce all this to 1 query?

 

 

[cooldude832]

I wrote this so far to get the username in the select the items info  (It works)

<?php
$fields = array(
			DEALS_TABLE.".DealID",
			DEALS_TABLE.".Title",
			DEALS_TABLE.".Type",
			DEALS_TABLE.".Product_Name",
			DEALS_TABLE.".Description",
			DEALS_TABLE.".Price",
			DEALS_TABLE.".Img_url",
			DEALS_TABLE.".Product_url",
			DEALS_TABLE.".Date_start",
			DEALS_TABLE.".Date_end",
			DEALS_TABLE.".Date_posted",
			DEALS_TABLE.".Hits",
			USERS_TABLE.".Username",
			USERS_TABLE.".UserID");
		$fields = implode(" , ", $fields);
		$q = "Select ".$fields." from `".DEALS_TABLE."`, `".USERS_TABLE."`
			Where ".DEALS_TABLE.".Date_start >= '".date("Y-m-d G:i:s", strtotime(date("Y-m-d")))."' and ".DEALS_TABLE.".UserID = ".USERS_TABLE.".UserID
			ORDER BY ".DEALS_TABLE.".Date_posted desc limit ".$lower_limit.", ".$upper_limit;
		$r = mysql_query($q) or die(mysql_error()."<br />".$q);
?>

 

Now I just need to figure out the Average, phpmyadmin keep giving me errors trying to do AVG(ratings_table.rating)

[cooldude832]

I get this

Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

 

when I do

<?php
		$fields = array(
			DEALS_TABLE.".DealID",
			DEALS_TABLE.".Title",
			DEALS_TABLE.".Type",
			DEALS_TABLE.".Product_Name",
			DEALS_TABLE.".Description",
			DEALS_TABLE.".Price",
			DEALS_TABLE.".Img_url",
			DEALS_TABLE.".Product_url",
			DEALS_TABLE.".Date_start",
			DEALS_TABLE.".Date_end",
			DEALS_TABLE.".Date_posted",
			DEALS_TABLE.".Hits",
			USERS_TABLE.".Username",
			USERS_TABLE.".UserID",
			"AVG(".DEALS_RATINGS_TABLE.".RATING) as Rating",
			"COUNT(".DEALS_RATINGS_TABLE.".RATING) as Rating_count",
			);
		$fields = implode(" , ", $fields);
		$q = "Select ".$fields." from `".DEALS_TABLE."`, `".USERS_TABLE."`, `".DEALS_RATINGS_TABLE."`
			Where ".
				DEALS_TABLE.".Date_start >= '".date("Y-m-d G:i:s", strtotime(date("Y-m-d")))."' and ".
				DEALS_TABLE.".UserID = ".USERS_TABLE.".UserID and ".
				DEALS_RATINGS_TABLE.".DealID = ".DEALS_TABLE.".DealID
			ORDER BY ".DEALS_TABLE.".Date_posted desc limit ".$lower_limit.", ".$upper_limit;
?>

 

Should I add GROUP BY DEALS_TABLE.".DealID"

??

[cooldude832]

and I figrued it out by reading

this is it

<?php
		$fields = array(
			DEALS_TABLE.".DealID",
			DEALS_TABLE.".Title",
			DEALS_TABLE.".Type",
			DEALS_TABLE.".Product_Name",
			DEALS_TABLE.".Description",
			DEALS_TABLE.".Price",
			DEALS_TABLE.".Img_url",
			DEALS_TABLE.".Product_url",
			DEALS_TABLE.".Date_start",
			DEALS_TABLE.".Date_end",
			DEALS_TABLE.".Date_posted",
			DEALS_TABLE.".Hits",
			USERS_TABLE.".Username",
			USERS_TABLE.".UserID",
			"AVG(".DEALS_RATINGS_TABLE.".RATING) as Rating",
			"COUNT(".DEALS_RATINGS_TABLE.".RATING) as Rating_count",
			);
		$fields = implode(" , ", $fields);
		$q = "Select ".$fields." from `".DEALS_TABLE."`, `".USERS_TABLE."`, `".DEALS_RATINGS_TABLE."`
			Where ".
				DEALS_TABLE.".Date_start >= '".date("Y-m-d G:i:s", strtotime(date("Y-m-d")))."' and ".
				DEALS_TABLE.".UserID = ".USERS_TABLE.".UserID and ".
				DEALS_RATINGS_TABLE.".DealID = ".DEALS_TABLE.".DealID GROUP BY ".DEALS_TABLE.".DealID
			ORDER BY ".DEALS_TABLE.".Date_posted DESC  LIMIT ".$lower_limit.", ".$upper_limit;
?>

 

See any issues with this?

[cooldude832]

I made an alteration to it

now my ratings are +1/-1

so I am puling

"SUM(".DEALS_RATINGS_TABLE.".RATING) as Rating"

 

and I want to order by this so i said

ORDER BY Rating

but they are randomly ordered