Trying to get a count of initial instances...

[Jim R]

I'm trying to get a count of how many topics someone starts in a forum.  I use XenForo, and they only count Posts, but I can order Posts by post_id and group those posts by thread_id to determine the initial instance of that topic.  The problem I'm having is output.

I'm getting the right User info, but the output is offset by one row:  

User 1 | 0 topics (because $c=0)

User 2 | 128 topics (but those are User 1's #)

User 3 | 0 topics (but those are User 2's #)

User 4 | 141 topics (but those are User 3's #)

etc

 

I realize as I play through the logic of the code, it's printing in that order.  I can't seem to get the right order.  I've tried various ways, with a couple of extra IF statements in there too.  

 

$query = "select thread_id,user_id,username from 
(
select * from xf_post 
order by post_id
) x
group by thread_id
order by username,thread_id";

$results = mysqli_query($con,$query);
		echo mysqli_error($con);
		
		$c=0;
		$currentID = false; 

			while($line = mysqli_fetch_assoc($results)) {
				
				
				if ($currentID != $line['user_id']) {
				
							echo '<div>' . $line['username'] . ', ' . $c;

							$currentID = $line['user_id'];
							$c=0;
				}	
				else {
					$c++;
				}
					
			}

 

[Jim R]

Never mind...figured that part out....

Print the first half of what I need (the User Name) in the main IF loop, then add the count after there is a change but before the User name and count are reset.

 

$query = "select thread_id,user_id,username from 
(
select * from xf_post 
order by post_id
) x
group by thread_id
order by username,thread_id";

$results = mysqli_query($con,$query);
		echo mysqli_error($con);
		
		$currentID= false; 

			while($line = mysqli_fetch_assoc($results)) {
				
				
				if ($currentID != $line['user_id']) {
				
					if ($currentID != false) {
							echo $c . '</div>';
							$c=0;
					}

					$currentID = $line['user_id'];
					echo '<div>' . $line['username'] . ', ';
					$c++;


				}	
				else {
					$c++;
				}
			
				
			}

 

Edited September 13, 2019 by Jim R

[Jim R]

Here is my other question.  If I want to list the User names in the order of how many topics they have started, I likely have to use count() in the query, right?  

I didn't wrap my head around the syntax of that.

[requinix]

Create a view for yourself that shows threads and the initial posts. It'll make life easier. Though I'm really skeptical that XenForo doesn't have a way to get that information sort of finding the first post for a given thread ID - after all, since there is an ID in the first place, surely there is some source generating that ID, right?

Once you have the view the query to find users is trivial.

[Barand]

Try

SELECT username
     , COUNT(*) as topics_started
FROM (
        SELECT user_id, username                           -- find username in record matching first post in each thread
        FROM xf_post
             JOIN (
                    SELECT thread_id                       -- subquery to find time of first post in each thread
                         , MIN(timestamp) as timestamp
                    FROM xf_post
                    GROUP BY thread_id
                  ) firstpost USING (thread_id, timestamp)
     ) firstuser
GROUP BY user_id
ORDER BY topics_started DESC;