Query with array returning only first record

Question

Query with array returning only first record

Navigation

#1 by (1 votes)

0

I have the following querys:

   <?php 

   $VarMensagem = 1;

   $pdo     = new PDO($dsn, $un, $pwd, $opt);
   $data          = array();
   $dataGeral     = array();

   try {
      $stmt    = $pdo->query("SELECT * FROM mensagem WHERE mensagem_id ='{$VarMensagem}'");

      while($row  = $stmt->fetch(PDO::FETCH_OBJ))

      {


         $data[] = $row;

           $VarMinhasResp   =  $data[]=$row->respostas;



      $QueryRespostas    = $pdo->query("SELECT id AS ID,descricao AS DESCRICAO FROM respostas WHERE id IN ('{$VarMinhasResp}')");

      while($row  = $QueryRespostas->fetchall(PDO::FETCH_OBJ))
      {

         $dataGeral['respostas'] = $row;

         }


      }


$result = array_merge($data , $dataGeral);



echo json_encode($result);



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

What I do, I go to the table messages and get the answers id and I make a query in the answers, this is working, but this is only returning the first record, it follows the query, which runs perfectly in mysql: / p>

SELECT id AS ID,descricao AS DESCRICAO FROM respostas WHERE id IN (1,5,10,11,15)

I can not see where the error is, whether it's in the loop or in the array, what can it be?

php mysql pdo array select

asked by anonymous 14.03.2017 / 04:23

1 answer

Why does not Clang recognize the basic header (iostream)? Real time with javascript or jquery?

score 1 · Accepted Answer

If you really want to use PHP to create IN , you can use fetchAll and use implode to put everything together.

$stmt = $pdo->query("SELECT respostas FROM mensagem WHERE mensagem_id ='{$VarMensagem}'");

$idResposta = $stmt->fetchAll(PDO::FETCH_COLUMN, 0);
$idResposta = implode(',', $idResultante);

$QueryRespostas = $pdo->query("SELECT id AS ID, descricao AS DESCRICAO FROM respostas WHERE id IN ('{$idResposta}')");

echo json_encode(['respostas' => $QueryRespostas->fetchAll()]);

The idea is basic, such as mentioned here and here too , just add , between elements so that it would 1, 2, 3, 4, 5 . Then use that data for IN .

Another solution, keeping your code as it is :

$stmt = $pdo->query("SELECT * FROM mensagem WHERE mensagem_id ='{$VarMensagem}'");

while($row = $stmt->fetch(PDO::FETCH_ASSOC)){

    $data[] = $row;

}

$VarMinhasResp = implode(',', array_column($data, 'respostas'));

$QueryRespostas = $pdo->query("SELECT id AS ID,descricao AS DESCRICAO FROM respostas WHERE id IN ('{$VarMinhasResp}')");

while($row = $QueryRespostas->fetch(PDO::FETCH_ASSOC)){

    $dataGeral['respostas'][] = $row;

}

$result = array_merge($data , $dataGeral);

echo json_encode($result);

SQL Only:

SELECT id        AS ID, 
       descricao AS DESCRICAO 
FROM   respostas 
WHERE  id IN (SELECT respostas
              FROM   mensagem 
              WHERE  mensagem_id = '{$VarMensagem}')

In this way you can do:

$QueryRespostas = $pdo->query("SELECT id AS ID, descricao AS DESCRICAO FROM respostas WHERE id IN(SELECT respostas FROM mensagem WHERE mensagem_id = '{$VarMensagem}')");