可以'；t计算sql语句 - can't figure out sql statement

有人能帮我吗？我正在尝试创建一个xml列表，其中包含按日期排序的所有事件。我使用php脚本来完成这项工作。现在我有这个：

$sql = "SELECT DATE_FORMAT(K.kalender_datum,'%W %d %M %Y') as afhaaldag, A.afval_naam
        FROM tblkalender K
        INNER JOIN tblafval A
        ON A.pk_afval_Id = K.fk_afval_Id
        WHERE kalender_datum > CURDATE()
        ORDER BY K.kalender_datum";
$query = mysql_query($sql);
$last_date = "";
$tag_open = false;
$xml = "<?xml version='"1.0'" encoding='"UTF-8'"?>'n";
$xml .= "<kalender>'n";
for($i = 0; $i<mysql_num_rows($query); $i++)
{   
    $itemrow = mysql_fetch_assoc($query);
    if($last_date != $itemrow['kalender_datum'])
    {
        //als de laatste datum die gebruikt is niet gelijk is aan de datum die hij binnen krijgt, moet hij een nieuwe afhaaldag beginnen
        if($tag_open)
        {
            $xml .= "'t</afhaaldag>'n";
            $tag_open = false;
        }
        $xml .= "'t<afhaaldag>'n";
        $xml .= "'t<datum>" . $itemrow['kalender_datum'] . "</datum>'n";
        $xml .= "'t<afval>" . $itemrow['afval_naam'] . "</afval>'n";    
        $last_date = $itemrow['kalender_datum'];
    }   
    else
    {
        $xml .= "'t<afval>" . $itemrow['afval_naam'] . "</afval>'n";
        $last_date = $itemrow['kalender_datum'];
        $tag_open = true;
    }   
}
$xml .= "</kalender>";
echo $xml;

这给了我xml格式的输出：

<kalender>
   <afval>Grofvuil</afval>
   <afval>Restafval</afval>
   <afval>GFT</afval>
   <afval>PMD</afval>
   <afval>Snoeiresten</afval>
   <afval>GFT</afval>
   <afval>Restafval</afval>
   <afval>GFT</afval>
   <afval>PMD</afval>
   <afval>GFT</afval>
   <afval>Snoeiresten</afval>
   <afval>Papier en karton</afval>
   <afval>Grofvuil</afval>
   <afval>Restafval</afval>
   <afval>GFT</afval>
   <afval>PMD</afval>
</kalender>

但我需要的是：

<kalender>
   <afhaaldag>
      <datum>2012-01-02</datum>
      <afval>GFT</afval>
      <afval>Restafval</afval>
      <afval>PMD</afval>
   </afhaaldag>
   <afhaaldag>
      <datum>2012-01-17</datum>
      <afval>Papier en karton</afval>
   </afhaaldag>
   <afhaaldag>
      <datum>2012-01-23</datum>
      <afval>GFT</afval>
   </afhaaldag>
</kalender>

有人知道我怎么做吗？我知道我需要某种循环（我猜是for循环）。有人能帮我解决这个问题吗。

我在这里使用python，但您可以毫无问题地遵循以下代码：

kalender = [
('2012-01-02', 'GFT'),
('2012-01-02', 'Restafval'),
('2012-01-02', 'PMD'),
('2012-01-17', 'Papier en karton'),
('2012-01-23', 'GFT')
] 
last_date = ""
tag_open = False
xml = "<?xml version='"1.0'" encoding='"UTF-8'"?>'n"
xml += "<kalender>'n"

for i in kalender:
    # i[0] == datum
    # i[1] == afval
    # i == $itemrow in your code
    datum = i[0]
    afval = i[1]
    if last_date != datum:
        if tag_open:
            xml += "'t</afhaaldag>'n"
            tag_open = False
        xml += "'t<afhaaldag>'n"
        xml += "'t<datum>" + datum + "</datum>'n"
        xml += "'t<afval>" + afval +  "</afval>'n"
        last_date = datum
            tag_open = True
    else:
        xml += "'t<afval>" + afval + "</afval>'n"
        last_date = datum
        tag_open = True
xml += "'t</afhaaldag>'n"
xml += "</kalender>"
print xml

这就是输出：

<?xml version="1.0" encoding="UTF-8"?>
<kalender>
    <afhaaldag>
    <datum>2012-01-02</datum>
    <afval>GFT</afval>
    <afval>Restafval</afval>
    <afval>PMD</afval>
    </afhaaldag>
    <afhaaldag>
    <datum>2012-01-17</datum>
    <afval>Papier en karton</afval>
    </afhaaldag>
    <afhaaldag>
    <datum>2012-01-23</datum>
    <afval>GFT</afval>
    </afhaaldag>
</kalender>

[完成]

输出正是你想要的，是吗？请尝试再次检查，因为在您的新输出中，基准标记不在那里，所以这是wierd。

注意我增加了两行：

tag_open = True  ##(in the True part of the If Statement)
xml += "'t</afhaaldag>'n" ###Before ending the kalender tag.

和删除else子句中的if语句。

代码：http://dl.dropbox.com/u/3389104/kal.py

-------编辑结束

我的评论（代码）：

$last_date = "";
$tag_open = false;
for($i = 0; $i<mysql_num_rows($query); $i++)
{   
    $itemrow = mysql_fetch_assoc($query);   
    if ($last_date != $itemrow['kalender_datum'])  
    {
       //New Entry
       if (tag_open)
       {
           $xml .= "'t</afhaaldag>'n";
           tag_open = false;
       }
       $xml .= "'t<afhaaldag>'n";
       $xml .= "'t<datum>" . $itemrow['kalender_datum'] . "</datum>'n";
       $xml .= "'t<afval>" . $itemrow['afval_naam'] . "</afval>'n";
       $xml .= "'t</afhaaldag>'n";
       $last_date = $itemrow['kalender_datum']
    }
    else
    {
       if not (tag_open)
       {
           $xml .= "'t<afhaaldag>'n";
           tag_open = true;
       }
       $xml .= "'t<afval>" . $itemrow['afval_naam'] . "</afval>'n";
    }
}

对不起，我现在没有带php的电脑。这个代码是我的总体想法，我不知道它是否有效。

好吧，我想说的一件事是，查询结果中没有"kalender_datum"键。您将格式化日期别名为afhaaldag。所以在你的循环中，你应该有：

if($last_date != $itemrow['afhaaldag'])
{
    ...
    $xml .= "'t<datum>" . $itemrow['afhaaldag'] . "</datum>'n";
    $xml .= "'t<afval>" . $itemrow['afval_naam'] . "</afval>'n";    
    $last_date = $itemrow['afhaaldag'];
}   
else
{
    $xml .= "'t<afval>" . $itemrow['afval_naam'] . "</afval>'n";
    $last_date = $itemrow['afhaaldag'];
    $tag_open = true;
}

不确定这是否是问题所在，因为我还没有测试代码，但这可能是问题所在。