Davie33 Posted August 16, 2012 Share Posted August 16, 2012 Hi can anyone tell me how i make a code that will give me the total of topics and posts made please. This is a code i made show them seperate from each other now i would like to know how to get them both as one. What i mean is say Davie made 2 topics and 7 posts would = 9 <div class="boxheader">Top Topic Posters</div> <div class="middlewrapperbox"> <?php $query = yasDB_select("SELECT * FROM user order by topics desc limit 5",false); while ($row = $query->fetch_array(MYSQLI_ASSOC)) { $id = $row['id']; $username = $row['username']; $topic = $row['topics']; if ( $setting['seo']=='yes' ) { $memberlink = $setting['siteurl'].'showmember/'.$id.'.html'; } else { $memberlink = $setting['siteurl'] . 'index.php?act=showmember&id='.$id; } echo'<a href="'.$memberlink.'">'.$username.'</a> '.$topic.' topics<br>'; } $query->close(); ?> </div> <div class="boxheader">Top Posters</div> <div class="middlewrapperbox"> <?php $query = yasDB_select("SELECT * FROM user order by posts desc limit 5",false); while ($row = $query->fetch_array(MYSQLI_ASSOC)) { $id = $row['id']; $username = $row['username']; $posts = $row['posts']; if ( $setting['seo']=='yes' ) { $memberlink = $setting['siteurl'].'showmember/'.$id.'.html'; } else { $memberlink = $setting['siteurl'] . 'index.php?act=showmember&id='.$id; } echo'<a href="'.$memberlink.'">'.$username.'</a> '.$posts.' posts<br>'; } $query->close(); ?> </div> Quote Link to comment https://forums.phpfreaks.com/topic/267177-getting-the-total-number/ Share on other sites More sharing options...
MMDE Posted August 16, 2012 Share Posted August 16, 2012 Could you explain to me why creating a thread does not involve also creating a post, hence you should be able to just count posts? But I see what you want... or at least I think so... SELECT * FROM user order by topics+posts desc limit 5 ^ Just tested on one of my own MySQL databases, so yeah, should work. Quote Link to comment https://forums.phpfreaks.com/topic/267177-getting-the-total-number/#findComment-1369970 Share on other sites More sharing options...
xyph Posted August 16, 2012 Share Posted August 16, 2012 Could you explain to me why creating a thread does not involve also creating a post, hence you should be able to just count posts? But I see what you want... or at least I think so... SELECT * FROM user order by topics+posts desc limit 5 ^ Just tested on one of my own MySQL databases, so yeah, should work. This requires the MySQL server to add those two columns to EVERY ROW in the table, in order to sort before LIMITing. It's NOT efficient, but probably the easiest solution to the way you've decided to design your database. Quote Link to comment https://forums.phpfreaks.com/topic/267177-getting-the-total-number/#findComment-1369983 Share on other sites More sharing options...
Davie33 Posted August 17, 2012 Author Share Posted August 17, 2012 Could you explain to me why creating a thread does not involve also creating a post, hence you should be able to just count posts? But I see what you want... or at least I think so... SELECT * FROM user order by topics+posts desc limit 5 ^ Just tested on one of my own MySQL databases, so yeah, should work. It didn't work as i don't know what to do for the rest of the code hence "topic+posts" in the query. What am trying to get is topics and post count together Here is what shows on my site the now with them two codes i showed you but would like them joined to give me the total. I have set it this for top topic posters by how many topics they have man and same goes for top posters by how many posts are made. Top Topic Posters slippers 2 topics babybliss035 1 topics Thomas Teig 0 topics Jennifer 0 topics Top Posters babybliss035 1 posts slippers 3 posts Thomas Teig 0 posts Jennifer 0 posts code i would like to see looking like this. Top Posters babybliss035 2 posts slippers 5 posts as in total topics and posts Thomas Teig 0 posts Jennifer 0 posts @ xyph thanks for the reply wish i know how to do it.Its been bugging me for a week. Quote Link to comment https://forums.phpfreaks.com/topic/267177-getting-the-total-number/#findComment-1370222 Share on other sites More sharing options...
xyph Posted August 17, 2012 Share Posted August 17, 2012 Since you don't actually store this value, you're going to have to add the two for EVERY user in order to calculate the values. It's not going to scale well, and I'd suggest you redesign your database, where topics and posts are considered the same. If a 'post' needs to be considered a topic, set a flag of some sort. SELECT username, (posts+topics) as total FROM user ORDER BY (posts+topics) DESC I can't make many more suggestions, because I don't know how your application works. If you don't want to redesign your database, it would probably be smart to store the value of posts+topics as a 3rd column in your table. Quote Link to comment https://forums.phpfreaks.com/topic/267177-getting-the-total-number/#findComment-1370224 Share on other sites More sharing options...
Davie33 Posted August 17, 2012 Author Share Posted August 17, 2012 Since you don't actually store this value, you're going to have to add the two for EVERY user in order to calculate the values. It's not going to scale well, and I'd suggest you redesign your database, where topics and posts are considered the same. If a 'post' needs to be considered a topic, set a flag of some sort. SELECT username, (posts+topics) as total FROM user ORDER BY (posts+topics) DESC I can't make many more suggestions, because I don't know how your application works. If you don't want to redesign your database, it would probably be smart to store the value of posts+topics as a 3rd column in your table. I see what you mean but for (posts+topics) in query how would i echo that to show on web page ?. Quote Link to comment https://forums.phpfreaks.com/topic/267177-getting-the-total-number/#findComment-1370226 Share on other sites More sharing options...
xyph Posted August 17, 2012 Share Posted August 17, 2012 $result = mysql_query($query); if( !$result ) { echo 'query error'; } else { while( $row = mysql_fetch_assoc($result) ) { print_r($row); } } Quote Link to comment https://forums.phpfreaks.com/topic/267177-getting-the-total-number/#findComment-1370227 Share on other sites More sharing options...
Davie33 Posted August 17, 2012 Author Share Posted August 17, 2012 $result = mysql_query($query); if( !$result ) { echo 'query error'; } else { while( $row = mysql_fetch_assoc($result) ) { print_r($row); } } That code gives out Array ( [username] => babybliss [total] => 0 ) on web page here is what i did but not really what am looking for <?php $query = yasDB_select("SELECT username, (posts+topics) as total FROM user ORDER BY (posts+topics) DESC limit 5",false); $result = $query->fetch_array(MYSQLI_ASSOC); if( !$result ) { echo 'query error'; } else { while($row = $query->fetch_array(MYSQLI_ASSOC)) { print_r($row); } } $query->close(); ?> Quote Link to comment https://forums.phpfreaks.com/topic/267177-getting-the-total-number/#findComment-1370229 Share on other sites More sharing options...
xyph Posted August 17, 2012 Share Posted August 17, 2012 You're gonna have to do a little research on your own here. I'm not going to hold your hand through the basics of the language, nor am I going to finish this for you. I've provided you with enough information to get started. Quote Link to comment https://forums.phpfreaks.com/topic/267177-getting-the-total-number/#findComment-1370230 Share on other sites More sharing options...
Davie33 Posted August 17, 2012 Author Share Posted August 17, 2012 Hold my hand no thanks, but thank you for the info it has helped as your query's do work. Quote Link to comment https://forums.phpfreaks.com/topic/267177-getting-the-total-number/#findComment-1370232 Share on other sites More sharing options...
Davie33 Posted August 17, 2012 Author Share Posted August 17, 2012 This is what i have which works just in case someone else has the same problem as me . <?php $query = yasDB_select("SELECT username, (posts+topics) as total FROM user ORDER BY (posts+topics) DESC limit 5",false); $result = $query; if( !$result ) { echo 'query error'; } else { while($row = $query->fetch_array(MYSQLI_ASSOC)) { $username = $row['username']; $total = $row['total']; if ( $setting['seo']=='yes' ) { $memberlink = $setting['siteurl'].'showmember/'.$id.'.html'; } else { $memberlink = $setting['siteurl'] . 'index.php?act=showmember&id='.$id; } echo'<a href="'.$memberlink.'">'.$username.'</a> '.$total.' posts<br>'; } } $query->close(); ?> Quote Link to comment https://forums.phpfreaks.com/topic/267177-getting-the-total-number/#findComment-1370235 Share on other sites More sharing options...
xyph Posted August 17, 2012 Share Posted August 17, 2012 Glad I could help, you should take the other advice into consideration. The solution you've implemented will NOT SCALE. Quote Link to comment https://forums.phpfreaks.com/topic/267177-getting-the-total-number/#findComment-1370238 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.