Run sql query for use in viewtopic_body.html

Discussion forum for MOD Writers regarding MOD Development.
Locked
OldSkul
Registered User
Posts: 24
Joined: Wed Jan 02, 2008 6:11 pm

Run sql query for use in viewtopic_body.html

Post by 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
Last edited by Oyabun1 on Sat Jan 26, 2013 8:49 pm, edited 1 time in total.
Reason: Moved from Support
User avatar
DionDesigns
Registered User
Posts: 515
Joined: Sun Feb 26, 2012 11:22 pm
Location: Uncertain due to momentum.
Contact:

Re: Run sql query for use in viewtopic_body.html

Post by 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
Registered User
Posts: 24
Joined: Wed Jan 02, 2008 6:11 pm

Re: Run sql query for use in viewtopic_body.html

Post by 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
Registered User
Posts: 24
Joined: Wed Jan 02, 2008 6:11 pm

Re: Run sql query for use in viewtopic_body.html

Post by 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.
User avatar
DionDesigns
Registered User
Posts: 515
Joined: Sun Feb 26, 2012 11:22 pm
Location: Uncertain due to momentum.
Contact:

Re: Run sql query for use in viewtopic_body.html

Post by 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
Registered User
Posts: 24
Joined: Wed Jan 02, 2008 6:11 pm

Re: Run sql query for use in viewtopic_body.html

Post by 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.
User avatar
AmigoJack
Registered User
Posts: 5836
Joined: Tue Jun 15, 2010 11:33 am
Location: グリーン ヒル ゾーン
Contact:

Re: Run sql query for use in viewtopic_body.html

Post by 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, 
  • The worst thing about censorship is ███████████
  • "The problem is probably not my English but you do not want to understand correctly. ... We will not come anybody anyway, nevertheless, it's best to shit this." Affin, 2018-11-20
  • "But this shit is not here for you. You can follow with your. Maybe the question, instead, was for you, who know, so you shoved us how you are." axe70, 2020-10-10
OldSkul
Registered User
Posts: 24
Joined: Wed Jan 02, 2008 6:11 pm

Re: Run sql query for use in viewtopic_body.html

Post by OldSkul »

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

Return to “[3.0.x] MOD Writers Discussion”