Databases
StackOverflow2013
Postsdbo
POcan't figure out sql statement
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POcan't figure out sql statement
    text
    copied!<p>Could someone please help me. I'm trying to create an xml list with all the events ordered by date. I'm doing this by using a php script. Right now I have this:</p> <pre><code>$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 &gt; CURDATE() ORDER BY K.kalender_datum"; $query = mysql_query($sql); $last_date = ""; $tag_open = false; $xml = "&lt;?xml version=\"1.0\" encoding=\"UTF-8\"?&gt;\n"; $xml .= "&lt;kalender&gt;\n"; for($i = 0; $i&lt;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&lt;/afhaaldag&gt;\n"; $tag_open = false; } $xml .= "\t&lt;afhaaldag&gt;\n"; $xml .= "\t&lt;datum&gt;" . $itemrow['kalender_datum'] . "&lt;/datum&gt;\n"; $xml .= "\t&lt;afval&gt;" . $itemrow['afval_naam'] . "&lt;/afval&gt;\n"; $last_date = $itemrow['kalender_datum']; } else { $xml .= "\t&lt;afval&gt;" . $itemrow['afval_naam'] . "&lt;/afval&gt;\n"; $last_date = $itemrow['kalender_datum']; $tag_open = true; } } $xml .= "&lt;/kalender&gt;"; echo $xml; </code></pre> <p>That gives me this output in xml:</p> <pre><code>&lt;kalender&gt; &lt;afval&gt;Grofvuil&lt;/afval&gt; &lt;afval&gt;Restafval&lt;/afval&gt; &lt;afval&gt;GFT&lt;/afval&gt; &lt;afval&gt;PMD&lt;/afval&gt; &lt;afval&gt;Snoeiresten&lt;/afval&gt; &lt;afval&gt;GFT&lt;/afval&gt; &lt;afval&gt;Restafval&lt;/afval&gt; &lt;afval&gt;GFT&lt;/afval&gt; &lt;afval&gt;PMD&lt;/afval&gt; &lt;afval&gt;GFT&lt;/afval&gt; &lt;afval&gt;Snoeiresten&lt;/afval&gt; &lt;afval&gt;Papier en karton&lt;/afval&gt; &lt;afval&gt;Grofvuil&lt;/afval&gt; &lt;afval&gt;Restafval&lt;/afval&gt; &lt;afval&gt;GFT&lt;/afval&gt; &lt;afval&gt;PMD&lt;/afval&gt; &lt;/kalender&gt; </code></pre> <p>But what I need is this:</p> <pre><code>&lt;kalender&gt; &lt;afhaaldag&gt; &lt;datum&gt;2012-01-02&lt;/datum&gt; &lt;afval&gt;GFT&lt;/afval&gt; &lt;afval&gt;Restafval&lt;/afval&gt; &lt;afval&gt;PMD&lt;/afval&gt; &lt;/afhaaldag&gt; &lt;afhaaldag&gt; &lt;datum&gt;2012-01-17&lt;/datum&gt; &lt;afval&gt;Papier en karton&lt;/afval&gt; &lt;/afhaaldag&gt; &lt;afhaaldag&gt; &lt;datum&gt;2012-01-23&lt;/datum&gt; &lt;afval&gt;GFT&lt;/afval&gt; &lt;/afhaaldag&gt; &lt;/kalender&gt; </code></pre> <p>Does someone know how I can do it. I know I need some sort of loop (I'm guessing a for loop). Could someone please help me out on this one.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload