php - 如何在带日期的标题下显示按日期排序的多个表中的排序数据？

Question

我正在开发一个项目管理系统，必须使用多个 MySQL 表(消息、待办事项、事件等)来使用 PHP 将数据提取和排序为结构化格式。

格式应该与此类似

<p><b>Today</b></p>
<p>
  Todo Item, Item name, Item Description <br />
  Todo Item, Item name, Item Description <br />
  Message,   Message title               <br />
  Todo Item, Item name, Item Description <br />
  Todo Item, Item name, Item Description <br />
  Message,   Message title               <br />
</p>
<p><b>June 22, 2011</b></p>
<p>
  Todo Item, Item name, Item Description <br />
  Todo Item, Item name, Item Description <br />
  Todo Item, Item name, Item Description <br />
  Message,   Message title               <br />
  Todo Item, Item name, Item Description <br />
  Message,   Message title               <br />
</p>

示例 MySQL 表如下:

CREATE TABLE `todo` (
  `id` int(12) NOT NULL auto_increment,
  `item` varchar(255) NOT NULL default '',
  `description` text NOT NULL,
  `created` int(12) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM;

INSERT INTO `todo` (`id`, `item`, `description`, `created`) VALUES
(1, 'test to-do list 1', 'this is simply a test 1', 1308847222),
(2, 'test to-do list 2', 'this is simply a test 2', 1308847318),
(3, 'test to-do list 3', 'this is simply a test 3', 1308847371),
(4, 'test to-do list 4', 'this is simply a test 4', 1306847441),
(5, 'test to-do list 5', 'this is simply a test 5', 1306848208);

CREATE TABLE `messages` (
  `id` int(12) NOT NULL auto_increment,
  `title` varchar(255) NOT NULL default '',
  `message` text NOT NULL,
  `created` int(12) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM;

INSERT INTO `messages` (`id`, `title`, `message`, `created`) VALUES
(1, 'test messages 1', 'this is simply a test 1', 1308847222),
(2, 'test messages 2', 'this is simply a test 2', 1308847318),
(3, 'test messages 3', 'this is simply a test 3', 1308847371),
(4, 'test messages 4', 'this is simply a test 4', 1306847441),
(5, 'test messages 5', 'this is simply a test 5', 1306848208);

有效处理此问题的最佳方法是什么？

Answer 1

检索所有项目的联合查询:

SELECT DATE(created) AS d, item, description, 'todo' AS src
FROM todo

UNION

SELECT DATE(created) AS d, title, message, 'messages' AS src
FROM messages

ORDER BY created

然后是一个简单的状态机来处理输出:

$prev_date = null;
$first = true;
while($row = mysql_fetch_assoc($query_results)) {
   if ($prev_date <> $row['d') {
        if (!$first) {
            echo "</p>';
        }
        echo "<p><b>{$row['d']</b></p><p>";
   }
   echo " ... rows stuff<br />";
}