Help with add SQL command to php file

Discussion forum for Extension Writers regarding Extension Development.
Post Reply
Difadon
Registered User
Posts: 55
Joined: Wed Nov 06, 2013 3:36 pm
Location: Špindlerův Mlýn
Name: Jan Nejedlý
Contact:

Help with add SQL command to php file

Post by Difadon »

Hello,

Perhaps you can help me. I created your own page and i need created list of user.

This is my php file:

Code: Select all

<?php
define('IN_PHPBB', true);
$phpbb_root_path = (defined('PHPBB_ROOT_PATH')) ? PHPBB_ROOT_PATH : './';
$phpEx = substr(strrchr(__FILE__, '.'), 1);
include($phpbb_root_path . 'common.' . $phpEx);

// Start session management
$user->session_begin();
$auth->acl($user->data);
$user->setup('hodnosti'); 

page_header('Seznam hodností');

$template->set_filenames(array(
    'body' => 'hodnosti.html',
));

make_jumpbox(append_sid("{$phpbb_root_path}viewforum.$phpEx"));
page_footer();
?>
This is SQL command:

Code: Select all

SELECT username FROM forum_users WHERE user_rank = 1;
And i need a created list of users with rank 1. If multiple users i need use separator , and limit list to 100 records.

Colorized username is very great. :P
User avatar
mrgoldy
Development Team Member
Development Team Member
Posts: 1394
Joined: Tue Oct 06, 2009 7:34 pm
Location: The Netherlands
Name: Gijs
Contact:

Re: Help with add SQL command to php file

Post by mrgoldy »

I'm not too experienced, but perhaps this will work:

php file:

Code: Select all

$sql = 'SELECT username FROM forum_users WHERE user_rank = 1';

$result = $db->sql_query($sql);
$row_count = $result->num_row;

while ($row = $db->sql_fetchrow($result) && $row_count <= 100){
	
	$template->assign_block_vars('rank_block', array(
		'RANK_USERNAME'		=> $row['username'],
	));
}

$db->sql_freeresult($result);
And then in the HTML file you can use:

Code: Select all

<!-- BEGIN rank_block -->
	{rank_block.RANK_USERNAME}
<!-- END rank_block -->
I haven't tested it myself but I think it should work..
phpBB Studio / Member of the Studio

Contributing: You can do it too! Including testing Pull Requests (PR).
phpBB Development and Testing made easy.
Difadon
Registered User
Posts: 55
Joined: Wed Nov 06, 2013 3:36 pm
Location: Špindlerův Mlýn
Name: Jan Nejedlý
Contact:

Re: Help with add SQL command to php file

Post by Difadon »

Thanks, but there is little problem. Do not see any nickname. :(
Difadon
Registered User
Posts: 55
Joined: Wed Nov 06, 2013 3:36 pm
Location: Špindlerův Mlýn
Name: Jan Nejedlý
Contact:

Re: Help with add SQL command to php file

Post by Difadon »

I find a error. I must change code to:

Code: Select all

$sql = 'SELECT username FROM forum_users WHERE user_rank = 1 LIMIT 100';

$result = $db->sql_query($sql);
$row_count = $result->num_row;

while ($row = $db->sql_fetchrow($result)){
   
   $template->assign_block_vars('rank_block', array(
      'RANK_USERNAME'      => $row['username'],
   ));
}

$db->sql_freeresult($result);
But now i need add separator for multiple nickname (no for one nickname) and created a hyperlink from nickname.
User avatar
mrgoldy
Development Team Member
Development Team Member
Posts: 1394
Joined: Tue Oct 06, 2009 7:34 pm
Location: The Netherlands
Name: Gijs
Contact:

Re: Help with add SQL command to php file

Post by mrgoldy »

So you want the username to be linked to their profile?
And a seperator between the usernames?

Change:
$sql = 'SELECT username FROM forum_users WHERE user_rank = 1 LIMIT 100';
to:
$sql = 'SELECT username, user_id, user_colour FROM forum_users WHERE user_rank = 1 LIMIT 100';

Change:
'RANK_USERNAME' => $row['username'],
To:
'USER' = > get_username_string('full', $row['user_id'], $row['username'], $row['user_colour']));

Then the HTML will be:

Code: Select all

<!-- BEGIN rank_block -->
	<!-- IF rank_block.S_FIRST_ROW --><ul><!-- ENDIF -->
   <li>{rank_block.USER}</li>
   <!-- IF rank_block.S_LAST_ROW--></ul><!-- ENDIF -->
<!-- END rank_block -->
Try that.
phpBB Studio / Member of the Studio

Contributing: You can do it too! Including testing Pull Requests (PR).
phpBB Development and Testing made easy.
Difadon
Registered User
Posts: 55
Joined: Wed Nov 06, 2013 3:36 pm
Location: Špindlerův Mlýn
Name: Jan Nejedlý
Contact:

Re: Help with add SQL command to php file

Post by Difadon »

I try this and i have a white page. There is php file:

Code: Select all

<?php
define('IN_PHPBB', true);
$phpbb_root_path = (defined('PHPBB_ROOT_PATH')) ? PHPBB_ROOT_PATH : './';
$phpEx = substr(strrchr(__FILE__, '.'), 1);
include($phpbb_root_path . 'common.' . $phpEx);

// Start session management
$user->session_begin();
$auth->acl($user->data);
$user->setup('hodnosti'); 

page_header('Seznam hodností');

$template->set_filenames(array(
    'body' => 'hodnosti.html',
));

$rank1 = 'SELECT username, user_id, user_colour FROM forum_users WHERE user_rank = 1 LIMIT 100';

$result = $db->sql_query($rank1);
$row_count = $result->num_row;

