How to run through a column and get all results in an array?

[Lotts]

Hi guys. It's my first time trying to get help and I'm a PHP and phpBB beginner, but I like to do projects while I'm learning, so it's exactly what I'm doing right now, but now I'm stucked. So, I have this table:



And this code in main_listener.php:

Code: Select all

public function add_utils($event)
	{
		$sql = 'SELECT nome_personagem FROM ' . $this->personagens_table . ' WHERE (user_id = ' . (int) $this->user->data['user_id'] . ')';
		$result = $this->db->sql_query($sql);
		$return = array();

		while ($row = $this->db->sql_fetchrow($result))
		{
			$return[] = $row;
		}
		$this->db->sql_freeresult($result);

		$this->template->assign_block_vars('info_contas', array(
			'CONTA_OFF'   => $this->user->data['username'],
			'PERSONAGENS' => $return,
		));
		
		$this->template->assign_vars(array(
			'U_PERFILRPG_CRIARPERSONAGEM' => $this->helper->route('lotts_perfilrpg_route'),
		));
	}

And this is my HTML - Template:

Code: Select all

{% if S_USER_LOGGED_IN %}
<ul class="perfilrpg-top-level-menu">
	<li>
		<a href="#">{{ lang('PERFIL_RPG') }}</a>
		<ul class="perfilrpg-second-level-menu">
			<li>
				<a href="#">{{ lang('POSTAR_COMO') }}</a>
				<ul id="postar-como-sub-menu" class="perfilrpg-third-level-menu">
					{% for conta in info_contas %}
					<li>
						<a href="#" class="postar-como-contas conta-selecionada">{{ conta.CONTA_OFF }}</a>
						<a href="#" class="postar-como-contas">{{ conta.PERSONAGENS }}</a>
						<a href="#" class="postar-como-contas">{{ conta.PERSONAGENS }}</a>
					</li>
					{% endfor %}
				</ul>
			</li>
			<li>
				<a href="#">{{ lang('MEUS_PERSONAGENS') }}</a>
				<ul class="perfilrpg-third-level-menu">
					{# Popular aqui com personagens #}
				</ul>
			</li>
			<li><a href="{U_PERFILRPG_CRIARPERSONAGEM}">{{ lang('CRIAR_PERSONAGEM') }}</a></li>
		</ul>
	</li>
</ul>
{% endif %}

This code displays:



What I'm trying to do is: run through 'nome_personagem' column at the 'personagens_table' and get ALL rows that match with WHERE condition. But I can't get it, when I go to my forum "{{ conta.PERSONAGENS }}" displays 'Array' instead of 'Personagem 1' and below 'Personagem 2'. This is not my first code trying to do this, few minutes ago I wrote a code that worked partially (conta.PERSONAGENS displayed 'Personagem 1' in both <a> tag, but it's not what I want too). Could anyone help me? Thank you.

[Kailey]

Code: Select all

		while ($row = $this->db->sql_fetchrow($result))
		{
			$return[] = $row;
		}
		$this->db->sql_freeresult($result);

		$this->template->assign_block_vars('info_contas', array(
			'CONTA_OFF'   => $this->user->data['username'],
			'PERSONAGENS' => $return,
		));

You're calling an array ($return) in your block_vars. You need to modify accordingly. Something like

Code: Select all

		while ($row = $this->db->sql_fetchrow($result))
		{
			$this->template->assign_block_vars('info_contas', array(
				'CONTA_OFF'   => $this->user->data['username'],
				'PERSONAGENS' => $row,
			));
		}
		$this->db->sql_freeresult($result);

[Lotts]

Thank you so much, kinerity! I've changed some parts of the code, but your answer was the key, no doubt that.

[martti]

You're calling an array ($return) in your block_vars. You need to modify accordingly. Something like

Code: Select all

		while ($row = $this->db->sql_fetchrow($result))
		{
			$this->template->assign_block_vars('info_contas', array(
				'CONTA_OFF'   => $this->user->data['username'],
				'PERSONAGENS' => $row,
			));
		}
		$this->db->sql_freeresult($result);

$row is still an array isn't it?
More like:

Code: Select all

		
		while ($row = $this->db->sql_fetchrow($result))
		{
			$this->template->assign_block_vars('info_contas', array(
				'PERSONAGENS' => $row['nome_personagem'],
			));
		}
		$this->db->sql_freeresult($result);
		
// CONTA_OFF is always the same, so can be outside the loop:

$this->template->assign_vars(array(
	'CONTA_OFF'	=> $this->user->data['username'],
	'U_PERFILRPG_CRIARPERSONAGEM' => $this->helper->route('lotts_perfilrpg_route'),
));

Or because we fetch only one field, we can use the method fetchfield:

Code: Select all

		
		while ($nome_personagem = $this->db->sql_fetchfield('nome_personagem'))
		{
			 $this->template->assign_block_vars('info_contas', array(
				'PERSONAGENS' => $nome_personagem,
			));
		}
		$this->db->sql_freeresult($result);

From phpbb\db\driver\driver_interface:

Code: Select all

	/**
	* Fetch field
	* if rownum is false, the current row is used, else it is pointing to the row (zero-based)
	*
	* @param	string	$field		Name of the column
	* @param	mixed	$rownum		Row number, if false the current row will be used
	*								and the row curser will point to the next row
	*								Note: $rownum is 0 based
	* @param	mixed	$query_id	Already executed query to get the rows from,
	*								if false, the last query will be used.
	* @return	mixed		String value of the field in the selected row,
	*						false, if the row does not exist
	*/
	public function sql_fetchfield($field, $rownum = false, $query_id = false);

	/**
	* Fetch current row
	*
	* @param	mixed	$query_id	Already executed query to get the rows from,
	*								if false, the last query will be used.
	* @return	mixed		Array with the current row,
	*						false, if the row does not exist
	*/
	public function sql_fetchrow($query_id = false);

[Kailey]

$row is still an array isn't it?

Yes, $row is an array but since it's in a while loop, it should go through each iteration.

[Lotts]

I think this code is a bit confusing haha. Yesterday I changed almost the whole code and add some stuffs to reach my expectations.

[martti]

It's not at all confusing.

Yes, $row is an array but since it's in a while loop, it should go through each iteration.

while(expr) in itself hasn't anything to do with arrays. See the PHP manual

Code: Select all

while(expr){
// code in loop
}

When expr casts to true, the code in loop is processed and the program counter jumps back to test the expr again. Whenever expr casts to false the code in loop is ignored.

[3Di]

I think this code is a bit confusing haha. Yesterday I changed almost the whole code and add some stuffs to reach my expectations.

Care to share? For the sake of the posterity I mean.

[Lotts]

Care to share? For the sake of the posterity I mean.

No, not at all. As I said before: I'm really beginner in programming languages such as in phpBB, so things can be wrong or not good, but that's the way I could reach what I was trying to do. So here is my code (don't mind if some variables are in english and others are in pt-BR ):

main_listener.php:

Code: Select all

	public function add_variables_and_links($event)
	{
		$this->template->assign_vars(array(
			'CREATE_CHARACTER_LINK' => $this->helper->route('lotts_perfilrpg_create_character'),
		));
		foreach ($this->general_functions->get_username() as $all_accounts)
		{
			$this->template->assign_vars(array(
				'CONTA_OFF' => $all_accounts,
			));
		}
		
		//get_all_characters() and get_username() functions return an array with infos I need
		foreach ($this->general_functions->get_all_characters() as $all_characters)
		{
			foreach ($this->general_functions->get_username() as $all_accounts)
			{
				$this->template->assign_block_vars('characters_info', array(
					'MY_CHARACTER_LINK' => $this->helper->route('lotts_perfilrpg_my_character', array(
						'account_name'    => preg_replace('/\s+/', '_', $all_accounts),
						'character_name'  => preg_replace('/\s+/', '_', $all_characters['character_name'])
					)),
					'A_TAG_CHARACTERS'    => $all_characters['character_name'],
				));
			}
		}
	}

template.html:

Code: Select all

{% if S_USER_LOGGED_IN %}
<ul class="perfilrpg-top-level-menu">
	<li>
		<a href="#">{{ lang('PERFIL_RPG') }}</a>
		<ul class="perfilrpg-second-level-menu">
			<li>
				<a href="#">{{ lang('POSTAR_COMO') }}</a>
				<ul id="postar-como-sub-menu" class="perfilrpg-third-level-menu">
					<li><a href="#" class="postar-como-contas conta-selecionada">{{ CONTA_OFF }}</a></li>
					{% for character in characters_info %}
					<li><a href="#" class="postar-como-contas">{{ character.A_TAG_CHARACTERS }}</a></li>
					{% endfor %}
				</ul>
			</li>
			<li>
				<a href="#">{{ lang('MEUS_PERSONAGENS') }}</a>
				<ul class="perfilrpg-third-level-menu">
					<form id="my_character_menu_form" name="my_character_menu_form" method="post">
					{% for character in characters_info %}
					<input class="perfilrpg-third-level-menu-my-character" type="submit" name="select_character" value="{{ character.A_TAG_CHARACTERS }}" formaction="{{ character.MY_CHARACTER_LINK }}"/>
					{% endfor %}
					</form>
				</ul>
			</li>
			<li><a href="{{ CREATE_CHARACTER_LINK }}">{{ lang('CRIAR_PERSONAGEM') }}</a></li>
		</ul>
	</li>
</ul>
{% endif %}