CODEWITHSUNDEEP
phpxml
Bertho Joris
Bertho Joris

Reputation: 1601

Double query to retrieve data from database

Please help me to do the process in php so that I get the data derived from a variable. In this case, I created a php file that serves to make xml file creation. Here I've got the data, namely channel, and I want every parent in that channel my child will enter data. Please see my code :

<?php
$dbhost = "localhost";
$dbuser = "root";
$dbpass = "";
$dbname = "db_epg";
mysql_connect($dbhost,$dbuser,$dbpass);
mysql_select_db($dbname);


$sql = "select distinct(channel_name) FROM epg";
$q   = mysql_query($sql) or die(mysql_error());
$xml = "<timetable start='00:00' end='24:00' title='Friday, September 28th, 2011'>";

while($r = mysql_fetch_array($q)){
     $CN   = $r['channel_name'];
     $xml .= "<channel>".$CN."</channel>";  


}   

    $xml .= "</timetable>";
    $sxe = new SimpleXMLElement($xml);
    $sxe->asXML("epg.xml");

    ?>

And the result from my code is : http://s9.postimage.org/rjsodf9mn/result.png

If I want to do a loop in each data channel will be no data on children, then what should I do?

I've tried doing another query in this while section :

while($r = mysql_fetch_array($q)){
$CN         = $r['channel_name'];
$xml .= "<channel>".$CN."</channel>";   

$sql2 = "select * FROM epg where channel_name='".$CN."'";
$q2  = mysql_query($sql2) or die(mysql_error());

while($r2 = mysql_fetch_array($q2)){
    $Mulai      = date('H:i', strtotime($r2['waktu_mulai']));
    $Selesai    = date('H:i', strtotime($r2['waktu_akhir']));
    $Title      = $r2['judul'];
    $Desk       = $r2['sinopsis'];

      $xml .= "<event start='".$Mulai."' end='".$Selesai."'>";
      $xml .= "<title>".$Title."</title>";
      $xml .= "<description>".$Desk."</description>"; 
      $xml .= "</event>";   
    }


}

But I have failed. Can someone help me??

Upvotes: 1

Views: 190

Answers (1)

nur.hidayat
nur.hidayat

Reputation: 36

Try modify your code like below, please check for syntax error since i havent got the chance to try it :)

while ($r = mysql_fetch_array($q)) {
   // open channel header
   $CN  = $r['channel_name'];
   $xml .= "<channel name='".$CN."'>";   

   // event details for channel
   $sql2 = "select * FROM epg where channel_name='".$CN."'";
   $q2  = mysql_query($sql2) or die(mysql_error());
   while($r2 = mysql_fetch_array($q2)) {
      $Mulai      = date('H:i', strtotime($r2['waktu_mulai']));
      $Selesai    = date('H:i', strtotime($r2['waktu_akhir']));
      $Title      = $r2['judul'];
      $Desk       = $r2['sinopsis'];
      $xml .= "<event start='".$Mulai."' end='".$Selesai."'>";
      $xml .= "<title>".$Title."</title>";
      $xml .= "<description>".$Desk."</description>"; 
      $xml .= "</event>";   
   }

   // close channel
   $xml .= "</channel>";   
}

you may also see the code here the code here

Upvotes: 2

Related Questions