Order By Votes

[Joshua F]

Hello, I have a little thing I am making, and it is a Toplist. People can vote for a user, and then there vote will go up. But people can only vote once per day on one IP address.

 

I am having a problem ordering my front page by votes though. Here is my code for it to load the votes, and to display the user's on the list.

 

<?php
include 'vote.php'
    $list_q = mysql_query("SELECT * FROM toplist WHERE premium='1' && activated='1' && ban='0' && staff='0' ORDER BY '$voteAmount'") or die (mysql_error());
    while($list_f = mysql_fetch_assoc($list_q)) {
list ($error, $message, $voteAmount) = voteAmount(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME, $list_f['id']);
?>
<tbody style="" id="cat_1_e">
<!-- start: forumbit_depth2_forum -->
<tr>
<td class="trow2" align="center" valign="middle" width="1"></td>
<td class="trow2" valign="middle">
<strong><a href="server.php?view=details&id=<?php echo $list_f['id'];?>"><?php echo capitalizeFirstCharacter($list_f['servername']);?></a></strong><div class="smalltext"><?php echo $list_f['shortdescription'];?></div>
</td>
<td class='trow1' valign='middle' align='left' style='white-space: nowrap'><span class='smalltext'><?php echo $list_f['revision'];?></span></td>

<td class="trow2" valign="middle" align="right" style="white-space: nowrap"><font size="4px"><?php echo $voteAmount;?> Votes</font></td>
</tr>
<!-- end: forumbit_depth2_forum -->

</tbody>
<?php } ?>

 

And here is the code to my vote.php file that it loads all of the data from.

<?php
function install($host, $user, $pass, $db) {
   $c = @mysql_connect($host, $user, $pass);
   if(mysql_select_db($db, $c)) {
     mysql_query('CREATE TABLE `'.$db.'`.`votes` (
                  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
                  `ip` VARCHAR(50) NOT NULL,
                  `serverId` INT(50) UNSIGNED NOT NULL,
                  `date` DATETIME NOT NULL,
                  PRIMARY KEY (`id`)
                ) ENGINE = InnoDB;');
     return true;
   } else {
      return false;
   } 
}
function voteAmount($host, $user, $pass, $db, $serverId) {
   $c = @mysql_connect($host, $user, $pass);
    if($c) {
      if(mysql_select_db($db, $c)) {
        if($getVotes = mysql_query('SELECT * FROM `'. $db .'`.`votes` WHERE serverId='. $serverId)) {
        if(mysql_num_rows($getVotes) >= 1) {
          $getVoteQ = mysql_query('SELECT COUNT(id) AS total FROM `'. $db .'`.`votes` WHERE serverId='. $serverId);
          $vote = mysql_fetch_assoc($getVoteQ);
          return array(0, "", $vote['total']);
        } else {
          return array(0, "", 0);
        }
      	} else {
      	if(install($host, $user, $pass, $db)) {
          return array(1, "The vote script has successfully been installed.<br>Please reload the page.", 0, 0);
      } else {
          return array(1, "Could not connect to DB.", 0);
      }
      	}
      } else {
        return array(1, "Error connecting to DB.", 0);
      } 
    } else {
      if(install($host, $user, $pass, $db)) {
          return array(1, "The vote script has successfully been installed.<br>Please reload the page.", 0, 0);
      } else {
          return array(1, "Could not connect to DB.", 0);
      }
    }
}              
// list ($error, $message, $voteAmount) = voteAmount('HOST', 'USER', 'PASS', 'DB', $_SERVER['REMOTE_ADDR'], SERVER_ID);
function votedToday($host, $user, $pass, $db, $hours, $ip, $serverId) {
  $c = @mysql_connect($host, $user, $pass);
    if($c) {
      if(mysql_select_db($db, $c)) {
        if($getVotes = mysql_query('SELECT * FROM `'. $db .'`.`votes` WHERE ip="'. $ip .'"')) {
        if(mysql_num_rows($getVotes) >= 1) {
          $dateQ = mysql_query('SELECT MAX(date) AS lastVoteDate FROM `'. $db .'`.`votes` WHERE ip="'. $ip .'"') or die(mysql_error());
          $getDate = mysql_fetch_assoc($dateQ);
          $diffrence = time() - strtotime($getDate['lastVoteDate']);
          if (($diffrence / 60 / 60) >= $hours) {       
            mysql_query('INSERT INTO `'. $db .'`.`votes` (`ip`, `serverId`, `date`) VALUES ("'. $ip .'", '. $serverId .', NOW())') or die(mysql_error());
            return array(0, "Your vote has been added.");      
          } else {
            return array(1, "You can only vote every ". $hours ." hours. So far ". number_format(($diffrence / 60 / 60), 2) ." hours have past.");    
          }
        } else {
          mysql_query('INSERT INTO `'. $db .'`.`votes` (`ip`, `serverId`, `date`) VALUES ("'. $ip .'", '. $serverId .', NOW())') or die(mysql_error());
          return array(0, "Your vote has been added.");
        }
        } else {
          if(install($host, $user, $pass, $db)) {
          	  return array(1, "The vote script has successfully been installed.<br>Please reload the page.");
	      } else {
	          return array(1, "Could not connect to DB.");
	      }        
        }
      } else {
          return array(1, "Error connecting to DB.");
      }   
    } else {
      if(install($host, $user, $pass, $db)) {
          return array(1, "The vote script has successfully been installed.<br>Please reload the page.");
      } else {
          return array(1, "Could not connect to DB.");
      }
    }
}

