MYSQL order query

Question

MYSQL order query

Navigation

#1 by (0 votes)

0

I have the following mysql queries:

<div class="container">
    <?php foreach($mysqli->query('SELECT * FROM categoria ORDER BY categoria_ordre ASC') as $row_cat) { ?>
        <div class="panel panel-default">
            <div class="panel-heading">
                <h3 class="panel-title">
                    <i class="fa fa-trophy"></i> 
                    <?php echo $row_cat['categoria_nom']; ?>
                </h3>
            </div>
            <div class="panel-body">
                <div class="row columnes-temporades">
                    <?php foreach($mysqli->query('SELECT * FROM competicio WHERE competicio_categoria = '.$row_cat['categoria_id'].' ') as $row_comp) {
                        $sql_temp = 'SELECT * FROM temporada WHERE temporada_id = '.$row_comp['competicio_temporada'].' ORDER BY temporada_ordre DESC ';
                        $res_temp = $mysqli->query($sql_temp);
                        $row_temp = $res_temp->fetch_assoc();
                    ?>
                        <div class="columnes">
                            <a href="classificacio-veure.php?competicio_id=<?php echo $row_comp['competicio_id']; ?>" class="btn btn-xs btn-info">
                                <?php echo $row_temp['temporada_nom']; ?>
                            </a>
                        </div>
                    <?php } ?>
                </div>
            </div>
        </div>
    <?php } ?>
</div>

When I want to show the field $row_temp['temporada_nom'] I do not get sorted by the criteria of the select, any idea of what I'm doing wrong?

Thank you.

PS: Attached screenshots of the tables:

Competition table:

Tambla season:

The field competicio_temporada of the competition table is a foreign key in the field temporada_id of the season table.

Image of the result you currently show me:

mysql php

asked by Vieri 25.06.2018 в 16:14

source

1 answer

I want to make the background that is on the sides of my menu have a color # f2f2f2 Go through Json that is inside an array with each of jquery

score 0 · Accepted Answer

In principle I see some kind of redundancy between categories and competitions, but good assuming that the model works well and is stable, here is a rethink using a single QUERY and doing a bit of magiclabarism ™ with the group and joins :

SQL

SELECT
   categoria_nom,
   competicio_nom,
   GROUP_CONCAT(DISTINCT tempo ORDER BY ordre DESC SEPARATOR ',') as temporadas
  FROM (
    SELECT
       t.temporada_ordre as ordre,
       CONCAT(t.temporada_nom, "|", c.competicio_id) as tempo,
       c.competicio_nom,
       c.competicio_categoria
     FROM temporada t
       INNER JOIN competicio c ON c.competicio_temporada = t.temporada_id
  ) AS tc
  LEFT JOIN categoria ca ON ca.categoria_id = competicio_categoria
  GROUP BY competicio_nom, competicio_categoria, categoria_ordre, categoria_nom
  ORDER BY categoria_ordre
  ;

In the internal select joineamos temporada and competicio , we save the competicio_id associated with each temporada_nom concatenating both with a |

In the external select we join categoria to know the name and categoria_ordre .

In this step we also group by competicio_nom and competicio_categoria keeping the association of the seasons (of the internal select) using GROUP_CONCAT where we also tell you to order this list by temporada_ordre .

Finally we order by categoria_ordre .

This would result in something like this:

categoria_nom   competicio_nom      temporadas
categoria 1     3a catala grup 5    2017-18|69,2016-17|1,2015-16|5,2014-15|3,2013-14|4,2012-13|6
categoria 3     2a regio grup 5     2010-11|8,2009-10|9,2008-09|10,2007-08|11,2006-07|12,2005-06|13,2004-05|14
categoria 5     3a regio grup 5     2010-11|30,2009-10|31,2008-09|32,2007-08|33
categoria 6     2a catala grup 4    2014-15|54,2013-14|55,2012-13|56,2011-12|57

php here I simulate the result in an array (uncomment the query and comment the array)

