fetch_array does not return data

Asked

Viewed 159 times

1

I am making a user list and the data does not appear on the page. I waited for the user’s name, his type and the number of posts and responses he sent to the system to appear. There is another problem. Three users are registered, but only one appears. Below is the code:

    <?php

        $conecta =  new MySQLi("localhost","root","","forum-ti-1");

    ?>


    <!DOCTYPE html>
    <html>
    <head>
    <meta charset="utf-8">
            <meta http-equiv="X-UA-Compatible" content="IE=edge">
            <meta name="viewport" content="width=device-width, initial-         scale=1">
    <title></title>
            <link rel="stylesheet" href="css/style.css">
            <script src="js/script.js"></script>
  </head>
  <body>
    <?php
                include('templates/header.html.php');
    ?>
                <div class="head_topics" style="top: 330px; left: 210px;">
                    <h2>Usuários</h2>
                </div>

                    <table class="t_subtopics" style=" top: 370px; left:210px;">
                            <?php
                                $usuario = "SELECT usuario.nome, situacao_usuario.descricao, COUNT(discussao.titulo) As posts, COUNT(resposta.conteudo) AS respostas 
                                FROM usuario, situacao_usuario, discussao, resposta 
                                WHERE usuario.id_situacao = situacao_usuario.id AND usuario.id = discussao.id_usuario AND usuario.id = resposta.id_usuario";
                                $lista = $conecta->query($usuario);
                                while($rs = $lista->fetch_array()){

                            ?>
                            <tr>
                                <td style="width: 200px;">
                                    <div class="discussion_photo">
                                        <img src="images/profile-photo.jpg" style="height: 80px; width: 100px; display: block;"/>
                                    </div>
                                </td>
                                <td style="width: 500px;">
                                    <ul>
                                        <li><h3 style="font-size: 20px;"><? echo $rs['usuario.nome']; ?></h3></li>
                                        <li id="discussion_type_user" style="width: 80px; padding: 5px;"><? echo $rs['situacao_usuario.descricao']; ?></li>
                                    </ul>
                                </td>
                                <td style="width: 200px;">
                                    <ul>
                                        <li><? echo $rs['posts']; ?> tópicos</li>
                                        <li><? echo $rs['respostas']; ?> respostas</li>
                                    </ul>
                                </td>
                            </tr>
                            <?php

                                }

                            ?>

                    </table>
            <?php
                include('templates/footer.html.php');
    ?>
</body>

The figure below shows the result:

inserir a descrição da imagem aqui

I used the consultation: 

     "SELECT usuario.nome, situacao_usuario.descricao, COUNT(discussao.titulo) As posts, COUNT(resposta.conteudo) AS respostas 
                                                        FROM usuario, situacao_usuario, discussao, resposta 
                                                        WHERE usuario.id_situacao = situacao_usuario.id 
                                                        AND usuario.id = discussao.id_usuario 
                                                        AND usuario.id = resposta.id_usuario
                                                        GROUP BY usuario.id"

but did not return any data:

inserir a descrição da imagem aqui

Editing

The server query worked, but did not appear on the page:

inserir a descrição da imagem aqui

(Editing)

php mysql sql mysqli

  • mysqli_error() returns something?

  • No, it does not return.

  • if you run sql directly in the database, returns something?

  • "If you run sql directly in the database, does it return anything? - Euler01 " No, I’ll fix that.

  • Nothing keeps popping up.

  • Try it like this $conecta->query($usuario); var_dump($conecta->error());, note that the error has to be used after query and not before.

  • directly in the database works sql?

  • "directly in the database works sql? - Euler01 3 mins ago" No.

  • " Try $connect->query($user); var_dump($connects->error());, note that the error has to be used after the query and not before. - Guilherme Nascimento 5 minutes ago " .

  • If you are not returning a record running directly in the database, the problem is in SQL. Check if there is record in all tables used in sql. If the user’s id_status exists in the table of situacao_usuario, if the user has record in the discussion.

  • I changed the SQL of my answer, try running it in the database and tell me if you returned anything.

  • Fatal error: Call to a Member Function fetch_array() on a non-object The problem is with fetch_array()...

  • Query is wrong in this part "LEFT JOIN reply ON user.id = answer.id_user GROUP BY user"'

  • Yes, of course. had a comma after the excerpt "ON user.id = discussao.id_usuario" removes it.

  • Luana, if my answer solved your problem, mark it as correct, Otherwise, tell them it didn’t work. I noticed that you have several questions with answers, but you did not accept any, please accept the answers that solved your problems, the community please.

