Sorting Daily Top by votes

[epicrevolt]

Alright, another post. This one seems to be the most complex thing I have ever tried. So, I am making a site where a user can post jokes and vote on them. They can only vote up, so a digg like system. I want the users to be able to sort the list of jokes by vote within the last 24 hours. In other words, jokes posted within the last 24 hours , whichever has the most will be on top.
As of now, I am fetching jokes via a mysql query and a while array. It can sort jokes by category (by retrieving URL variable catid) right now, an I need it to also sort by Daily Top, and Weekly top. There can be potentially 2 variables in the url (like index.php?catid=3&sort=daily), so I need too know how I would sort the data. Would I sort the array or the query. I wanted to do it by query, but I don't know how to specify a time range in the query.
I have ideas on how I would implement it, just got to find out how to code it. I have been researching for about an hour and still am. The links below are what I have gathered through my research. I don't know if they are any help, but it's worth a shot.
http://www.php.net/manual/en/array.sorting.php
http://www.webmasterworld.com/forum88/9531.htm
http://www.tizag.com/mysqlTutorial/mysqlorderby.php
http://us2.php.net/manual/en/function.strtotime.php
http://ca3.php.net/manual/en/function.sort.php

I'd use query rather than sort via array.

You could have a table like:

joke_id | today | previous

You could run a cronjob at midnight to transfer add today to previous and setting today to 0.

THis just looks at last 24 hours and all-time. Making a week long period wouldn't work though, as you'd lose all the data in the total.

Otherwise store each vote as a row with the date - you can they total for the past week, 5 days, etc.

joke_id | dated

[qazplm114477]

I'm no expert so all I can do is offer my opinion...

When working with php and mysql, I always try to fetch the optimal results first in mysql and if I can't do it with mysql then I'll format with PHP. IF you have a date/time field in the table than you can sort it using that with the BETWEEN keyword in your Mysql Query. So...

if($_GET['sort'] == 'daily'){
   $query = "select * from jokes_tbl 
             where date_time_field BETWEEN 
             DATE_ADD(NOW(),INTERVAL -1 DAY) AND NOW() 
             ORDER BY rating";
}

Please note that I haven't tested that query, but there are plenty of different ways to sort via dates with php.

how to use the between in your query link:
http://www.tutorialspoint.com/mysql/mysql-between-clause.htm

[epicrevolt]

Thanks both of you, when I get back to my PC I will have to try both methods. I will report back on how I succeeded (or failed, lol).

[qazplm114477]

cool, good luck on it. just post here if you run into any problems

[epicrevolt]

I ran into a problem. My jokes are tabulated differently. My database simply does not store the vote count in the joke column. each vote is its own tiny row so I can check if a user has already voted on that specific joke, so it is a no go on that query. I will have to call the dates through the query, calculate vote counts for each joke, then order the new array by vote count.
Here is what my table looks like in case you wanted to know:
jokes
- id
- date
- catid
- joke
- authorid
- ip
votes
- voteid
- date
- authorid
- jokeid

Once again, I will get back to you on my findings.

[qazplm114477]

Hmm this gets a bit complex if we try to do this with the query alone. If joins wont work then (just thinking outside the box here)

$arg = array();//set empty array

//select all the jokes from the past 24 hrs
$query = 'select id from jokes where date_time_field BETWEEN DATE_ADD(NOW(),INTERVAL -1 DAY) AND NOW() ';

//query the database
while($row = /*query*/){
   $query = //select sum or count as total, depending on how you want to calculate the votes
            //where jokeid = $row['id'];
   //query the database
   while(){
      //assign the returned results to a variable
      $votes = $row['total'];
   }
   
   //put returned values into an array
   $arg[$row['id']] = $votes;
}

//Sort an array in reverse order via the value (high to low)
//arsort returns a Boolean, hence the if statement 
if(arsort($arg)){
   foreach($arg as $key => $value){
      //query the database again to display or have the $arg store a multidimensional array in which case you have to use array_multisort 
  }
}

please note that I haven't tested any of this out but the logic is there, I'm also not entirely sure if this is the best way to do it but hopefully you'll find it helpful.

[epicrevolt]

So I got a couple helpful tips from people saying that having a separate table for votes is a bad idea especially if you want to expand. So I just created a column in my jokes table called votes. My completed function for listing jokes is displayed below.

Before you go on ranting about my code, let me tell you what this code needed to accomplish.
Basically the total list of jokes from any given category in any given order (daily top, weekly top, or recent) are ALL processed through 1 single function. If you look closely, there are only 3 queries that are being made when the conditions are met. I slimmed it down as much as I could, tell me what you think :)

