Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3

Thread: join two tables

  1. #1
    Regular Coder
    Join Date
    Aug 2011
    Posts
    134
    Thanks
    20
    Thanked 0 Times in 0 Posts

    join two tables

    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:


    Code:
    <?
    $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>";
                          }
                        }
                     }
    ?>

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,151
    Thanks
    80
    Thanked 4,559 Times in 4,523 Posts
    Yes, and you should.
    Code:
    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
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,151
    Thanks
    80
    Thanked 4,559 Times in 4,523 Posts
    Why do you need the "newpmid" when you don't use it in the echo???
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •