View Full Version : join two tables

09-16-2011, 06: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());

$getnewpm=mysql_query("SELECT * from pmtext WHERE pmtextid='$pmtextid' AND dateline >='$datelast' AND dateline <='$datenow'") or die (mysql_error());

$getnewpmid=mysql_query("SELECT pmid from pm WHERE pmtextid='$newpmtextid'") or die (mysql_error());
echo "NEW PM:<b> &nbsp;".$pmtitle."</b>&nbsp;FROM:&nbsp;<b>".$fromusername."</b><br>";

Old Pedant
09-16-2011, 09: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, 09:46 PM
Why do you need the "newpmid" when you don't use it in the echo???