while ($row = $db->sql_fetchrow($result)){
   
   $template->assign_block_vars('rank_block', array(
      'USER' = > get_username_string('full', $row['user_id'], $row['username'], $row['user_colour']));
   ));
}

$db->sql_freeresult($result);

page_footer();
?>
And it´s possible use separator ,?
User avatar
mrgoldy
Development Team Member
Development Team Member
Posts: 1394
Joined: Tue Oct 06, 2009 7:34 pm
Location: The Netherlands
Name: Gijs
Contact:

Re: Help with add SQL command to php file

Post by mrgoldy »

You can remove the $row_count.
There is a space between the = and the > after 'USER'
And remove one ) from that line aswell ;)

I personally couldnt use the forum_users table and had to use this query:
$rank1 = 'SELECT username, user_id, user_colour FROM '. USERS_TABLE .' WHERE user_rank = 1 LIMIT 100';

, separator

Code: Select all

<!-- BEGIN rank_block -->
  {rank_block.USER}<!-- IF not rank_block.S_LAST_ROW -->,<!-- ENDIF -->
<!-- END rank_block -->
Last edited by mrgoldy on Fri Nov 06, 2015 9:46 pm, edited 1 time in total.
phpBB Studio / Member of the Studio

Contributing: You can do it too! Including testing Pull Requests (PR).
phpBB Development and Testing made easy.
Difadon
Registered User
Posts: 55
Joined: Wed Nov 06, 2013 3:36 pm
Location: Špindlerův Mlýn
Name: Jan Nejedlý
Contact:

Re: Help with add SQL command to php file

Post by Difadon »

Again white page:

Code: Select all

<?php
define('IN_PHPBB', true);
$phpbb_root_path = (defined('PHPBB_ROOT_PATH')) ? PHPBB_ROOT_PATH : './';
$phpEx = substr(strrchr(__FILE__, '.'), 1);
include($phpbb_root_path . 'common.' . $phpEx);

// Start session management
$user->session_begin();
$auth->acl($user->data);
$user->setup('czapp'); 

page_header('Seznam hodností');

$template->set_filenames(array(
    'body' => 'hodnosti.html',
));

$rank1 = 'SELECT username, user_id, user_colour FROM forum_users WHERE user_rank = 1 LIMIT 100';

$result = $db->sql_query($rank1);

while ($row = $db->sql_fetchrow($result)){
   
   $template->assign_block_vars('rank_block', array(
      'USER' => get_username_string('full', $row['user_id'], $row['username'], $row['user_colour']));
   ));
}

$db->sql_freeresult($result);

page_footer();
?>
User avatar
mrgoldy
Development Team Member
Development Team Member
Posts: 1394
Joined: Tue Oct 06, 2009 7:34 pm
Location: The Netherlands
Name: Gijs
Contact:

Re: Help with add SQL command to php file

Post by mrgoldy »

Yeah, sorry, I added in that there is a ")" too many.

Code: Select all

<?php
define('IN_PHPBB', true);
$phpbb_root_path = (defined('PHPBB_ROOT_PATH')) ? PHPBB_ROOT_PATH : './';
$phpEx = substr(strrchr(__FILE__, '.'), 1);
include($phpbb_root_path . 'common.' . $phpEx);

// Start session management
$user->session_begin();
$auth->acl($user->data);
$user->setup('czapp'); 

page_header('Seznam hodností');

$template->set_filenames(array(
    'body' => 'hodnosti.html',
));

$rank1 = 'SELECT username, user_id, user_colour FROM forum_users WHERE user_rank = 1 LIMIT 100';

$result = $db->sql_query($rank1);

while ($row = $db->sql_fetchrow($result)){
   
   $template->assign_block_vars('rank_block', array(
      'USER' => get_username_string('full', $row['user_id'], $row['username'], $row['user_colour']);
   ));
}

$db->sql_freeresult($result);

page_footer();
?>
phpBB Studio / Member of the Studio

Contributing: You can do it too! Including testing Pull Requests (PR).
phpBB Development and Testing made easy.
Difadon
Registered User
Posts: 55
Joined: Wed Nov 06, 2013 3:36 pm
Location: Špindlerův Mlýn
Name: Jan Nejedlý
Contact:

Re: Help with add SQL command to php file

Post by Difadon »

Maybe i´m little stupid.....but page is white. :D
User avatar
mrgoldy
Development Team Member
Development Team Member
Posts: 1394
Joined: Tue Oct 06, 2009 7:34 pm
Location: The Netherlands
Name: Gijs
Contact:

Re: Help with add SQL command to php file

Post by mrgoldy »

Okay, last attempt, turn on some debugger btw, makes it a lot easier..
'USER' => get_username_string('full', $row['user_id'], $row['username'], $row['user_colour']);
TO
'USER' => get_username_string('full', $row['user_id'], $row['username'], $row['user_colour']),

And otherwise try changing the table:
$rank1 = 'SELECT username, user_id, user_colour FROM forum_users WHERE user_rank = 1 LIMIT 100';
to
$rank1 = 'SELECT username, user_id, user_colour FROM '. USERS_TABLE .' WHERE user_rank = 1 LIMIT 100';
phpBB Studio / Member of the Studio

Contributing: You can do it too! Including testing Pull Requests (PR).
phpBB Development and Testing made easy.
Difadon
Registered User
Posts: 55
Joined: Wed Nov 06, 2013 3:36 pm
Location: Špindlerův Mlýn
Name: Jan Nejedlý
Contact:

Re: Help with add SQL command to php file

Post by Difadon »

You are genius. Working!

Thank you very much. :)
Post Reply

Return to “Extension Writers Discussion”