对 Mysql PHP 查询结果进行分组 [英] Grouping Mysql PHP Query Results

问题描述

我有一个基本的 PHP 页面，用于搜索包含复古 ZX Spectrum 杂志文章的 MySQL 数据库.

I have a basic PHP page which searches a MySQL Database of retro ZX Spectrum magazine articles.

地址是[www.retroresource.co.uk][1]

The address is [www.retroresource.co.uk][1]

PHP 结果页面很好，因为它产生了结果，但我希望能够按fgames.fgname"对结果进行分组

The PHP Results Page is fine, in that it produced the results, but I would like to be able to group the results by 'fgames.fgname'

例如，如果我搜索 Dizzy

So for example if I searched for Dizzy

头晕(作为标题)

包含 fgames.fgname = Dizzy 行的表

Table with rows that have fgames.fgname = Dizzy

Spindizzy(作为标题)

Spindizzy (as a header)

包含 fgames.fgname = Spindizzy 行的表

Table with rows that have fgames.fgname = Spindizzy

金银岛晕(作为标题)

包含 fgames.fgname = Treasure Island Dizzy 行的表

Table with rows that have fgames.fgname = Treasure Island Dizzy

等等等等

我用谷歌搜索并查看了我的一些教科书，但找不到解决方案.非常感谢任何帮助.

I have googled and looked in a few of my text books, but am unable to see a solution. Any help much appreciated.

彼得

援助后修改代码(虽然没有结果)

Amended Code after assistance (No results though)

<?php 

error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);


require( '../connect_db.php' ) ;

$query = $_GET['query']; 


$min_length = 4;
// you can set minimum length of the query if you want

$query = strip_tags($query); 

$query = mysqli_real_escape_string ($dbc,$query);


$q = "SELECT fgames.fgname, fgames.fgprorg, fgames.fgprbud, fgames.fgratng, fgames.fgprdsk, ftypes.fttname, frefs.fryymm, frefs.frpage, frefs.frissue, fmagzne.fmname, frefcde.ffname
FROM fgames, ftypes,frefs, fmagzne,frefcde
WHERE ftypes.fttype = fgames.fgtype
and   fgames.fglink = frefs.frlink2
and   frefs.frentry = frefcde.ffentry
and   frefs.frmag = fmagzne.fmmag 
and   fgames.fgname LIKE '%".$query."%'
order by fgames.fgname ASC" ;

$r = mysqli_query( $dbc , $q ) ;


$current_group = '';
while ( $row = mysqli_fetch_array( $r , MYSQLI_ASSOC ) ) 

{
    if($row['fgames.fgname']) !== $current_group) 

{

       if($current_group !== '') 
{
           echo '</table>';
}
      $current_group = $row['fgames.fgname'];

      echo '<table><tr><th>FGNAME</th><th>FGPRORG</th><th>FGRBUD</th><th>FGRATNG</th><th>FGRDSK</th>
      <th>FTTTNAME</th><th>FRYYMM</th><th>FRPAGE</th><th>FRISSUE</th><th>FMNAME</th><th>FFNAME</th></tr>';

}
 echo '<tr><td>'.$row['fgname'].'</td><td>'.$row['fgprorg'].'</td><td> '.$row['fgprbud'].' </td><td>'.$row['fgratng'].' </td>            <td>'.$row['fgprdsk'].' </td><td>'.$row['fttname'].'</td><td> '.$row['fryymm'].'</td><td> '.$row['frpage'].'</td><td> '.$row['frissue'].' </td> <td>'.$row['fmname'].' </td><td>'.$row['ffname'].'</td></tr>';

}

echo '</table>';

else
{
  echo '<p>' . mysqli_error( $dbc ) . '</p>'  ;
}








# Close the connection.
mysqli_close( $dbc ) ;
?>

推荐答案

有两种基本方法.

或者将查询数据加载到二维数组中，然后像这样使用嵌套循环输出:

Either load the query data into a two dimensional array and output with nested loop like this:

$array_for_output = array();
while($row = /* your query fetch mechanism here */) {
    $array_for_output[$row['field_you_are_grouping_on']] = $row;
}

foreach($array_for_output as $table_name => $table_data) {
    // output table header here
    foreach($table_data as $table_row) {
        // output individual row
    }
    // output table closure here
}

或者另一种方法是在您尝试分组的字段上使用 ORDER BY 子句进行查询，并在单个循环中检测当前组中的变化

Or the other approach is to query using an ORDER BY clause on the field you are trying to group by and detect changes in the current group in single loop

$query = "
SELECT ...
FROM ...
WHERE ...
ORDER BY field_you_are_grouping_on, any_secondary_ordering_field_here ASC";

// execute query here

$current_group = '';
while ($row = /* your query fetch mechanism here */) {
    if($row['field_you_are_grouping_on']) !== $current_group) {
       // we have started a new group
       if($current_group !== '') {
           // this is not first table
           // so output closure to previous table here
       }
       $current_group = $row['field_you_are_grouping_on'];
       // output new table header here
    }
    // output row data here
}
// output closure to final table here

从内存管理的角度来看，第二种方法通常更可取(您不必在任何时候都将整个结果集存储在内存中)，但一些新手开发人员可能不会发现这不如第一种方法直观.如果您想获取完整的结果集并将其注入到用于呈现显示的某种模板中，第一种方法也可能更合适.

The second approach is usually preferable from a memory management standpoint (you don't have to store entire result set in memory at any point), but some beginning developers may not find this not as intuitive as the first method. The first method also may be more appropriate if you want to take the full result set and inject it into some sort of template that is used to render the display.

这篇关于对 Mysql PHP 查询结果进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！