Order consultation in the interim

Asked

Viewed 658 times

5

I am trying to list a certain product category. example. 

$consulta = $pdo->prepare("SELECT * FROM msg where tema = :tema;");
$s = 'mensagens-de-aniversario';
$consulta->bindParam(":tema", $s, PDO::PARAM_STR);
$consulta->execute();
while ($linha = $consulta->fetch(PDO::FETCH_ASSOC)){

if($linha['voz'] == 'vozfeminina'){
 echo "<p>".$linha['voz']." A</p>";
}

if($linha['voz'] == 'vozmasculina'){
 echo "<p>".$linha['voz']." B</p>";
}

}

This listing thus: 

vozfeminina A, vozfeminina A, vozmasculina B, vozmasculina B

Would have a way when do this query list all products in order:

"vozfeminina A"  |  "vozmasculina B"  |  "vozfeminina A"   |  "vozmasculina B"...

I am using mysql PDO connection.

php mysql select

  • But what order would that be? I do not know if it was the example, but it seems that it is wanting to intermediate?

  • 1

    That same interim result. Would be like this: I have 4 products X and 4 product Y when listing I would like it to stay like this product X, Y, X, Y, X, Y

  • But there is only one pair (female A, male A, female B, male B, female C, male C) which would result in fem-A, masc-A, fem-B, masc-B. Would that be?

  • We’re almost there. That’s how it would be. in the same table has female product and male product, the idea is to list thus product-a, product,b product,a product,b. the above code is listing product a, product a, product b, product b,

  • I am doing some tests with GROUP by but shows only two results. and already with Union shows the same result above.

  • It seems to me Pivot

Show 1 more comment

2 answers

2


It is possible to sort using a sub-query and variables:

SELECT voz, produto, outra_coluna
FROM (
    SELECT voz, produto, outra_coluna
    , IFNULL(@orderA, @orderA := 0)
    , IFNULL(@orderB, @orderB := 0)
    , IF(voz = 'vozfeminina', @orderA := @orderA + 1, IF(voz = 'vozmasculina', @orderB := @orderB + 1, null)) AS idx
    FROM msg
    WHERE tema = :tema
) AS a
ORDER BY idx, voz;

Working example: http://sqlfiddle.com/#! 9/663f3/4

  • Hello Sanction thank you for answering. I tried this way and this listing all fem and all masc without intermediate the result. yet thank you for the strength...

  • 1

    @Wildson added a working fiddle

0

It may be necessary some adjustment, but with this logic you can solve the problem. It is not a sort with mysql but it will work. So you make two queries, one for masc and one for Femi, and intercala at the time of displaying.

$s = 'mensagens-de-aniversario';
// CONSULTA DE VOZ FEMININA
$consultaF = $pdo->prepare("SELECT * FROM msg where tema = :tema AND voz = 'vozfeminina';");
$consultaF->bindParam(":tema", $s, PDO::PARAM_STR);
$consultaF->execute();
// CONSULTA DE VOZ MASCULINA
$consultaM = $pdo->prepare("SELECT * FROM msg where tema = :tema AND voz = 'vozmasculina';");
$consultaM->bindParam(":tema", $s, PDO::PARAM_STR);
$consultaM->execute();
// FAZ O FETCH DAS DUAS CONSULTAS
while ($linhaF = $consultaF->fetch(PDO::FETCH_ASSOC) | $linhaM = $consultaM->fetch(PDO::FETCH_ASSOC)){

    if(count($linhaF)){
     echo "<p>".$$linhaF['voz']." A</p>";
    }

    if(count($linhaM)){
     echo "<p>".$linhaM['voz']." B</p>";
    }

}

  • Opa Euler01 ok.Thanks for answering. I’m using your code for testing. at the moment this is what I want. plus the part of $linhaM is not showing the table data. I will make some adjustments to see what is happening. worth Broder..

  • Are you using the while the way I put it? With an operator | Just? Like this: while ($lineF = $query F->fetch(PDO::FETCH_ASSOC) | $lineM = $query->fetch(PDO::FETCH_ASSOC)){

  • if you use one | it executes the first and the second. If you use two || it executes the first and jumps.

  • Thanks Dad I got here.

Browser other questions tagged php mysql select

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