'Top 5' Pics Display PHP MySQL

[invision]

Hello (again),

I need a little help once more with some PHP and MySQL , should be pretty straightforward.

On my site I have a rating scheme, it works great. However , on the home page, I want to display the 5 most highly rated pictures, stored in a 'user_images' table. I have a 'vote' table that stores each individual vote, and I use the 'round' to figure out an average.

However, I wondered how easy it would be to display the title of each image and their average vote on the home page.

Anyone care to help out?

Many thanks once more,


Michael.

[shimul]

u said u have two tables but u didnt provide relationship among them . however i'm giving u a query and try with it .

QueryString:
---------
selct user_images.id, user_images.name, count(vote.uid) from user_images, vote Where user_images.id = vote.uid group by vote.uid order by count(vote.uid) desc limit 5
---------


hopw it wil help you.

[invision]

Hello Shimul,

Thanks for the response.

I didn't want to throw everything into one post.

But here's the two tables :

CREATE TABLE `user_images` (
`id` tinyint(4) NOT NULL auto_increment,
`image_name` varchar(30) NOT NULL default '',
`img_src` mediumblob NOT NULL,
`content_type` varchar(20) NOT NULL default '',
`dateUpload` date NOT NULL default '0000-00-00',
`comments` text NOT NULL,
`area` varchar(30) NOT NULL default '',
`cameraType` varchar(50) NOT NULL default '',
`longitude` float NOT NULL default '0',
`latitude` float NOT NULL default '0',
`aid` tinyint(4) NOT NULL default '0',
`user_id` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
)


CREATE TABLE `vote` (
`vid` tinyint(4) NOT NULL auto_increment,
`imgid` tinyint(4) NOT NULL default '0',
`vote` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`vid`)
)


Here's the code I used adapted from the above, but still no luck


<?
require_once ('../mysql_connect.php');

$query_top5 = "SELECT user_images.id, user_images.image_name, count(vote.vote) FROM user_images, vote WHERE user_images.id = vote.imgid GROUP BY vote.vid ORDER BY count(vote.vote) DESC LIMIT 5";
$result2 = @mysql_query($query_top5) or trigger_error("Query: $query_top5\n<br />MySQL Error: " . mysql_error()); // Run the Query.
$row2 = mysql_fetch_array($result2, MYSQL_NUM);

echo '<a href="picture.php?id=' . $row2[0] . '">' . $row2['1'] . ' - ' . $row2['2'] . '</a>';
?>


Any help would be much appreciated.

[shimul]

ok now try with this query. i didnt run at my machine but it will work fine.

selct user_images.id, user_images.image_name, count(vote.imgid) from user_images, vote Where user_images.id = vote.imgid group by vote.imgid order by count(vote.imgid) desc limit 5

[invision]

Hi !

Unfortunately, I get a MySQL error "Invalid use of group function"

[shimul]

ok . now i tested it . its ok now. here is your query

SELECT user_images.id, user_images.image_name, count( vote.vid ) AS cnt
FROM user_images, vote
WHERE user_images.id = vote.imgid
GROUP BY user_images.id
ORDER BY cnt DESC
LIMIT 5

[invision]

That's weird, I had made pretty well the exact same changes to my query too :)

Currently, it's only displaying one 'image_name' with the vote '4', so do I need to use a while loop to go through them all ?

<tr>
<td valign="top" class="leftnavCellnoU">
<a href="picture.php?id=22">Elvanfoot - 4</a> </td>
</tr>

http://www.michaelmcg.co.uk/screengrab.gif

[invision]

Also, this might complicate things a little, but I wanted to display the average vote in the Top 5. The picture 'Elvanfoot' received 3 votes '4', '1' and '1' and I worked out the average was 1.75 , so is there anyway to display that in the output ?

[shimul]

yeh you have to use a loop to show them all.

[invision]

Thanks again for all your help.

The annoying thing is that it just displays a vote, rather than the average vote for a place.

[shimul]

mysql have AVG function for making average among fields . try with this function and if u cant then better make avarage at php as u get vote count for each image. that will be easier for u to display .

[invision]

Yes, I used AVG as average and it worked :)