Table Union Results/Sort
Posted: Thu Aug 27, 2009 9:27 pm
Hi,
I've got a page where results are sorted by day, week, month, highest, and lowest--all these function properly. However there should also be a most commented and least commented result--These do not return results. Here's the problem, comments are stored in the comments table, where are compliments (main results) are stored in the comps table.
So what I'm trying to do is find the ID from the comments table with the most results and return it, with the message, etc. from the comps table in both asc (least) and desc (most) order. Hope that makes sense, here's the code which will probably help you understand.
I've got a page where results are sorted by day, week, month, highest, and lowest--all these function properly. However there should also be a most commented and least commented result--These do not return results. Here's the problem, comments are stored in the comments table, where are compliments (main results) are stored in the comps table.
So what I'm trying to do is find the ID from the comments table with the most results and return it, with the message, etc. from the comps table in both asc (least) and desc (most) order. Hope that makes sense, here's the code which will probably help you understand.
Code: Select all
<?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 = "<strong>Most Commented</strong>";
}
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("select * from comps order by numcoms $by3query LIMIT 5"); ****NOTE FOR FORUM: This query doesn't work because upon submission the numcoms is NOT updated so the field is empty for all comps****
$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="?by3=mcom">'. $mcomlabel .'</a> /
<a href="?by3=lcom">'. $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;
?>