<?php
/*
$result = $mysqli->query('
SELECT
   categoria_nom,
   competicio_nom,
   GROUP_CONCAT(DISTINCT tempo ORDER BY ordre DESC SEPARATOR ",") as temporadas
  FROM (
    SELECT
       t.temporada_ordre as ordre,
       CONCAT(t.temporada_nom, "|", c.competicio_id) as tempo,
       c.competicio_nom,
       c.competicio_categoria
     FROM temporada t
       INNER JOIN competicio c ON c.competicio_temporada = t.temporada_id
  ) AS tc
  LEFT JOIN categoria ca ON ca.categoria_id = competicio_categoria
  GROUP BY competicio_nom, competicio_categoria, categoria_ordre, categoria_nom
  ORDER BY categoria_ordre
  ;
');
$resultados = $result->fetch_all(MYSQLI_ASSOC);
//*/
$resultados = [
['categoria_nom' =>'categoria 1', 'competicio_nom' => '3a catala grup 5', 'temporadas' => '2017-18|69,2016-17|1,2015-16|5,2014-15|3,2013-14|4,2012-13|6'],
['categoria_nom' =>'categoria 3', 'competicio_nom' => '2a regio grup 5', 'temporadas' => '2010-11|8,2009-10|9,2008-09|10,2007-08|11,2006-07|12,2005-06|13,2004-05|14'],
['categoria_nom' =>'categoria 5', 'competicio_nom' => '3a regio grup 5', 'temporadas' => '2010-11|30,2009-10|31,2008-09|32,2007-08|33'],
['categoria_nom' =>'categoria 6', 'competicio_nom' => '2a catala grup 4', 'temporadas' => '2014-15|54,2013-14|55,2012-13|56,2011-12|57']
];

$categorias = [];
foreach ($resultados as $row_res) :
  $cat_nom = array_shift($row_res); // conseguimos el nombre de categoria
  $categorias[$cat_nom][] = $row_res; // armamos array con el resto de campos
endforeach;

// var_dump($resultados, $categorias);
?>
<div class="container">
  <?php foreach($categorias as $categoria_nom=>$row_cat) { // categorias
  ?><h2><?php echo $categoria_nom; ?></h2>
    <hr />
    <?php foreach($row_cat as $row_comp) { // competiciones
    ?><div class="panel panel-default">
      <div class="panel-heading">
        <h3
        class="panel-title"><i class="fa fa-trophy"></i> <?php
        echo $row_comp['competicio_nom'];
        ?></h3>
      </div>
      <div class="panel-body">
        <div class="row columnes-temporades">
          <?php // extraigo las temporadas a un array
          $temporadas = explode(',', $row_comp['temporadas']);
          foreach($temporadas as $row_temp) { // temporadas
            $tempo = explode('|', $row_temp);
            $temporada_nom = $tempo[0]; // el nombre
            $competicio_id = $tempo[1]; // el id asociado
            ?><div class="columnes">
              <a
              href="classificacio-veure.php?competicio_id=<?php echo $competicio_id; ?>"
              class="btn btn-xs btn-info"
              ><?php echo $temporada_nom; ?></a>
            </div>
          <?php } // temporadas
          ?></div>
      </div>
    </div>
  <?php } // competiciones?>
<?php } // categorias?>
</div>

First, a two-dimensional array is armed for the categories.

We keep the foreach plus one external for the categories, this is where I see something strange in the relationship between categories and competitions.

Internal foreachs are assembled over arrays using explode to separate the data by , (seasons) and by | ( temporada_nom and competicio_id )

html output is something like this (the category titles look like reduntantes because in the sample that I used data there were not two competitions in the same category):

.row.columnes-temporades{text-align:center}
.columnes {display:inline-block}

<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
  

