Apparently global variable does not store the entire mysqli_query output

Question

I am facing problems when trying to pass the result of mysqli_query by globals, because I can only print on screen data from the first row of the table. Follows the code:

function of class select:

public function select($dataset_name,$entire = True){

    $table = $this->get_table();
    $db_connection = $this->get_db_connection();

    if ($entire == True) {
       $GLOBALS[$dataset_name] = mysqli_query($db_connection,"SELECT * FROM {$table}");
    }else {
      $array = func_get_args();
      unset($array [0]);
      unset($array [1]);
      $columns = implode(",", $array);
      $GLOBALS[$dataset_name] = mysqli_query($db_connection,"SELECT {$columns} FROM {$table}");
    }
  }

function responsible for printing the result (PS: It is unfinished, because missing format the data in a table)

<?php
function list_dataset($dataset_name){
  $num_columns = mysqli_field_count($GLOBALS['connection']) - 1;
  $count = 0;

   while ($array = mysqli_fetch_array($GLOBALS[$dataset_name])) {

    while ($count <= $num_columns ) {
           echo $array[$count].'</br>';

      $count = $count + 1;

    }
  }
}
?>

The result of inserting print_r(array_keys($GLOBALS)); exit; before the function mysqli_fetch_array() is:

 Array ( [0] => _GET [1] => _POST [2] => _COOKIE [3] => _FILES [4] => GLOBALS [5] => db_connection [6] => db_action [7] => mysqli [8] => connection [9] => con [10] => teste [11] => select )

Other information:

• $GLOBALS['connection'] and $GLOBALS[$dataset_name] coincide, as only a connection was made.

• The function list_dataset() has to suit any select, otherwise it loses its meaning completely.

• I call it that:

  $db_connection = new db_connection;
  $db_connection->set_all('localhost','usuário','senha','db');
  $db_connection->open();
  $db_action = new db_action;
  $db_action->set_all('tabela_usuarios');
  $db_action->select('teste');
  $db_action->list_dataset('teste');

Doubt: What am I doing wrong? Am I putting some part of the code in the wrong place?

Answer 1

There are a lot of things wrong, but I will refrain from commenting on the points outside the main purpose of the question. So let’s get to the point.

The function list_dataset(), modify in this way:

function list_dataset($dataset_name){
    /*
    Note o segundo parâmetro. Defini como MYSQLI_ASSOC. Se preferir, modifique para MYSQLI_NUM. A diferença é que MYSQLI_ASSOC retorna um array associativo, ou seja, a chave de cada array será o nome da coluna da tabela. Com MYSQLI_NUM, os nomes das chaves serão numéricas.

    Consulte: http://php.net/manual/en/mysqli-result.fetch-array.php


    */
    while ($data = mysqli_fetch_array($GLOBALS[$dataset_name], MYSQLI_ASSOC)) {
        /*
        id e parent_id são os nomes das colunas da tabela usada para teste.
        modifique para os nomes das colunas da sua tabela 
        */
        echo $data['id'].' - '.$data['parent_id'].'<br>';
    }
}

That’s enough to go on.

If you want something clearer, see a full test:

class Foo {
    public function get_db_connection(){
        return new mysqli('localhost', "root", '', 'test');
    }
    public function select($dataset_name,$entire = True){

    $table = 'foo'; // "foo" é nome da tabela que usei para teste. Se quiser pode continuar usando o $table = $this->get_table(); conforme está originalmente.

    $db_connection = $this->get_db_connection();

    if ($entire == True) {
       $GLOBALS[$dataset_name] = mysqli_query($db_connection,"SELECT * FROM {$table}");
    }else {
      $array = func_get_args();
      unset($array [0]);
      unset($array [1]);
      $columns = implode(",", $array);
      $GLOBALS[$dataset_name] = mysqli_query($db_connection,"SELECT {$columns} FROM {$table}");
    }
  }

}

function list_dataset($dataset_name){
    /*
    $GLOBALS[$dataset_name]->num_rows retorna a quantidade de registros encontrados.
    Toda aquela doidera com $count é desnecessária e era a causa principal do problema.
    */
    if ($GLOBALS[$dataset_name]->num_rows > 0) {
        while ($data = mysqli_fetch_array($GLOBALS[$dataset_name], MYSQLI_ASSOC)) {
            /*
            id e parent_id são os nomes das colunas da tabela usada para teste.
            modifique para os nomes das colunas da sua tabela 
            */
            echo $data['id'].' - '.$data['parent_id'].'<br>';
        }
    } else {
        echo 'nada encontrado';
    }
}

$c = new Foo();
$c->select('connection', true);
list_dataset('connection');

The table I used for tests:

CREATE TABLE `foo` (
  `id` int(1) NOT NULL,
  `parent_id` int(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `foo` VALUES ('1', '0'), ('2', '1'), ('3', '1'), ('4', '1');