SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2006
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    PHP MYSQL join shenanigens

    Hi all

    I'm just getting my head around using joins.

    I have two databases and have queried them both with the following code:

    Code PHP:
    $sql = mysql_query("select * from energypost, energycomment where energypost.postedby and energycomment.postedby = $PostByID");

    all is well with this however I have columns in both databases with the same name (entrydate) which is ok until I iterate through to echo the results and it then gets confused i.e

    Code PHP:
    while ($result      = mysql_fetch_array($sql)) {
     
     
    	   $PostedByID = $result['postedby'];        // assigns db entry users id to pull current username from user db and to pass 
    	   $entrydate  = $result['entrydate'];       // assigns db entry of when entered to display and sort by most recent
           $heading    = $result['heading'];         // assigns db entry of heading to display and pass to viewcategory script 
           $tip        = $result['tip'];             // asigns db entry of the actual posted tip for display
    	   $comment    = $result['comment'];
     
    	   echo "Posted By " . $PostedByID . "<br/>";
    	   echo "Entrydate " . $entrydate . "<br/>";
    	   echo "Heading   " . $heading . "<br/>";
    	   echo "Tip       " . $tip . "<br/>";
    	   echo "Comment   " . $comment . "<br/><br/>";	   
     }

    is there anyway to differentiate between the different entrydates when echoing out or the way it is placed in or pulled out of the array ?

    Any thoughts would bve most helpful..

    Thanks in anticipation.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,510
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Code:
    SELECT 
        ep.field1 AS epfield1
      , ep.field2 AS epfield2
      , ec.field1 AS ecfield1
      , ec.field2 AS ecfield2
    FROM energypost AS ep
    INNER JOIN energycomment AS ec
    ON ep.postedby = ec.postedby 
    WHERE ep.postedby = $PostByID
    Don't use the * to select the fields, but specify them one by one. You can then give them a unique alias.
    I also gave an alias to the tables, to avoid having to type the entire table name multiple times

  3. #3
    SitePoint Enthusiast
    Join Date
    Apr 2006
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the response that queries both db as desired howevever when I iterate through as described in the below test code:

    Code PHP:
    $sql = mysql_query("SELECT 
    	   ep.id        AS epID,
    	   ep.postedby  AS epPostedBy,
    	   ep.entrydate AS epEntrydate,
    	   ep.heading   AS epHeading,
    	   ep.tip       AS epTip,
    	   ec.id        AS ecID,
    	   ec.postedby  AS ecPostedBy,
    	   ec.entrydate AS ecEntrydate,
    	   ec.postid    AS ecPostID,
    	   ec.comment   AS ecComment
     
    	   FROM energypost AS ep INNER JOIN energycomment AS ec ON ep.postedby = ec.postedby WHERE ep.postedby = $PostByID ");
     
     
    $i= 1;
     
    while ($result      = mysql_fetch_array($sql)) {
     
    echo "Loop :" . $i . "<br/><br/>";	
     
    echo "Energy Post ID:               " . $result['epID']       . "<br/>";
    echo "Energy Post Posted By ID:     " . $result['epPostedBy'] . "<br/>";
    echo "Energy Post Entrydate:        " . $result['epEntrydate']. "<br/>";
    echo "Energy Post Heading:          " . $result['epHeading']  . "<br/>";
    echo "Energy Post Tip:              " . $result['epTip']      . "<br/><br/>";
    echo "Energy Commment ID:           " . $result['ecID']       . "<br/>";
    echo "Energy Comment Posted By ID:  " . $result['ecPostedBy'] . "<br/>";
    echo "Energy Comment Entry Date:    " . $result['ecEntrydate']. "<br/>";
    echo "Energy Comment Post ID:       " . $result['ecPostID']   . "<br/>";
    echo "Energy Comment:               " . $result['ecComment']  . "<br/><br/>";
     
    $i++;
    }

    However variations of each possible combination is being looped out repeating some of the entries several times (I'll include it below for purposes of clarity, hope it helps)

    Loop :1

    Energy Post ID: 66
    Energy Post Posted By ID: 1
    Energy Post Entrydate: 2009-03-30 20:24:12
    Energy Post Heading: Heating
    Energy Post Tip: Shut Curtains

    Energy Commment ID: 20
    Energy Comment Posted By ID: 1
    Energy Comment Entry Date: 2009-03-30 22:06:39
    Energy Comment Post ID: 67
    Energy Comment: Mine don't open !!!

    Loop :2

    Energy Post ID: 68
    Energy Post Posted By ID: 1
    Energy Post Entrydate: 2009-03-30 22:31:53
    Energy Post Heading: Electrical
    Energy Post Tip: ZZzzzzz

    Energy Commment ID: 20
    Energy Comment Posted By ID: 1
    Energy Comment Entry Date: 2009-03-30 22:06:39
    Energy Comment Post ID: 67
    Energy Comment: Mine don't open !!!

    Loop :3

    Energy Post ID: 66
    Energy Post Posted By ID: 1
    Energy Post Entrydate: 2009-03-30 20:24:12
    Energy Post Heading: Heating
    Energy Post Tip: Shut Curtains

    Energy Commment ID: 21
    Energy Comment Posted By ID: 1
    Energy Comment Entry Date: 2009-03-30 22:09:02
    Energy Comment Post ID: 67
    Energy Comment: Mine don't open....Have I already said this ??

    Loop :4

    Energy Post ID: 68
    Energy Post Posted By ID: 1
    Energy Post Entrydate: 2009-03-30 22:31:53
    Energy Post Heading: Electrical
    Energy Post Tip: ZZzzzzz

    Energy Commment ID: 21
    Energy Comment Posted By ID: 1
    Energy Comment Entry Date: 2009-03-30 22:09:02
    Energy Comment Post ID: 67
    Energy Comment: Mine don't open....Have I already said this ??

    Loop :5

    Energy Post ID: 66
    Energy Post Posted By ID: 1
    Energy Post Entrydate: 2009-03-30 20:24:12
    Energy Post Heading: Heating
    Energy Post Tip: Shut Curtains

    Energy Commment ID: 22
    Energy Comment Posted By ID: 1
    Energy Comment Entry Date: 2009-03-30 22:29:27
    Energy Comment Post ID: 66
    Energy Comment: I can't afford curtains...

    Loop :6

    Energy Post ID: 68
    Energy Post Posted By ID: 1
    Energy Post Entrydate: 2009-03-30 22:31:53
    Energy Post Heading: Electrical
    Energy Post Tip: ZZzzzzz

    Energy Commment ID: 22
    Energy Comment Posted By ID: 1
    Energy Comment Entry Date: 2009-03-30 22:29:27
    Energy Comment Post ID: 66
    Energy Comment: I can't afford curtains...

    Ideally I would like to display each entry only once and order them by entrydate of both tables. I can order the results from one table but I am struggling with the syntax to sort from a join......

    Once again thanks in anticipation and hope to hear from you soon.

  4. #4
    SitePoint Enthusiast
    Join Date
    Apr 2006
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well I can see that's got you all stumped though in fairness I think I posted in the wrong section and really this is a mysql issue.

    I posted over in the mysql forum and was kindly helped out by r937 who came up with the following code:

    Code SQL:
    SELECT tip, NULL AS comment, entrydate FROM energypost
    UNION ALL
    SELECT NULL AS tip, comment, entrydate FROM energycomment
    ORDER BY entrydate ASC

    Hope this helps anyone who ends up down this road.

    Regards

  5. #5
    SitePoint Evangelist captainccs's Avatar
    Join Date
    Mar 2004
    Location
    Caracas, Venezuela
    Posts
    516
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jominsons View Post
    I have two databases and have queried them both...

    all is well with this however I have columns in both databases with the same name (entrydate) which is ok until I iterate through to echo the results and it then gets confused i.e.
    You can't have duplicate keys in associative arrays, the second one overrides the first. This code:

    PHP Code:
    $test = array();
    $test['a'] = 'first a';
    $test['b'] = 'first b';
    $test['a'] = 'second a';
    $test['c'] = 'first c';

    print_r ($test); 
    will print out:

    Code:
    Array
    (
        [a] => second a
        [b] => first b
        [c] => first c
    )
    'first a' is lost. The solution is either to have unique column names as has been suggested by guido2004 or to use numeric array instead which is messy because you have to know the position of every field

    PHP Code:
    $numericArray mysql_fetch_array ($result MYSQL_NUM); 

    Also, there is no reason to pull the results from the array to individual variables since you can echo them directly from the array:

    PHP Code:
    echo "Posted By " $result['PostedByID'] . "<br/>"
    Denny Schlesinger
    web services


Bookmarks

Posting Permissions

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