Problem with select php mysql

Question

I’m with a problem I’ve never seen in php, I’ll try to pass an example to make it easier to understand.

I have a table arquivos in this table I have 2 columns and they are the id do arquivo and the id do usuário (to whom this file belongs).

The user of id 8 is connected and in the system will list all the files that have the idUsuario 8 who are 4 files related to it only that my method of listing files always omitted an example file are 4 files registered with the idUsuario 8 it does not list me 4 files it is listing me 3 or is a file it does not show me, if I add another file to the user of id 8 getting 5 files related to user 8, it will list me 4 instead of listing the 5.

by interpreting the output it is taking the amount of data that a given user has and always omitting the smallest table id file

list method

public function listarArquivo(){
    try{
        $id_usuario = $this->id;

        $query = new DbFunctions();
        $query->selectFiles("SELECT * FROM arquivos WHERE idUsuario = '$id_usuario'");

        //var_dump($id_usuario);
        foreach($query->getResult() as $chave => $dado){
            echo "<tr>
            <td>".$dado->id_arquivo."</td>
            <td>".$dado->nome."</td>
            <td>".$dado->tamanho."</td>
            <td>".$dado->data."</td>
            <td>
                <a href='visualizar_arquivo.php?acao=visualizar&id_arquivo=".$dado->id_arquivo."'target='_blank'><i class='fa fa-external-link fa-2x' aria-hidden='true'></i></a>
                <a href='visualizar_arquivo.php?acao=download&id_arquivo=".$dado->id_arquivo."'target='_blank'><i class='fa fa-cloud-download fa-2x' aria-hidden='true'></i></a>
                <a href='?link1=".$dado->id_arquivo."' name='link1' ><i class='fa fa-trash fa-2x' aria-hidden='true'></i></a>
            </td>";
        }
        //$query->getResult();
        echo "<pre>";
        var_dump($query->getResult());
        echo "</pre>";

    }catch(PDOException $e){
        echo $e->getMessage();
    }
}

Method selectFiles:

public function selectFiles($sql){
    $query = $this->conecta()->query($sql);
    foreach($query as $row){
        $this->setResult($query->fetchAll(\PDO::FETCH_OBJ));
        //$this->setResult($row);
        return true;
    }
}

I don’t know if this problem can be because I’m taking the result of the query and putting it in an object, this object would have a storage size?

I may be a little confused about my problem but I also don’t understand the reason for this problem if anyone can help me I appreciate.

Answer 1

Probably the first line of the result is being omitted because of foreach($query as $row), moving the cursor forward one position in Resultset ($query), making the fetchAll skip the first record, as it has already been processed by foreach. One possible solution is to change the foreach by a if, something like:

public function selectFiles($sql){
    $query = $this->conecta()->query($sql);
    //verifica se foram retornados registros pela consulta
    if($query->rowCount()){
       $this->setResult($query->fetchAll(\PDO::FETCH_OBJ));
       return true;
    }
 }

Answer 2

implementation of selectFiles, getResult and setResult methods

selectFiles method

public function selectFiles($sql){
    $query = $this->conecta()->query($sql);
    foreach($query as $row){
       $this->setResult($query->fetchAll(\PDO::FETCH_OBJ));
       return true;
    }
 }

getResult method

public function getResult(){return $this->result;}

setResult method

private function setResult($r){$this->result = $r;}

Answer 3

The file listing method does not have the closure of the tr.

Correct example

public function listarArquivo(){
    try{
        $id_usuario = $this->id;

        $query = new DbFunctions();
        $query->selectFiles("SELECT * FROM arquivos WHERE idUsuario = '$id_usuario'");

        //var_dump($id_usuario);
        foreach($query->getResult() as $chave => $dado){
            echo "<tr>
            <td>".$dado->id_arquivo."</td>
            <td>".$dado->nome."</td>
            <td>".$dado->tamanho."</td>
            <td>".$dado->data."</td>
            <td>
                <a href='visualizar_arquivo.php?acao=visualizar&id_arquivo=".$dado->id_arquivo."'target='_blank'><i class='fa fa-external-link fa-2x' aria-hidden='true'></i></a>
                <a href='visualizar_arquivo.php?acao=download&id_arquivo=".$dado->id_arquivo."'target='_blank'><i class='fa fa-cloud-download fa-2x' aria-hidden='true'></i></a>
                <a href='?link1=".$dado->id_arquivo."' name='link1' ><i class='fa fa-trash fa-2x' aria-hidden='true'></i></a>
            </td>
          </tr>"; // <------ </tr> que faltava 
        }
        //$query->getResult();
        echo "<pre>";
        var_dump($query->getResult());
        echo "</pre>";

    }catch(PDOException $e){
        echo $e->getMessage();
    }
}