Jump to content

Order By Votes


Joshua F

Recommended Posts

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');

Link to comment
https://forums.phpfreaks.com/topic/210089-order-by-votes/
Share on other sites

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.

Link to comment
https://forums.phpfreaks.com/topic/210089-order-by-votes/#findComment-1096450
Share on other sites

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.

Link to comment
https://forums.phpfreaks.com/topic/210089-order-by-votes/#findComment-1096457
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.