Sort query in an interleaved way

Question

Sort query in an interleaved way

Navigation

#1 by (2 votes)
#2 by (0 votes)

5

I'm trying to list a particular 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>";
}

}

Listing this way:

vozfeminina A, vozfeminina A, vozmasculina B, vozmasculina B

There would be a way to do this when listing all the products in the order:

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

I'm using mysql's PDO connection.

php mysql select

asked by anonymous 14.06.2015 / 20:47

2 answers

0

You may need 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 men and one for femi, and interleave the time to display.

$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>";
    }

}

15.06.2015 / 14:13

EF is giving Timeout in a query that should be instantaneous Cancel Uploads

score 2 · Accepted Answer

It is possible to order using a subquery 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;

Example working: link