<div class="container">
  <h2>categoria 1</h2>
    <hr />
    <div class="panel panel-default">
      <div class="panel-heading">
        <h3
        class="panel-title"><i class="fa fa-trophy"></i> 3a catala grup 5</h3>
      </div>
      <div class="panel-body">
        <div class="row columnes-temporades">
          <div class="columnes">
              <a
              href="classificacio-veure.php?competicio_id=69"
              class="btn btn-xs btn-info"
              >2017-18</a>
            </div>
          <div class="columnes">
              <a
              href="classificacio-veure.php?competicio_id=1"
              class="btn btn-xs btn-info"
              >2016-17</a>
            </div>
          <div class="columnes">
              <a
              href="classificacio-veure.php?competicio_id=5"
              class="btn btn-xs btn-info"
              >2015-16</a>
            </div>
          <div class="columnes">
              <a
              href="classificacio-veure.php?competicio_id=3"
              class="btn btn-xs btn-info"
              >2014-15</a>
            </div>
          <div class="columnes">
              <a
              href="classificacio-veure.php?competicio_id=4"
              class="btn btn-xs btn-info"
              >2013-14</a>
            </div>
          <div class="columnes">
              <a
              href="classificacio-veure.php?competicio_id=6"
              class="btn btn-xs btn-info"
              >2012-13</a>
            </div>
          </div>
      </div>
    </div>
  <h2>categoria 3</h2>
    <hr />
    <div class="panel panel-default">
      <div class="panel-heading">
        <h3
        class="panel-title"><i class="fa fa-trophy"></i> 2a regio grup 5</h3>
      </div>
      <div class="panel-body">
        <div class="row columnes-temporades">
          <div class="columnes">
              <a
              href="classificacio-veure.php?competicio_id=8"
              class="btn btn-xs btn-info"
              >2010-11</a>
            </div>
          <div class="columnes">
              <a
              href="classificacio-veure.php?competicio_id=9"
              class="btn btn-xs btn-info"
              >2009-10</a>
            </div>
          <div class="columnes">
              <a
              href="classificacio-veure.php?competicio_id=10"
              class="btn btn-xs btn-info"
              >2008-09</a>
            </div>
          <div class="columnes">
              <a
              href="classificacio-veure.php?competicio_id=11"
              class="btn btn-xs btn-info"
              >2007-08</a>
            </div>
          <div class="columnes">
              <a
              href="classificacio-veure.php?competicio_id=12"
              class="btn btn-xs btn-info"
              >2006-07</a>
            </div>
          <div class="columnes">
              <a
              href="classificacio-veure.php?competicio_id=13"
              class="btn btn-xs btn-info"
              >2005-06</a>
            </div>
          <div class="columnes">
              <a
              href="classificacio-veure.php?competicio_id=14"
              class="btn btn-xs btn-info"
              >2004-05</a>
            </div>
          </div>
      </div>
    </div>
  <h2>categoria 5</h2>
    <hr />
    <div class="panel panel-default">
      <div class="panel-heading">
        <h3
        class="panel-title"><i class="fa fa-trophy"></i> 3a regio grup 5</h3>
      </div>
      <div class="panel-body">
        <div class="row columnes-temporades">
          <div class="columnes">
              <a
              href="classificacio-veure.php?competicio_id=30"
              class="btn btn-xs btn-info"
              >2010-11</a>
            </div>
          <div class="columnes">
              <a
              href="classificacio-veure.php?competicio_id=31"
              class="btn btn-xs btn-info"
              >2009-10</a>
            </div>
          <div class="columnes">
              <a
              href="classificacio-veure.php?competicio_id=32"
              class="btn btn-xs btn-info"
              >2008-09</a>
            </div>
          <div class="columnes">
              <a
              href="classificacio-veure.php?competicio_id=33"
              class="btn btn-xs btn-info"
              >2007-08</a>
            </div>
          </div>
      </div>
    </div>
  <h2>categoria 6</h2>
    <hr />
    <div class="panel panel-default">
      <div class="panel-heading">
        <h3
        class="panel-title"><i class="fa fa-trophy"></i> 2a catala grup 4</h3>
      </div>
      <div class="panel-body">
        <div class="row columnes-temporades">
          <div class="columnes">
              <a
              href="classificacio-veure.php?competicio_id=54"
              class="btn btn-xs btn-info"
              >2014-15</a>
            </div>
          <div class="columnes">
              <a
              href="classificacio-veure.php?competicio_id=55"
              class="btn btn-xs btn-info"
              >2013-14</a>
            </div>
          <div class="columnes">
              <a
              href="classificacio-veure.php?competicio_id=56"
              class="btn btn-xs btn-info"
              >2012-13</a>
            </div>
          <div class="columnes">
              <a
              href="classificacio-veure.php?competicio_id=57"
              class="btn btn-xs btn-info"
              >2011-12</a>
            </div>
          </div>
      </div>
    </div>
  </div>

In short, an interesting exercise to group, concatenate and sort in SQL and then ungroup to show in PHP. I hope it serves you.

The fiddle updated with the problematic record (69, '3a catala grup 5', 1, 18), link