PHP 根据满足条件更改 MySQL 顺序

Question

我正在尝试根据 PHP 中是否满足 if 条件来更改 MySQL 结果的顺序。

我的代码检查是否有任何行重复，如果是，则更改行类。

我想要实现的是，如果有重复项，则 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 排序会将重复项放在其他使查找和编辑/删除变得容易。

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
      }
  }
?>

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