[MySQL]News from one table, their authors from second one

How to do a PHP script which connect to 2 MySQL tables in such a way that it displays pieces of news in turn from ‘news_data_tbl’ table and authors of news and their e-mail addresses form ‘users_tbl’ table. ‘news_data_tbl’ table include ‘user_id’ field where numbers of authors are included. There the same numbers in ‘ID’ field in ‘users_tbl’ table. I want the script to display names and e-mail address of author (which ‘ID’ in ‘users_tbl’ is the same as ‘user_id’ in ‘news_data_tbl’ table) in all pieces of news. I have tried to do this in this way:

<?php header("Content-type: application/rss+xml"); echo '<?xml version="1.0" encoding="utf-8" ?>'; ?>
<rss version="2.0">
  <channel>
  <title>Aktualno&#347;ci LO im. Jose Marti w Warszawie</title>
  <link>http://josemarti.waw.pl</link> 
  <description>Szkolne aktualno&#347;ci</description>
  <language>pl</language>
  <managingEditor>szmitek@hotmail.com (Kamil Szmit)</managingEditor>
<?php require_once('mwc.php'); $wa = mysql_query("SELECT * FROM $GLOBALS[news_data_tbl] WHERE `stat`='1' ORDER BY date DESC"); $wb = mysql_query("SELECT * FROM $GLOBALS[users_tbl]"); while ($ra = mysql_fetch_array($wa)) {echo '  <item>
   <title>'.utf8_encode($ra['title']).'</title>
   <link>'.$site_adres."news.php?id=".$ra['ID'].'</link>
   <pubDate>'.date("r", $ra["date"]).'</pubDate>
   <description><![CDATA['.utf8_encode($ra["text"]).']]></description>
   <category>'.$ra["cid"].'</category>
   <author>'; while ($rb = mysql_fetch_array($wb)){if ($rb["ID"]==$ra["user_id"]){echo $rb["email"].' ('.$rb["name"].')';}} echo '</author>
   <guid isPermaLink="true">'.$site_adres."news.php?id=".$ra['ID'].'</guid>
  </item>
';} ?>
 <webMaster>szmitek@hotmail.com</webMaster>
 </channel>
</rss>

PHP:

However e-mail address and name is being displayed only in first item (http://josemarti.waw.pl/rss.php). How to do in order so names of authors and their e-mail address are displayed in all items.

 

Try to move the second MySQL query inside the while loop, so it looks like this:

<?php
	require_once('mwc.php');
	$wa = mysql_query("SELECT * FROM $GLOBALS[news_data_tbl] WHERE `stat`='1' ORDER BY date DESC");
	while ($ra = mysql_fetch_array($wa)) {
	   echo '<item><title>'.utf8_encode($ra['title']).'</title>
	   <link>'.$site_adres."news.php?id=".$ra['ID'].'</link>
	   <pubDate>'.date("r", $ra["date"]).'</pubDate>
	   <description><![CDATA['.utf8_encode($ra["text"]).']]></description>
	   <category>'.$ra["cid"].'</category>
	   <author>';
		$wb = mysql_query("SELECT * FROM $GLOBALS[users_tbl]");
		while ($rb = mysql_fetch_array($wb)){
			if ($rb["ID"]==$ra["user_id"]){
			echo $rb["email"].' ('.$rb["name"].')';
			}
		}
	   echo '</author>
	   <guid isPermaLink="true">'.$site_adres."news.php?id=".$ra['ID'].'</guid>
	   </item>';
	}
?>

Code (markup):

 

Is this fulfilling your requirement or want some more changes?

 

Thank you JOGS_DEV. Now it works.