As for your code, I am still working it out, as your method seems more efficient than mine ;)

function list_jokes($catid,$order = null) { // give a category ID and generate a list of jokes from it

	$time = time();
	$day = time() - 86400;
	$week = time() - 604800;

	if(!empty($catid)) {
	$catidcheck = mysql_query("SELECT catid FROM categories WHERE catid = $catid");
	$catidcheck = mysql_num_rows($catidcheck);
	if($catidcheck == 0) {
		echo "<h1>Invalid category</h1>";
		exit();
		} else {
			if(($order === null) || ($order == "day")) {
				$jokes = mysql_query("SELECT * FROM jokes WHERE catid = '$catid' && date BETWEEN '$day' AND '$time' ORDER BY votes DESC");
				$title = 'Daily Top ';
				} elseif($order == "week") {
					$jokes = mysql_query("SELECT * FROM jokes WHERE catid = '$catid' && date BETWEEN '$week' AND '$time' ORDER BY votes DESC");
					$title = 'Weekly Top ';
					} elseif($order == "recent") { //sort by recent
						$jokes = mysql_query("SELECT * FROM jokes WHERE catid = '$catid' ORDER BY id DESC");
						$title = 'Recent ';
						}
			}
		} else {
			if(($order === null) || ($order == "day")) {
				$jokes = mysql_query("SELECT * FROM jokes WHERE date BETWEEN '$day' AND '$time' ORDER BY votes DESC");
				$title = 'Daily Top ';
				} elseif($order == "week") {
					$jokes = mysql_query("SELECT * FROM jokes WHERE date BETWEEN '$week' AND '$time' ORDER BY votes DESC");
					$title = 'Weekly Top ';
					} elseif($order == "recent") { //sort by recent
						$jokes = mysql_query("SELECT * FROM jokes ORDER BY id DESC");
						$title = 'Recent ';
						}
			}
				if(!empty($catid)) {
					$menu = mysql_query("SELECT * FROM categories WHERE catid = '$catid'");
					while($catrow = mysql_fetch_array($menu)) {
					$cattitle = $catrow['name'];
						echo "<h1>" . $title . $cattitle . "</h1>";
						}
					} else {
						echo "<h1>" . $title . " Jokes</h1>";
						}
				echo "<a href=\"index.php?";
				if(!empty($catid)) {
					echo "catid=" . $catid . "&order=day";
					} else {
						echo "order=day";
						}
				echo "\">Daily Top Jokes</a>";
				echo" | <a href=\"index.php?";
				if(!empty($catid)) {
					echo "catid=" . $catid . "&order=week";
					} else {
						echo "order=week";
						}
				echo "\">Weekly Top Jokes</a>";
				echo " | <a href=\"index.php?";
				if(!empty($catid)) {
					echo "catid=" . $catid . "&order=recent";
					} else {
						echo "order=recent";
						}
				echo "\">Recent Jokes</a>";
				while($row = mysql_fetch_array($jokes)) {
				$jokeid = $row['id'];
				$votes = mysql_query("SELECT * FROM votes WHERE jokeid=$jokeid");
				$votecount = mysql_num_rows($votes);
					echo "<div class=\"joke\">";
					echo "<a href=\"#\" class=\"rating\" id=\"" . $jokeid . "\">+ " . $votecount . "</a>";
					print $row ['joke'];
					echo "<div class=\"meta\">";
						echo joke_author($jokeid);
						echo " | ";
						$jtime = joke_time($jokeid);
						$jtime = relativeTime($jtime);
						echo $jtime;
						echo " | ";
					echo "<a href=\"joke.php?jid=" . $jokeid . "\">";
					echo comment_count($jokeid);
					echo "</a></div></div>";
					}
	}

[qazplm114477]

I'm assuming that you've managed to display the jokes properly? The logic in your code looks pretty sound to me, you might have to clean it up a little to make it more readable after you've got everything fixed. Add loads of comments can really help both you and other developers. Also try to check for stuff you can break into it's own separate function for better re-usability. For example, you had to rewrite you select query several times, maybe put it in a function that returns a query based on it's parameters.

good luck on your site

[epicrevolt]

That was my next step. I originally had a lot of different functions, but since I had to basically recode my ENTIRE list_jokes function, I just thought it would be easier to debug. I am currently separating it out into different functions again.
And yes, the code works perfectly :) Alpha version of the site should be out by Friday. Just got to make some ajax loading content (load as you scroll), or pagnation and I'm set (for alpha stage anyways). Thanks for all the help you have provided.