Getting the total number

[Davie33]

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>

[MMDE]

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.

[xyph]

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.

[Davie33]

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.

[xyph]

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.

[Davie33]

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 ?.

[xyph]

$result = mysql_query($query);
if( !$result ) {
  echo 'query error';
} else {
  while( $row = mysql_fetch_assoc($result) ) {
    print_r($row);
  }
}

[Davie33]

$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();
?>

[xyph]

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.

[Davie33]

Hold my hand no thanks, but thank you for the info it has helped as your query's do work.

[Davie33]

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();
?>

[xyph]

Glad I could help, you should take the other advice into consideration. The solution you've implemented will NOT SCALE.