[SOLVED] multiple while loops help?

[cindreta]

so this is the scoop, i am doing some exporting from the database and i have two tables:

 

news (id,title,body,....) second one is comments (id,body,news_id) and now i need to export all the database info to a xml structure document.

 

so now i have per say this:

$sql = "SELECT id,title,body from news";
$result  = mysql_query ($sql) or die ("Couldn't execute query: Reason; ".mysql_error());
while ($row = mysql_fetch_assoc($result) ) 
{
$xml .= "<news id=\"".$row['id']."\">";
$xml .= "<title>".$row['title']."</title>";
}

and now i need to print the comments for that news item, but how to print multiple coments separated by "|"? Should i put another query inside the while loop? and how to count and echo all the comments for one news item?

 

example of what i need:

<comments>comment text 1|comment text 2.... </comments>

 

i know i have to use implode to join them, i just don't know how to get them into that while loop

[Masna]

Just nest another while loop in the original outer loop. In this one, fetch the comments for each news item.

 

Also, I highly suggest making a separate <comment> tag to hold each new comment (as opposed to clumping them all together in one <comments> tag and joining them with |.

[cindreta]

yes yes i see, hm well this is what i got here now:

 

$sql = "SELECT id,title,body from news";
$result  = mysql_query ($sql) or die ("Couldn't execute query: Reason; ".mysql_error());
while ($row = mysql_fetch_assoc($result) )
{
$xml .= "<news id=\"".$row['id']."\">";
$xml .= "<title>".$row['title']."</title>";

//get the comments
$query_c = "SELECT id,body FROM comments WHERE news_id=".$row['id']."";
$result_c = mysql_query($query_c) or die('Error : ' . mysql_error());
$rows_c = mysql_num_rows($result_c);
if($rows_c != "0"){
//while and count the number of comments and get them
while ($row_c = mysql_fetch_row($result_c)) 
{
for ($i=0; $i < mysql_num_fields($result_c); $i++){ $glue_c[] = "$row_c[$i]"; }
}
$joined_c = implode("|", $glue_c); $output .= " $joined_c"; } else { $output .= " none"; }
}//end main while

i have that pretty lame solution for getting the comments (please if you have a better ways for getting all the comments with the same id tell me) but and i do get an output, the problem if for the firsth news item it displays the right comments then for the second it shows the comments from the firsth plust the second and in the third news item all off them and so on? what am i doing wrong???

 

thank you

[Maq]

Is it necessary to have the comments separated by pipe lines (|)?

 

Like Masna suggested, if you're dealing with XML, you should do it properly.  My suggestion would be something along the lines of:

 

$sql = "SELECT id,title,body from news";
$result = mysql_query($sql) or die ("Couldn't execute query: Reason; ".mysql_error());
while($row = mysql_fetch_assoc($result))
{
   $xml .= "";
   $xml .= "{$row['title']}";
   //get the comments
   $query_c = "SELECT id,body FROM comments WHERE news_id={$row['id']}";
   $result_c = mysql_query($query_c) or die('Error : ' . mysql_error());
   $xml .= "";
   while ($row_c = mysql_fetch_row($result_c))
   {
      $xml .= "{$row_c['body']}";   
   }
   $xml .= "";
}//end main while

?>

 

What are you checking the XML with?  You can use XPath to see if there are no comments by checking the child of .

[cindreta]

no i can display the comments like you said, not seprated with |.

 

i tried you'r code and it just displays <comment/> and nothing inside that?

and also if a news has two comments it does display two of those <comment/><comment/> but nothing inside???

why is that, i only copy and pasted it and i don't know why it doesn't work.

 

LATEST EDIT: i just replaced this:

   while ($row_c = mysql_fetch_row($result_c))

with this:

   while ($row_c = mysql_fetch_array($result_c))

and it seems to be working for now.

[Maq]

Change this line:

 

   while ($row_c = mysql_fetch_row($result_c))

 

to this:

 

   while ($row_c = mysql_fetch_assoc($result_c))