Query with array returning only first record

Asked

Viewed 242 times

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 in the messages table and I get the id of the answers and I make a query in the answers, this working, but this returning only the first record, follows how it is the right of the query and that runs perfectly in mysql:

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

I cannot see where the error is, whether it is in the loop or in the array, which can be ?

php mysql array pdo select
  • 1

    Why are you running the query within the while? It shouldn’t be outside?

  • There are several mistakes, they are not separated by , and the execution is within the while. In addition you can nor need PHP to do this.

  • @ Anderson Carlos Woss I tested out and also was not

  • @ Inkeliz I did as you said only with sql and still only returned the first record

  • $dataGeral['respostas'] = $row; you are assigning the value in the same variable every time of the loop.

1 answer

1


If you really want to use PHP to create IN, can use the fetchAll and use the implode to put it all 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, like mentioned here and also here, just add , between the elements, thus would be 1, 2, 3, 4, 5. Then uses such data for the IN.

Another solution, keeping your code how are:

$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);

Only with SQL:

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

That 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}')");

Browser other questions tagged php mysql array pdo select

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