Show 10 more comments

2 answers

2

As you are using the COUNT function, it groups the results. That’s why you need to add at the end of SELECT a GROUP BY by the user id. Try it this way:

SELECT 
  usuario.nome,
  situacao_usuario.descricao,
  COUNT(discussao.titulo) AS posts,
  COUNT(resposta.conteudo) AS respostas 
FROM  usuario 
  LEFT JOIN situacao_usuario    ON usuario.id_situacao = situacao_usuario.id 
  LEFT JOIN discussao       ON usuario.id = discussao.id_usuario
  LEFT JOIN resposta        ON usuario.id = resposta.id_usuario 
GROUP BY usuario.id

1


I think I found the problem, you’re using <?, but you should be using <?php

This is because you are trying to use short_open_tag, but your php.ini settings must be blocking your use.

Note: From PHP 5.4.0 to tag <?= is always available, regardless of settings in php.ini.

Note: <?= and <? echo has the same result, but are different, ie if the short_open_tag is off <? echo will not work, but <?= will work.

You can use:

<li><?php echo $rs['posts']; ?> tópicos</li>

or

<li><?=$rs['posts']?> tópicos</li>

Follow the corrected code:

<?php
    $conecta =  new MySQLi("localhost","root","","forum-ti-1");
?><!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
        <meta http-equiv="X-UA-Compatible" content="IE=edge">
        <meta name="viewport" content="width=device-width, initial-         scale=1">
        <title></title>
        <link rel="stylesheet" href="css/style.css">
        <script src="js/script.js"></script>
</head>
<body>
<?php
            include('templates/header.html.php');
?>
                <div class="head_topics" style="top: 330px; left: 210px;">
                    <h2>Usuários</h2>
                </div>

                    <table class="t_subtopics" style=" top: 370px; left:210px;">
                            <?php
                                $usuario = "SELECT usuario.nome, situacao_usuario.descricao, COUNT(discussao.titulo) As posts, COUNT(resposta.conteudo) AS respostas 
                                FROM usuario, situacao_usuario, discussao, resposta 
                                WHERE usuario.id_situacao = situacao_usuario.id AND usuario.id = discussao.id_usuario AND usuario.id = resposta.id_usuario";
                                $lista = $conecta->query($usuario);
                                while($rs = $lista->fetch_array()){

                            ?>
                            <tr>
                                <td style="width: 200px;">
                                    <div class="discussion_photo">
                                        <img src="images/profile-photo.jpg" style="height: 80px; width: 100px; display: block;"/>
                                    </div>
                                </td>
                                <td style="width: 500px;">
                                    <ul>
                                        <li><h3 style="font-size: 20px;"><?php echo $rs['usuario.nome']; ?></h3></li>
                                        <li id="discussion_type_user" style="width: 80px; padding: 5px;"><?php echo $rs['situacao_usuario.descricao']; ?></li>
                                    </ul>
                                </td>
                                <td style="width: 200px;">
                                    <ul>
                                        <li><?php echo $rs['posts']; ?> tópicos</li>
                                        <li><?php echo $rs['respostas']; ?> respostas</li>
                                    </ul>
                                </td>
                            </tr>
                            <?php

                                }

                            ?>

                    </table>
            <?php
                include('templates/footer.html.php');
    ?>
</body>

Browser other questions tagged php mysql sql mysqli

You are not signed in. Login or sign up in order to post.