tmworking Posted August 28, 2009 Share Posted August 28, 2009 I've got a sort page, and everything works EXCEPT sort by most and least commented. Can you help me find the error here? I think it's in the query since unions are a bit of a mystery to me, but I'm just learning so any help is appreciated. <?php include('header.php'); //USER INPUT $by = clean($_GET['by']); $by2 = clean($_GET['by2']); $by3 = clean($_GET['by3']); if($by=="best"){ $bylab = "best"; $byquery ="votes1"; $by3query = "DESC"; $byget = "?by=best"; $bestlabel = "<span class='bold red'>Highest</span>"; }else{ $bestlabel = "Highest"; } if($by=="worst"){ $bylab = "worst"; $byquery = "votes2"; $by3query = "DESC"; $byget = "?by=worst"; $worstlabel = "<span class='bold red'>Lowest</span>"; }else{ $worstlabel = "Lowest"; } if($by2=="day"){ } if($by2=="week"){ } if($by3=="least-commented"){ $by3get = "?by3=least-commented"; $by3query = "ASC"; $lcomlabel = "<strong>Least Commented</strong>"; }else{ $lcomlabel = "Least Commented"; } if($by3=="most-commented"){ $by3get = "?by3=most-commented"; $by3query = "DESC"; $mcomlabel = "<strong>Most Commented</strong>"; }else{ $mcomlabel = "Most Commented"; } if(!$by3){ $ncomlabel = "<strong>Neither</strong>"; }else{ $ncomlabel = "Neither"; } // GET TODAY TIMESTAMP FROM BEGINNING OF DAY $datetime = getdate(time()); $mon = $datetime['mon']; $date = $datetime['mday']; $year = $datetime['year']; $wkday = $datetime['wday']; $today = mktime(0,0,0,$mon,$date,$year); // GET TIMESTAMP FROM BEGINNING OF WEEK $startwkday = $date-$wkday+1; $thisweek = mktime(0,0,0,$mon,$startwkday,$year); // GET TIMESTAMP DATA FROM &ID= IN ADDRESS BAR $tstamp = clean($_GET['id']); if($tstamp){ $datetime = getdate($tstamp); $day = $datetime['weekday']; $month = $datetime['month']; $mon = $datetime['mon']; $date = $datetime['mday']; $year = $datetime['year']; $wkday = $datetime['wday']; $startwkday2 = $date-$wkday+1; $chosendatetime = "$day $date $month $year"; } $daylabel = "Day"; $weeklabel = "Week"; $alltimelabel = "All Time"; if($by2=="day"){ $daylabel = "<strong>Day</strong>"; $pretime = mktime(0,0,0,$mon,$date-1,$year); $start = mktime(0,0,0,$mon,$date,$year); $aftertime = mktime(0,0,0,$mon,$date+1,$year); $by2lab = "day"; $by2query = ""; //$by2get = "&by2=day&id=$start"; $by2get = "-by-day-" . $start; $best_query = mysql_query("SELECT * FROM `comps` where Accepted='Yes' and time between '$start' and '$aftertime' ORDER BY `$byquery` DESC LIMIT 5"); } if($by2=="week"){ $weeklabel = "<strong>Week</strong>"; $pretime = mktime(0,0,0,$mon,$startwkday2-7,$year); $start = mktime(0,0,0,$mon,$startwkday2,$year); $aftertime = mktime(0,0,0,$mon,$startwkday2+7,$year); $by2lab = "week"; $by2query = ""; $by2get = "&by2=week&id=$start"; $best_query = mysql_query("SELECT * FROM `comps` where Accepted='Yes' and time between '$start' and '$aftertime' ORDER BY `$byquery` DESC LIMIT 5"); } if(empty($by2)){ $alltimelabel = "<strong>All Time</strong>"; $best_query = mysql_query("SELECT * FROM `comps` ORDER BY `$byquery` $by3query LIMIT 5"); } if($by3){ $bylab = "best"; $query = "(select comps.*, count(comments.id) as cnt from comps, comments where comps.id=comments.comp_id group by comments.comp_id) union ( select comps.*, '0' as cnt from comps where id not in (select comp_id from comments) ) order by cnt $by3query limit 5"; $best_query = mysql_query($query); } $array = $best_query; $cnt = '<form action="submitCompliment.php" class="submit_form" method="POST" target="sendCommentIFrame"> <table id="submissionform"> <tr><td class="input" colspan=5> <textarea onfocus="javascript:document.getElementById(\'commbox\').value=\'\';" name="comp" rows="13" height="75" cols="20" id="commbox">'. $names[4] .'</textarea> </td></tr> <tr><td> <select name="cat">'; while($category = current($catlist)){ $cnt .= '<option value="'. key($catlist) .'">'. current($catlist) .'</option>'; next($catlist); } $cnt .= ' </select></td></tr> <tr><td><input type="submit" name="Ok" value="Submit!" class="button_login2"/> </td></tr> <tr><td> <div id="loadingsubmit_article" style="display:none;"><img src="images/loader.gif"/></div> <div id="resultsubmit_article" style="display:none;"></div> </td></tr> </table> </form><iframe name="sendCommentIFrame" style="display:none"></iframe>'; $maincontent = "<h1><a href=\"javascript:submitToggle();\" class=\"submit\" style=\"font-weight: bold;\">" . $submitpop . "</a></h1><div style=\"display: none;\" id=\"submit\" class=\"post\"><div id=\"result2\" class=\"result\">" . $cnt . "</div></div>"; $maincontent .= ' <div id="result" style="text-align:center;"> <a href="'. $bylab .'-compliments-by-day-'. $today .'">'. $daylabel .'</a> / <a href="'. $bylab .'-compliments-by-week-'. $thisweek .'">'. $weeklabel .'</a> / <a href="'. $bylab .'-compliments">'. $alltimelabel .'</a> <div class="bestworst"> <a href="best-compliments">'. $bestlabel .'</a> / <a href="worst-compliments">'. $worstlabel .'</a> </div> </div> <div class="commented"> <a href="'. $bylab .'-compliments-most-commented">'. $mcomlabel .'</a> / <a href="'. $bylab .'-compliments-least-commented">'. $lcomlabel .'</a> </div> '; $voting_array = mysql_fetch_array(mysql_query("SELECT * FROM `votes` WHERE `ip`='$ip'")); $vote_explode = explode(',', $voting_array['comp_id']); foreach($vote_explode as $vote_ex){ $vote_ex = explode(":", $vote_ex); $compnum = $vote_ex[0]; $compvote = $vote_ex[1]; $compvotearray[$compnum] = $compvote; } if(mysql_num_rows($array) > 0){ while($row = mysql_fetch_array($array, MYSQL_ASSOC)){ // count the comments $commentsCountQuery = "select * from comments where comp_id=" . $row['id']; $commentsCountResult = mysql_query($commentsCountQuery); $commentsCount = mysql_num_rows($commentsCountResult); $maincontent .= "\n<div id=\"result". $row['id'] ."\" class=\"result\">\n"; $maincontent .= stripslashes($row['message']) ."\n<br /><br />\n"; $maincontent .= '<span class="sentBy"><span class="lside"><a href="comment-'. $row['id'] .'" class="commlink">read comments ('. $commentsCount .')</a></span>'; if(!$compvotearray[$row['id']]){ $maincontent .= '<span id="vote_buttons'. $row['id'] .'" class="vote_buttons"> <span id="a1votes_count'. $row['id'] .'"> <a id=":'. $row['id'] .':1:'. $row['votes1'] .':'. $row['votes2'] .':" class="vote_up" title="'. $phrase1 .':'. $phrase2 .'" href="javascript:;"> '. $phrase1 .' ('. $row['votes1'] .')</a></span>'; $maincontent .= ' - <span id="a2votes_count'. $row['id'] .'"> <a id=":'. $row['id'] .':2:'. $row['votes1'] .':'. $row['votes2'] .':" class="vote_down" title="'. $phrase1 .':'. $phrase2 .'" href="javascript:;"> '. $phrase2 .' ('. $row['votes2'] .')</a> </span></span><br />'; } else { $maincontent .= '<span id="vote_buttons'. $row['id'] .'" class="vote_buttons voted"> <span id="a1votes_count'. $row['id'] .'"> '. $phrase1 .' ('. $row['votes1'] .')</span>'; $maincontent .= ' - <span id="a2votes_count'. $row['id'] .'" class="vote_buttons voted"> '. $phrase2 .' ('. $row['votes2'] .') </span></span><br />'; } $maincontent .= '<span class="mesdetail">'. date('F j, Y \a\t g:i a ', $row['time']) .'by <a href="' . $row['user'] . '_' . $row['user'] .'-'. $row['user'] . '">'. $row['user'] .'</a> </span>'; $maincontent .= '<span class="mesdetail"> in - <a href="category-'. $row['cat'] .'_'. $catlist[$row['cat']] .'">'. $catlist[$row['cat']] .'</a></span>'; $maincontent .= "</div>"; } } else { $maincontent .= "<strong>There are no results in this category.</strong>"; } $layout = str_replace("[[main-content]]", $maincontent, $layout); echo $layout; ?> Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.