Run sql query for use in viewtopic_body.html

[OldSkul]

Ok, here is my problem. I have Filebase mod and I did some database modifications in order to create additional features.

I would like to display number of uploads (in Filebase) by every user in viewtopic_body.html under this line:

Code: Select all

<!-- IF postrow.POSTER_FROM --><dd><strong>{L_LOCATION}:</strong> {postrow.POSTER_FROM}</dd><!-- ENDIF -->

And here is sql query:

Code: Select all

$sql_totaluploads = "SELECT count(uploader) as totaluploads FROM phpbb_filebase WHERE user_id ='";
$row_totaluploads = mysql_fetch_array(mysql_query($sql_totaluploads ));
$user_totaluploads = $row_totaluploads ['totaluploads '];

My problem is that I do not know how and where to put this query and how to deal with sessions. Why with sessions? Becase every user needs to get different value and therefore sql query needs to distinguish between different users.

I hope someone can help me!
Thanks

[DionDesigns]

I obviously can't test with your data, but this should work, assuming you have a user_id column in the database table. Open viewtopic.php and locate the following code:

Code: Select all

// Posts are stored in the $rowset array while $attach_list, $user_cache
// and the global bbcode_bitfield are built
while ($row = $db->sql_fetchrow($result))
{
	// Set max_post_time
	if ($row['post_time'] > $max_post_time)
	{
		$max_post_time = $row['post_time'];
	}

	$poster_id = (int) $row['poster_id'];

Change it to:

Code: Select all

$uploader = array();
// Posts are stored in the $rowset array while $attach_list, $user_cache
// and the global bbcode_bitfield are built
while ($row = $db->sql_fetchrow($result))
{
	// Set max_post_time
	if ($row['post_time'] > $max_post_time)
	{
		$max_post_time = $row['post_time'];
	}

	$poster_id = (int) $row['poster_id'];
	$uploader[] = $poster_id;

Now locate the following code:

Code: Select all

// Output the posts
$first_unread = $post_unread = false;

Change it to:

Code: Select all

$sql = 'SELECT DISTINCT user_id, COUNT(uploader) AS totaluploads FROM phpbb_filebase WHERE ' . $db->sql_in_set('user_id', $uploader) . ' GROUP BY user_id';
$result = $db->sql_query($sql);
while ($row = $db->sql_fetchrow($result)) {
	$uploader_id = (int) $row['user_id'];
	$uploader[$uploader_id] = (int) $row['totaluploads'];
}
$db->sql_freeresult($result);

// Output the posts
$first_unread = $post_unread = false;

And finally, locate this code:

Code: Select all

	$postrow = array(

Change it to:

Code: Select all

	$postrow = array(
		'TOTAL_UPLOADS'		=> $uploader[$poster_id],

The viewtopic_body.html template should now have the upload count for each poster in {postrow.TOTAL_UPLOADS}.

I'd suggest saving a backup copy of viewtopic.php before making these changes...just in case.

[OldSkul]

Oh thanks. Yes i do have user_id (i am manually adding values because i just recently added this column) in filebase. I tried your suggestions (thanks alot) but i am getting these strange errors:

[phpBB Debug] PHP Notice: in file /viewtopic.php on line 1526: Undefined index: 19466
[phpBB Debug] PHP Notice: in file /viewtopic.php on line 1526: Undefined index: 2610
[phpBB Debug] PHP Notice: in file /viewtopic.php on line 1526: Undefined index: 19466
[phpBB Debug] PHP Notice: in file /viewtopic.php on line 1526: Undefined index: 10945
[phpBB Debug] PHP Notice: in file /viewtopic.php on line 1526: Undefined index: 10089
[phpBB Debug] PHP Notice: in file /viewtopic.php on line 1526: Undefined index: 10089
[phpBB Debug] PHP Notice: in file /viewtopic.php on line 1526: Undefined index: 10945
[phpBB Debug] PHP Notice: in file /viewtopic.php on line 1526: Undefined index: 8795
[phpBB Debug] PHP Notice: in file /viewtopic.php on line 1526: Undefined index: 19466
[phpBB Debug] PHP Notice: in file /viewtopic.php on line 1526: Undefined index: 19466

Additional explanation:
uploader column in phpbb_filebase table contains username in text format (OldSkul for example)

EDIT: I checked these numbers: 19466, 2610, etc. All these are user_ids which are apperanlty problematic users. I checked phpbb_users table and all these users have wier looking username value. 19466 user has username value"5461c5a16131" but in reality real username is "Taša1".

EDIT2: I suggest we remove uploader from sql query and only count user_ids. Value "0" should not be counted as some uploaded files (by users) have value 0 in user_id.

[OldSkul]

If i use this query for testing purposes:

Code: Select all

    $sql = "SELECT count(user_id) AS uploads		
        FROM phpbb_filebase		
        WHERE user_id = '2'";
		$result = $db->sql_query($sql);
    $uploader = (int) $db->sql_fetchfield('uploads');		
	$db->sql_freeresult($result);	

And this line under postrow:

Code: Select all

'UPLOADS_NUMBER'      => $uploader,

Every user gets value 521 which is correct. Everything works as expected but as i said i would like that every user gets their value, not value from user with user_id=2.

[DionDesigns]

I think the notice is the variable declaration, so try changing this:

Code: Select all

	$postrow = array(
		'TOTAL_UPLOADS'		=> $uploader[$poster_id],

to this:

Code: Select all

	$postrow = array(
		'TOTAL_UPLOADS'		=> ($uploader[$poster_id]) ? $uploader[$poster_id] : '',

and let me know if things work.

[OldSkul]

Yep, it works! When i turn debugging on, i still get same errors, but now i can see nr. of downloads under every username. One little problem though. Users that havent uploaded anything yet, should have value 0 but now they dont have anything. I guess only minor change is needed for this.

[AmigoJack]

Find:

Code: Select all

   $postrow = array(
      'TOTAL_UPLOADS'      => ($uploader[$poster_id]) ? $uploader[$poster_id] : '', 

Replace with:

Code: Select all

   $postrow = array(
      'TOTAL_UPLOADS'=> isset( $uploader[$poster_id] )? $uploader[$poster_id]: 0, 

[OldSkul]

Thanks a lot guys for free help. Problem is now solved!