PHP更改MySQL Order By If条件满足

Question

我正在尝试更改MySQL结果的顺序，这取决于PHP中是否满足if条件。

我的代码检查是否有行是duplicates的，如果是，则更改row类。

我试图实现的是，如果存在重复项，则MySQL查询按Frequency排序，如果没有找到重复项，则按Name排序。

这就是我所拥有的：

function getFrequencies() {
  $conn = getConnected("websiteData");
  $frequencyQuery = "
      SELECT
        f.Name,
        f.Frequency,
        f.Country,
        f.Programmed,
       (SELECT count(*) FROM frequencies WHERE Frequency = f.Frequency) as count
      FROM frequencies f;
      ";

  $frequencyResult = mysqli_query($conn, $frequencyQuery);
  while ($frequencyRow = mysqli_fetch_assoc($frequencyResult)) { 
    if($frequencyRow['count'] > 1) {
      $frequencyQuery = "
        SELECT
          f.Name,
          f.Frequency,
          f.Country,
          f.Programmed,
          (SELECT count(*) FROM frequencies WHERE Frequency = f.Frequency) as count
        FROM frequencies f
        ORDER BY Frequency;
        ";
    } else { 
      $frequencyQuery = "
        SELECT
          Name,
          Frequency,
          Country,
          Programmed
        FROM frequencies
        ORDER BY Name;
        ";
    }
  }
  $frequencyResult = mysqli_query($conn, $frequencyQuery);

  while ($frequencyRow = mysqli_fetch_assoc($frequencyResult)){ 
    if ($frequencyRow['count'] > 1) {
      echo '<tr class="warning">' .
             "<td>" . $frequencyRow['Name'] . "</td>" . 
             "<td>" . $frequencyRow['Frequency'] . "</td>" . 
             "<td>" . $frequencyRow['Country'] . "</td>" .
             "<td>" . $frequencyRow['Programmed'] . "</td>" .
           "</tr>" . PHP_EOL;
    } else {
      echo "<tr>" . PHP_EOL . 
             "<td>" . $frequencyRow['Name'] . "</td>" . 
             "<td>" . $frequencyRow['Frequency'] . "</td>" .
             "<td>" . $frequencyRow['Country'] . "</td>" . 
             "<td>" . $frequencyRow['Programmed'] . "</td>" . 
           "</tr>" . PHP_EOL;
    }
  }
}

现在，我知道我的表包含重复项，但当前没有任何突出显示的行，并且顺序是按Name排序的。

在我将我的代码更改为上面提到的代码之前，它确实高亮显示了我的重复行：

function getFrequencies() {
  $conn = getConnected("websiteData");
  // $frequencyQuery = "SELECT Name, Frequency, Country, Programmed, count(*) FROM frequencies GROUP BY Frequency ORDER BY Name";
  $frequencyQuery = "
      SELECT
        f.Name,
        f.Frequency,
        f.Country,
        f.Programmed,
        ( SELECT count(*) FROM frequencies WHERE Frequency = f.Frequency ) as count
      FROM frequencies f
      ORDER BY Frequency;
      ";
      $frequencyResult = mysqli_query($conn, $frequencyQuery);

      while ($frequencyRow = mysqli_fetch_assoc($frequencyResult)) {
        if ($frequencyRow['count'] > 1) {
          echo '<tr class="danger">' . PHP_EOL . 
                 "<td>" . $frequencyRow['Name'] . "</td>" . PHP_EOL . 
                 "<td>" . $frequencyRow['Frequency'] . "</td>" . PHP_EOL . 
                 "<td>" . $frequencyRow['Country'] . "</td>" . PHP_EOL . 
                 "<td>" . $frequencyRow['Programmed'] . "</td>" . PHP_EOL . 
               "</tr>" . PHP_EOL;
        } else {
          echo "<tr>" . PHP_EOL . 
                 "<td>" . $frequencyRow['Name'] . "</td>" . PHP_EOL . 
                 "<td>" . $frequencyRow['Frequency'] . "</td>" . PHP_EOL . 
                 "<td>" . $frequencyRow['Country'] . "</td>" . PHP_EOL . 
                 "<td>" . $frequencyRow['Programmed'] . "</td>" . PHP_EOL . 
               "</tr>" . PHP_EOL;
        }
   }
  }

我能做些什么来实现我想要做的事情？我想要更改顺序的原因是，当没有重复项时，按字母顺序滚动列表是一种首选，但如果有重复项，则按Frequency排序会将重复项排成一行，以便于find和edit/delete。

Answer 1

根据您正在尝试做的事情，我将构建一个结果的关联数组，然后使用您的逻辑对它们进行排序，使用PHP而不是MySQL的order by子句。

您可以采用的一种方法(如果您想保留所有重复项)是通过按Name key对数组进行排序，然后说if unique display else for each duplicate in array display来构建表，这将导致所有重复项连续显示，整个内容按名称的字母顺序排序，您可以使用asort来执行此操作。

为了避免触发副作用，您可以构建两个数组，一个来自MySQL select，另一个是使用array_unique的唯一数组。这样，当您循环遍历数组以显示页面时，您可以循环遍历唯一的数组，并且在呈现时仅获取原始数组中的重复元素。

请考虑以下几点：

<?php
  $array = array();
  $uniqueArray = array_unique($array);
  for ( $i = 0; $i < sizeof($uniqueArray); ++$i ) {
      if ( in_array($uniqueArray[$i], $array) ) {
          // Item has duplicates
      } else {
          // item is unique
      }
  }
?>

如果您不想保留重复项，您可以非常简单地遵循相同的公式，但是在匹配重复项时，您可以从数据库中清除每个重复项，只保留一个，然后从唯一数组中呈现项。