fetch_array does not return data

1

I am making a list of users and the data does not appear on the page. I waited for the user's name, his type, and the number of posts and replies 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:

Iusedthequery:

"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:

Issue

Thequeryontheserverworked,butitdidnotappearonthepage:

(Edition)

    
asked by anonymous 25.05.2015 / 19:29

2 answers

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 . , however your php.ini settings should be blocking your use.

  

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

     

Note: <?= and <? echo have the same result, but are different, ie if short_open_tag is turned 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>
    
26.05.2015 / 00:12
2

Since you are using the COUNT function, it groups the results together. So you need to add a GROUP BY at the end of the SELECT by the id of the user. Try this:

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 
    
25.05.2015 / 19:35