// list ($error, $message) = votedToday('HOST', 'USER', 'PASS', 'DB', HOURS, $_SERVER['REMOTE_ADDR'], SERVER_ID);
?>

 

Once again, I am trying to make it so the list is ordered by how many votes that server has. Here is an example of the votes database.

CREATE TABLE IF NOT EXISTS `votes` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ip` varchar(50) NOT NULL,
  `serverId` int(50) unsigned NOT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

INSERT INTO `votes` (`id`, `ip`, `serverId`, `date`) VALUES
(6, '::1', 4, '2010-08-07 11:44:44');

[cherran]

Could adding ASC or DESC after this .... ORDER BY '$voteAmount'")  solve the problem?

[Joshua F]

Tried it, I think the problem is when it loads the list on the front page, it has a code(EX: $list_f['table']) that it must be used, then the table name, and the code for it to look up the votes should be above the part were it loads the database, so it can order it by votes. But I can't do that because it has to load the $list_f[id], and for it to do so it has to be under the code that makes it load.

 

Example:

<?php
    $list_q = mysql_query("SELECT * FROM toplist WHERE premium='1' && activated='1' && ban='0' && staff='0' ORDER BY '$voteAmount'") or die (mysql_error());
    while($list_f = mysql_fetch_assoc($list_q)) {
list ($error, $message, $voteAmount) = voteAmount(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME, $list_f['id']);
?>

I think

list ($error, $message, $voteAmount) = voteAmount(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME, $list_f['id']);

I supposed to be above

$list_q = mysql_query("SELECT * FROM toplist WHERE premium='1' && activated='1' && ban='0' && staff='0' ORDER BY '$voteAmount'") or die (mysql_error());

But if I put it there It wont know where to load it from.

[void]

not sure what that list() does there at all, but ..

 

"SELECT * FROM toplist WHERE premium='1' && activated='1' && ban='0' && staff='0' ORDER BY (SELECT COUNT(*) FROM votes WHERE votes.serverId = toplist.id) DESC"

[Joshua F]

not sure what that list() does there at all, but ..

 

"SELECT * FROM toplist WHERE premium='1' && activated='1' && ban='0' && staff='0' ORDER BY (SELECT COUNT(*) FROM votes WHERE votes.serverId = toplist.id) DESC"

 

Worked, Thank you very much. I doubt I'd even think of to make it do that. Hah.