mysql join help

[php_begins]

I need help in joining 2 tables and display data:

Right now i am just tryin to display by using multiple while loops.

First i am obtaining the pmtextid where the messageread field is 0 from pm table.

Then I am fetching details from pmtext table.

Again I need to fetch the pmid from pm table depending on the details fetched from the pmtext table.

Can i do all these through one join statement:

 

<?
$getpmtext=mysql_query("SELECT pmtextid from pm WHERE messageread='0' ORDER by pmid desc") or die (mysql_error());
                while($getpmtext_results=mysql_fetch_assoc($getpmtext))
                {
                   //$pmid=$getpmtext_results['pmid'];
                   $pmtextid=$getpmtext_results['pmtextid'];

                   $getnewpm=mysql_query("SELECT * from pmtext WHERE pmtextid='$pmtextid' AND dateline >='$datelast' AND dateline <='$datenow'") or die (mysql_error());
                   while($getnewpm_results=mysql_fetch_assoc($getnewpm))
                   {
                      $newpmtextid=$getnewpm_results['pmtextid'];
                      $pmtitle=$getnewpm_results['title'];
                      $fromusername=$getnewpm_results['fromusername'];
                      $fromuserid=$getnewpm_results['fromuserid'];

                      $getnewpmid=mysql_query("SELECT pmid from pm WHERE pmtextid='$newpmtextid'") or die (mysql_error());
                      while($getnewpmid_results=mysql_fetch_assoc($getnewpmid))
                      {
                         $newpmid=$getnewpmid_results['pmid'];
                         echo "NEW PM:<b>  ".$pmtitle."</b> FROM: <b>".$fromusername."</b><br>";
                      }
                    }
                 }
?>

[requinix]

Yes, and you most definitely should - nested loops with SQL queries are almost always bad.

SELECT pm2.pmid, pm.pmtextid, pmtext.title, pmtext.fromusername, pmtext.fromuserid
FROM pm
JOIN pmtext ON pm.pmtextid = pmtext.pmtextid
JOIN pm pm2 ON pm.pmtextid = pm2.pmtextid
WHERE pm.messageread = 0 AND pmtext.dateline BETWEEN '$datelast' AND '$datenow'
ORDER BY pm.pmid DESC

[php_begins]

Thanks, How do I retrieve the details. I tried doing the following, but it does not return anything:

<?
$getpmtext=mysql_query(" SELECT pm2.pmid, pm.pmtextid, pmtext.title, pmtext.fromusername, pmtext.fromuserid
				     FROM pm JOIN pmtext ON pm.pmtextid = pmtext.pmtextid JOIN pm pm2 ON pm.pmtextid = pm2.pmtextid
				   WHERE pm.messageread = 0 AND pmtext.dateline BETWEEN '$datelast' AND '$datenow' ORDER BY pm.pmid DESC")                                   or die (mysql_error());
			while($getpmtext_results=mysql_fetch_assoc($getpmtext))
			{
			   $newpmid=$getpmtext_results['pm.pmid'];
			   $newpmtextid=$getpmtext_results['pm.pmtextid'];
		   echo $newpmtextid;
		}
?>

[requinix]

Don't include the table aliases. Just the field names.