Music Chart

[kempo]

Hello! i am trying to create a music chart for my site, and its frustrating ive found almost nothing that can help so far!

 

it sounds simple, basically, each band will have their own profile, somewhere on the page you'll be able to give them a vote, this vote will be sent to the full chart of all the bands which will order itself accordingly into top 100, with the band with the most votes at the top and the least votes at the bottom.

 

any ideas or questions?! please reply!

[cmgmyr]

start off with 3 tables

 

Users:

------

userid

username

password

email

...

 

Profile:

userid

profile

website

photo

...

 

Rate:

rid

userid

rating

...

 

Take a look at my site: www.SyracuseBands.net you might get some other ideas too

 

Hope this gets you started in the right direction

[kempo]

the top 10 thing on your site is exactly what i want!

 

i am a proper newbie at php, ive only really worked in html before...maybe we could come to some kind of arrangement..if you help me?!

[only one]

yea this is actually pretty simple.

 

on the vote page just do an update query or someting

 

$bands = mysql_query("SELECT * FROM bands WHERE bandname = '$_GET[bandname]'");

while ($band = mysql_fetch_array($bands)) {

mysql_query("UPDATE `bands` SET `votes` = '$band[votes] + 1' WHERE `bandbame` = '$_GET[bandname]'"); }

 

now to order 100 starting with the first on just do

 

$bands = mysql_query("SELECT * FROM bands WHERE bandname = '$_GET[bandname]' ORDER BY votes DESC LIMIT 100'");

while ($band = mysql_fetch_array($bands)) {

echo "$band[bandname]

<br />

\n";

}

 

edit bold bits

[cmgmyr]

here is my top 10 rating query:

 

SELECT rate.bandid, count(rate.bandid) as totalcount, avg( rate.rating ) as average, users.name FROM rate INNER JOIN users on (users.userid = rate.bandid) WHERE users . type = 'b' GROUP BY rate.bandid having totalcount >= 10 ORDER BY average DESC, users.name ASC LIMIT 10

[kempo]

does this all need mySQL then?

[cmgmyr]

yeah, then you pull out:

 

bandid

average

name

totalcount

 

and away you go!

[kempo]

away i go!....after i learn mySQL 

[cmgmyr]

haha yes

 

take a look at these:

 

http://www.tizag.com/mysqlTutorial/mysqlquery.php

http://www.php-mysql-tutorial.com/

[kempo]

cheers for your help, feel free to help me more!

[kempo]

wow this is pretty complicated, help me more!

[cmgmyr]

no problem, all you need to do is ask

 

if it is something general about php/mysql post it in here, if it is something more about a music site you can send me a PM or IM me (like how I did something)

[kempo]

i cant send personal messages apparently!

 

i'm thinking there must be a simple way of doing this! i was mucking around with this one voting system which use a text file to read and write the amount of votes.

 

problems with this were, i couldnt have it so you can only vote for one band and have it update just that one band in the document containing all the bands and votes (dont know if that makes sense)

 

and the other problem is it doesn't put them in order of highest vote, it just puts the amount of votes after it.

 

the text file is set out like this

 

PollQuestion

Pollanswer1:0

Pollanswer2:0

Pollanswer3:0

Pollanswer4:0

 

concrete example;

 

what is your favourite colour?

red:0

blue:0

green:0

 

this is the code:

 

 

 

 

<?php

/*************************************************

* Micro Polling System

*

* Version: 1.0

* Date: 2007-04-05

*

* Usage:

* Add your votings settings to polldata.txt file

* The first line is the question and the other

* linas are the possible answers.

*

****************************************************/

 

$pollQuestion = '';

$answers = '';

 

function readData(){

global $pollQuestion,$answers;

// Read configuration

$rawdata = file('polldata.txt');

// Get the question for polling

$pollQuestion = $rawdata[0];

 

// Get number of answers - The foirs row is the question

$numberOfAnswers = sizeof($rawdata)-1;

$count = 0;

for ($i=1; $i <= $numberOfAnswers; $i++){

$answerData = explode(':',$rawdata[$i]);

// If tha actual row is not empty than add to the answers array

if (strlen(trim($answerData[0]))>0){

$answers[$count]['text']  = $answerData[0];

$answers[$count]['count'] = $answerData[1];

++$count;

}

}

}

 

function writeData(){

global $pollQuestion,$answers;

$file = fopen('polldata.txt','w');

fwrite($file,$pollQuestion."\r\n",strlen($pollQuestion));

foreach ($answers as $value) {

$row = $value['text'].':'.$value['count']."\r\n";

fwrite($file,$row,strlen($row));

}

fclose($file);

}

 

readData();

?>

 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "DTD/xhtml1-transitional.dtd">

<html>

<head>

  <title>Micro Polling System</title>

  <link href="style/style.css" rel="stylesheet" type="text/css" />

</head>

<body>

    <div id="main">

<?php if (!isset($_POST['submitBtn'])) { ?>     

      <div class="caption"><?php echo $pollQuestion; ?></div>

      <div id="icon"> </div>

      <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post" name="poll">

        <table width="300">

        <?php

        foreach ($answers as $value) {

echo '<tr><td><input type="radio" name="polling" value="'.$value['text'].'"/> '.$value['text'].'</td></tr>';

        }

        ?>

          <tr><td align="center"><br/><input class="text" type="submit" name="submitBtn" value="Vote" /></td></tr>

        </table> 

      </form>

<?php   

} else {

    $count = 0;

      foreach ($answers as $value) {

if ($value['text']  == $_POST['polling']) {

$answers[$count]['count'] = ((int)$value['count'])+1;

(int)$totalCount++;

}

++$count;

      }

     

      writeData();

?>

      <div class="caption">Thanks for your vote!</div>

      <div id="icon"> </div>

      <div id="result">

        <table width="300">

<?php

      foreach ($answers as $value) {

echo '<tr><td> '.$value['text'].'</td><td>'.$value['count'].'</td></tr>';

      }

?>

        </table>

    </div>

<?php } ?>

<div id="source">Micro Polling System 1.0</div>

    </div>

</body> 

 

 

 

 

what do you think?

[cmgmyr]

you have to get a few more posts in order to send a PM.

 

anyways...I think you should make a poll with MySQL it will be a lot easier to manage that way and you wont have to worry about physical files.

[kempo]

your probly right, i'll try going through the tutorials properly

[kempo]

ok so i have a polling system set up on a mysql database, but now i cant figure out how to make it so each band page has a vote button which gets put into one big results table. any help?!

[cmgmyr]

just have a radio group with the values 1-5, put a hidden field in there that has the userid of the band. When they submit the form point that to a processing script that takes the bandid and the rating and insert it into the database.

[kempo]

ok, so ill need to create a database that takes, bandid, rating...anything else?

[cmgmyr]

yup, just another table

 

you should have:

rateid

bandid

rating

raterid(if you are requiring a login to be able to rate)

[kempo]

what will the rate id one do?

[cmgmyr]

it just gives a unique id to that row

[kempo]

so it will look like this?

 

rateid  bandid  rating

1        1          0

2        2          0

3        3          0

4        4          0

5        5          0

 

 

[cmgmyr]

Probably more like:

 

rateid  bandid  rating

1        2          5

2        2          3

3        8          2

4        5          4

5        6          5

 

...but yeah

[kempo]

haha

 

so i'll need to create a code that adds a value 1 to 5 onto the rating section. and is there a code that takes the bandid and changes into the actual band name, or shud i just make the band ids as their band name?

[kempo]

ok, im not doing too well at making this table!

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') VALUES('1', '1')' at line 2

 

hmm...if i make an sql table here can i transfer it to other servers??