Here’s my problem. I have a table called prank with two fields, one called pid which will store the players ID and one called ip which stores the IP address of the person that submitted the players ID. I am setting this up as a basic voting system and counting the number of results for each pid submitted.
pid | ip
1 | 1.2.3.4.5.6
3 | 3.5.5.3.5.3
1 | 5.3.6.6.4
I have this nested inside a while loop which counts the number of entries for each pid, this works fine. pid 1 would return 2 and pid 3 would return a total of 1.
<?
$query_count = "SELECT pid, COUNT(*) as total from prank WHERE pid = '$uid' GROUP BY pid";
$result_count = mysql_query($query_count) or die(mysql_error());
if ($vote_count = mysql_fetch_array($result_count)) {
echo $vote_count['total'];
} else {
echo '0';
} ?>
The problem I’m having is then taking the counted values and sorting them from highest to lowest and then inserting that into my database query so that when I use ORDER BY the player with the most votes will be first and so on.
$sql = "SELECT * FROM player";
I know I need to use ORDER BY DESC, but I’m not sure how to pass the counted values that are in a while loop to my database query that is located in the header of my .php file.
SELECT pid, Count( pid ) AS total
FROM `prank`
GROUP BY pid
ORDER BY total DESC
So were asking the query to group all the same pid’s together, count how many there are of each and output the pid reference and total in each group. then to order those records by the total value largest first.
then all you need to do is read out the values in the array.
$query_count = @mysql_query("
SELECT pid, Count( pid ) AS total
FROM `prank`
GROUP BY pid
ORDER BY total DESC
");
if(!$query_count){
//your error code here
}
while ($sql = mysql_fetch_array($query_count)){
echo 'User ID ' . $sql['pid'] . ' - ' . $sql['total'] . ' votes<br />';
}
Thanks, I got your code to work on my test page, but I’m having trouble using it with my already existing sql query that joins all my tables together.
My code is
$sql = "SELECT * FROM player
LEFT JOIN national on player.national_id = national.n_id
LEFT JOIN club on player.club_id = club.c_id
LEFT JOIN profile_pic ON player.p_id = profile_pic.proimg_id";
Is there a way I can put your code into it so I have one query that LEFT JOINS all my tables and counts the pid so it can ORDER BY total DESC
$query_count = @mysql_query("
SELECT pid, Count( pid ) AS total
FROM `prank`
GROUP BY pid
ORDER BY total DESC
");
Something like this? I don’t think it’ll work, but something along these lines
$sql = "SELECT pid, Count( pid ) AS total
FROM `prank`
GROUP BY pid
ORDER BY total DESC
SELECT * FROM player
LEFT JOIN national on player.national_id = national.n_id
LEFT JOIN club on player.club_id = club.c_id
LEFT JOIN profile_pic ON player.p_id = profile_pic.proimg_id";
$sql = "SELECT pid, COUNT(pid) AS total, add your other used fields here
FROM player
LEFT JOIN national ON player.national_id = national.n_id
LEFT JOIN club ON player.club_id = club.c_id
LEFT JOIN profile_pic ON player.p_id = profile_pic.proimg_id
GROUP BY pid
ORDER BY total DESC
";
You should never really use * in your SELECT statements so find out which fields you are using in the query and add those in the select statement
As youve not posted you database layout I have no way to know what your fields are called, this is the problem when you dont post your real code at the start of the thread !!
You’ll need to change the instances of ‘pid’ for the real field name that your using.
I think my problem is, the pid field is in the prank table and everything else I need is from the player table. So I need to somehow tell it to get pid from prank and everything else I wanted from player.
$sql = "SELECT player.p_id, player.national_id, player.club_id, player.player_first, player.player_last, player.month, player.year, player.day,
national.national_name, club.club_name, player.player_position, profile_pic.profile_image, profile_pic.profile_thumb
FROM player
LEFT JOIN national on player.national_id = national.n_id
LEFT JOIN club on player.club_id = club.c_id
LEFT JOIN profile_pic ON player.p_id = profile_pic.proimg_id";
This is what I have working now, I got rid of the * like you mentioned and just put in the fields I needed.
I tried doing
$sql = "SELECT pid, COUNT(pid) AS total,
player.p_id, player.national_id, player.club_id, player.player_first, player.player_last, player.month, player.year, player.day,
national.national_name, club.club_name, player.player_position, profile_pic.profile_image, profile_pic.profile_thumb
FROM player
LEFT JOIN national on player.national_id = national.n_id
LEFT JOIN club on player.club_id = club.c_id
LEFT JOIN profile_pic ON player.p_id = profile_pic.proimg_id";
I think the issue is that it’s looking in the player table instead of the prank table.
I tried adding LEFT JOIN player.p_id = prank.pid which have the same values, but that didn’t solve the problem.
$sql = "SELECT pid, COUNT(pid) AS total,
player.p_id, player.national_id, player.club_id,
player.player_first, player.player_last, player.month,
player.year, player.day, national.national_name,
club.club_name, player.player_position,
profile_pic.profile_image, profile_pic.profile_thumb
FROM player
LEFT JOIN prank
ON prank.pid = player.p_id
LEFT JOIN national
ON player.national_id = national.n_id
LEFT JOIN club
ON player.club_id = club.c_id
LEFT JOIN profile_pic
ON player.p_id = profile_pic.proimg_id
GROUP BY pid
ORDER BY total DESC";
Strikes me that your gathering an awfull lot of data just to give a ranking, are you sure that this particular query needs all that data ?
This ended up working and I added this like you mentioned it worked great thanks!
GROUP BY pid
ORDER BY total DESC
The only issue I’m having now is that because I selected the pid table I think it’s only displaying the results from that table (which are the players with votes) rather than displaying both the players with votes and the players with 0 votes which aren’t in prank.pid field
