I working on a custom made forum, This code currently displays threads from all categories and the threads are arranged from most commented first. here is the code : <?php $Threadquery = mysql_query("SELECT * FROM threads ORDER BY threadid DESC LIMIT $count , 1 ") or die(mysql_error()); $Threaddata = mysql_fetch_array($Threadquery); $NoThreadquery = mysql_query("SELECT * FROM comments where threadid='".$Threaddata['threadid']."'") or die(mysql_error()); $NoThreadComments = mysql_num_rows($NoThreadquery); ?> PHP: I want to display threads category wise, arranged most commented first. here are the categories in the forum : entertainment, others, travel. Hope a pro from here can help me out.. Thanks
try this: <?php $arrange = array(); $Threadquery = mysql_query("SELECT * FROM threads ORDER BY threadid DESC LIMIT $count , 1 ") or die(mysql_error()); while($Threaddata = mysql_fetch_array($Threadquery)) { $NoThreadquery = mysql_query("SELECT * FROM comments where threadid='".$Threaddata['threadid']."'") or die(mysql_error()); $NoThreadComments = mysql_num_rows($NoThreadquery); $arrange[$Threaddata['id']] = $NoThreadComments; } arsort($arrange); foreach($arrange as $id=>$NumComments) { /* Now, $id = the thread id, $NumComments = number of comments in the thread. The thread ids in the foreach are arranged by number of comments. the first one is the thread with the most comments. */ } ?> PHP:
I'm not sure but I think Oli3L may have misread your question. Just to confirm: do you want to order the results by categories (and amount of comments) or do you just want to pull results from just one category only? Also, what is the context of the code you posted? Is it in a while loop? Post the whole code.
OLi3L, thank for the help out, as chronomus mentioned, u may have misread the thread. I want the results to be shown from just one category (not a mix of all categories). and the results to be ordered by the most commented. The code above does this; it gathers results from all categories and order it by most commented.
<table width="1020" border="0"> <tbody><tr> <td width="220"> </td> <td style="font-family: 'Palatino Linotype'; font-size: 12px; color: rgb(0, 153, 0);" width="393" height="22" valign="top">MOST RECENT</td> <td width="378" valign="top"><span style="font-family: 'Palatino Linotype'; font-size: 12px; color: rgb(0, 153, 0);">MOST POPULAR</span></td> </tr> <?php $count = 0; while($count < 5) { ?> <tr> <td width="220"> </td> <td valign="top"><table width="393" border="0"> <tbody> <?php $Threadquery = mysql_query("SELECT * FROM threads ORDER BY threadid DESC LIMIT $count , 1 ") or die(mysql_error()); $Threaddata = mysql_fetch_array($Threadquery); $NoThreadquery = mysql_query("SELECT * FROM comments where threadid='".$Threaddata['threadid']."'") or die(mysql_error()); $NoThreadComments = mysql_num_rows($NoThreadquery); ?> <tr valign="top"> <td style="width:160;"> </td> <td style="font-family: 'Palatino Linotype'; font-size: 14px; color: rgb(255, 255, 255);" width="15" align="center" valign="top">+</td> <td width="368"><span style="font-family: 'Palatino Linotype'; font-size: 12px;"><a href="postdetails.php?thread=<?=$Threaddata['threadid'];?>&type=<?=$Threaddata['categoryid'];?>" rel="nofolloow"><?=$Threaddata['title'];?></a><span style="color: rgb(143, 71, 143);"> <br> by <?=$Threaddata['username'];?></span> <span style="color: rgb(237, 243, 254);"><?=$NoThreadComments;?> comment(s)</span></span></td> </tr> </tbody></table></td> <td valign="top"> <table width="378" border="0"> <tbody> <?php $Threadquery = mysql_query("SELECT threadid, count(threadid) as count FROM comments GROUP BY threadid ORDER BY count DESC LIMIT $count , 1 ") or die(mysql_error()); $Threaddata = mysql_fetch_array($Threadquery); $NoThreadquery = mysql_query("SELECT * FROM threads where threadid='".$Threaddata['threadid']."'") or die(mysql_error()); $NoThreadComments = mysql_fetch_array($NoThreadquery); ?> <tr valign="top"> <td width="15" align="center" valign="top"><span style="font-family: 'Palatino Linotype'; font-size: 14px; color: rgb(255, 255, 255);">+</span></td> <td width="353"><span style="font-family: 'Palatino Linotype'; font-size: 12px;"><a href="postdetails.php?thread=<?=$NoThreadComments['threadid'];?>&type=<?=$NoThreadComments['categoryid'];?>" rel="nofolloow" style="text-decoration: none; color: rgb(51, 102, 153);"><?=$NoThreadComments['title'];?></a><span style="color: rgb(143, 71, 143);"><br> by <?=$NoThreadComments['username'];?></span> <span style="color: rgb(237, 243, 254);"><?=$Threaddata['count'];?> comment(s)</span></span></td> </tr> </tbody></table> PHP: here is the code
So I don't get what you have there.. categories, threads and comments. Explain exactly what you need because it does seem like i don't understand.. what do you need to be arrange.. and how..?
right now, it shows threads from all categories, can you help me to get the threads of only one category to show. category id's are; Travel entertainment others
You simply need to add a WHERE parameter for your $Threadquery MySQL queries. For example, instead of $Threadquery = mysql_query("SELECT * FROM threads ORDER BY threadid DESC LIMIT $count , 1 ") or die(mysql_error()); Code (markup): You need to have $Threadquery = mysql_query("SELECT * FROM threads WHERE category='travel' ORDER BY threadid DESC LIMIT $count , 1 ") or die(mysql_error()); Code (markup): Replace 'category' with the name of your categories row, and 'travel' with the category name.
You mean, the number of comments disappear? You probably also need to add WHERE category='travel' for your second $Threadquery variable as well. Otherwise, I'm not sure how that's happening. Post the new code if that doesn't work.
$Threadquery = mysql_query("SELECT threadid, count(threadid) as count FROM comments GROUP BY threadid ORDER BY count DESC LIMIT $count , 1 ") or die(mysql_error()); PHP: for this code how will i be able to add category:travel
For The above one its working but for code below its not working, i tried as u advised and an error comes.
Well first of all why do you have 2 tables? What do they do? In which table is the number of comments not showing?
in the last table its not showing the no of comments, dont worry abt the first one.. i have removed it. its this code i am not able to integrate category.
You can't integrate category to that query because it seems that your comments table doesn't have a category column, so you need to change your query to select the thread IDs from the threads table first, like you did it in the first table. I don't understand why you just didn't use the first table, or use the queries from the first table since it seems like they practically do the same thing.
yes thats correct, the first table shows the latest threads from (ex: travel category) but second one shows most commented. ( the one with most commented in travel category will show) yes the code works for both of them, but the in the second table one thing is missing when loaded. ( ex: 19 comments) the comment value is not there when i add the code u gave