...

View Full Version : join two tables



coding_begins
09-16-2011, 05:41 PM
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> &nbsp;".$pmtitle."</b>&nbsp;FROM:&nbsp;<b>".$fromusername."</b><br>";
}
}
}
?>

Old Pedant
09-16-2011, 08:45 PM
Yes, and you should.


SELECT t.title, t.fromusername, pm2.pmid AS newpmid
FROM pm AS pm1, pmtext, pm AS pm2
WHERE messageread=0
AND pmtext.pmtextid = pm1.pmtextid
AND dateline BETWEEN '$datelast' AND '$datenow'
ORDER by pm1.pmid desc

Old Pedant
09-16-2011, 08:46 PM
Why do you need the "newpmid" when you don't use it in the echo???



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum