3 replies to this topic

[mandla]

I am trying make a voting system for my site and I have a part where people can vote certain things say in this case Top Hits

In my Database I have a Table called Tophits, which has the following feilds. Song Name, Artist and CloseDate

for this question I will provide a rough draft of my entries in databse.

TABLE =  TOPHITS
------------------------------------------------------------------------------------------------
Song                                 Artist                                 CloseDate
------------------------------------------------------------------------------------------------
Whats Up                             Singer                                 2010-08-13
Whats Up                             Singer                                 2010-08-06
Hello                                Chorus                                 2010-08-20
Hello                                Chorus                                 2010-08-20
Hello                                Chorus                                 2010-08-20
Hello                                Chorus                                 2010-08-20
Whats Up                             Singer                                 2010-08-13
Whats Up                             Singer                                 2010-08-13
Whats Up                             Singer                                 2010-08-13
Whats Up                             Singer                                 2010-08-13
Whats Up                             Singer                                 2010-08-13
Hello                                Chorus                                 2010-08-13
Hello                                Chorus                                 2010-08-13
Hello                                Chorus                                 2010-08-13
Hello                                Chorus                                 2010-08-13
Goodbye                              Hippies                                2010-08-13
Goodbye                              Hippies                                2010-08-13
Goodbye                              Hippies                                2010-08-13
Goodbye                              Hippies                                2010-08-13
Goodbye                              Hippies                                2010-08-13
Goodbye                              Hippies                                2010-08-13
Goodbye                              Hippies                                2010-08-13
Goodbye                              Hippies                                2010-08-13
Goodbye                              Hippies                                2010-08-13
Goodbye                              Hippies                                2010-08-13
Goodbye                              Hippies                                2010-08-13
Goodbye                              Hippies                                2010-08-13

What I am looking to have as a result a count of only songs with a close date = 2010-08-13 arranged by the number of occurances.
but instead of repeated results like

Goodbye                              Hippies                                2010-08-13
Goodbye                              Hippies                                2010-08-13
Goodbye                              Hippies                                2010-08-13
Goodbye                              Hippies                                2010-08-13
Goodbye                              Hippies                                2010-08-13
Goodbye                              Hippies                                2010-08-13
Goodbye                              Hippies                                2010-08-13
Goodbye                              Hippies                                2010-08-13
Goodbye                              Hippies                                2010-08-13
Goodbye                              Hippies                                2010-08-13
Goodbye                              Hippies                                2010-08-13
Goodbye                              Hippies                                2010-08-13
Whats Up                             Singer                                 2010-08-13
Whats Up                             Singer                                 2010-08-13
Whats Up                             Singer                                 2010-08-13
Whats Up                             Singer                                 2010-08-13
Whats Up                             Singer                                 2010-08-13
Whats Up                             Singer                                 2010-08-13
Hello                                Chorus                                 2010-08-13
Hello                                Chorus                                 2010-08-13
Hello                                Chorus                                 2010-08-13
Hello                                Chorus                                 2010-08-13

I would instead like results that would only display a count and automatically should rearrange the list into descending order witht he most recurring song at the top and the least recurring one at the bottom. Similar to the one listed below.

Goodbye                              Hippies                 12            
Whats Up                             Singer                   6
Hello                                Chorus                   4

Thanks for all the help.

[Quatrux]

I think it would be something like this:

SELECT song,artist,count(song) as hits FROM table1 GROUP BY song, artist;

Furthermore, I presume you would need to order by by hits descending or ascending, the way you want.

But what if the song is with the same name, but the artist is different? When I would suggest to do concat song and artist into one value to make it unique.

Edited by Quatrux, 26 August 2010 - 12:27 PM.

[web_designer]

your script should be like this

<?php

$dbhost = 'your server name';
$dbuser = 'your user name';
$dbpass = 'your password';

$con = mysql_connect($dbhost, $dbuser, $dbpass) or die  ("couldn't connect to database");

$db= mysql_select_db ("your database name", $con);



if ($query1= mysql_query ( " SELECT * FROM tophits WHERE closedate= '2010-08-13' AND song= 'Goodbye'")) {

$row1= mysql_fetch_array ($query1);


$num1= mysql_num_rows ($query1);

echo "$row1[song]"," ","$row1[artist]"," ",$num1, " ","<br/>";

}


if ($query2= mysql_query ( " SELECT * FROM tophits WHERE closedate= '2010-08-13' and song= 'Whats Up'")) {


$row2= mysql_fetch_array ($query2);

$num2= mysql_num_rows ($query2);

echo "$row2[song]"," ","$row2[artist]"," ",$num2, "<br/>";

}

if ($query3= mysql_query ( " SELECT * FROM tophits WHERE closedate= '2010-08-13' and song= 'Hello'")) {


$row3= mysql_fetch_array ($query3);

$num3= mysql_num_rows ($query3);

echo "$row3[song]"," ","$row3[artist]"," ",$num3, "<br/>";

}


mysql_close ($con);

?>

i test it and it worked perfectly, so if you have any questions feel free to ask me. good luck.

[SzamanGN]

That solution will solve your problems:

SELECT `song`,`artist`,COUNT(`id_th`) AS `countedvotes` FROM `tophits` WHERE `closedate`="2011-01-03" GROUP BY `song`, `artist` ORDER BY `countedvotes` DESC

It will show counted votes for the songs and artists.
Even if you have more then one the same name for the song or artist, you will have the correct statistics (I have tested it).