将列插入为类型的标题(insert column as header for type)
我有一个MySql表,我想按类型列出事物并插入标题。 我会使用什么类型的查询?
由此:
| Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Fang | Benny | dog | m | 1990-08-27 | NULL | | Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 | | Chirpy | Gwen | bird | f | 1998-09-11 | NULL | | Whistler | Gwen | bird | f | 1997-12-09 | NULL | | Slim | Benny | snake | m | 1996-04-29 | NULL | | Dalli | Alli | canine | m | 2001-12-20 | NULL | | Tara | David | canine | f | 2002-05-17 | NULL | | Mimi | Alli | guinea pig | m | 2004-05-17 | NULL |对此:
<h2>Cat</h2> <ul> <li>Fluffy</li> <li>Claws</li> </ul> <h2>Dog</h2> <li>Buffy</li> <li>Fang</li> <li>Bowser</li> </ul>等等
I have a MySql table and I want to list things by type and insert headers. What type of query would I use?
From This:
| Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Fang | Benny | dog | m | 1990-08-27 | NULL | | Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 | | Chirpy | Gwen | bird | f | 1998-09-11 | NULL | | Whistler | Gwen | bird | f | 1997-12-09 | NULL | | Slim | Benny | snake | m | 1996-04-29 | NULL | | Dalli | Alli | canine | m | 2001-12-20 | NULL | | Tara | David | canine | f | 2002-05-17 | NULL | | Mimi | Alli | guinea pig | m | 2004-05-17 | NULL |To this:
<h2>Cat</h2> <ul> <li>Fluffy</li> <li>Claws</li> </ul> <h2>Dog</h2> <li>Buffy</li> <li>Fang</li> <li>Bowser</li> </ul>etc.
最满意答案
不要尝试用SQL做所有这些(只做一个标准的选择查询),使用PHP对结果进行分组,然后呈现它。 这样做的最好方法是让一个对象为你做分组,因为你可能不止一次需要它。 例如,您的类可能如下所示:
<?php class Arrays { public static function group($array,$key) { if(NULL == $array) return NULL; $grouped = NULL; foreach($array as $item) { $grouped[$item[$key]][] = $item; } return $grouped; } } ?>你的用例可能是这样的:
<?php $result = ...; // The result of your database query. $grouped_by_type = Arrays::group($result,"type"); foreach($grouped_by_type as $type => $group) { echo "<h2>".ucwords($type)."</h2>"; echo "<ul>"; foreach($group as $animal) { echo "<li>".$animal['first_name']."</li>"; // Assumes the query brought back first_name... } echo "</ul>"; } ?>Don't try to do all of this with SQL (just do a standard select query), use PHP to group the result, then present it. The best way to do this would be to have an object that will do the grouping for you, as you'll probably need it more than once. For example, your class could look something like this:
<?php class Arrays { public static function group($array,$key) { if(NULL == $array) return NULL; $grouped = NULL; foreach($array as $item) { $grouped[$item[$key]][] = $item; } return $grouped; } } ?>And your use case could be something like:
<?php $result = ...; // The result of your database query. $grouped_by_type = Arrays::group($result,"type"); foreach($grouped_by_type as $type => $group) { echo "<h2>".ucwords($type)."</h2>"; echo "<ul>"; foreach($group as $animal) { echo "<li>".$animal['first_name']."</li>"; // Assumes the query brought back first_name... } echo "</ul>"; } ?>更多推荐
发布评论