SitePoint Sponsor |
|
User Tag List
Results 1 to 5 of 5
Thread: PHP MYSQL join shenanigens
-
Mar 31, 2009, 06:23 #1
- 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.
-
Mar 31, 2009, 06:29 #2Code:
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
I also gave an alias to the tables, to avoid having to type the entire table name multiple timesGuido - Community Team Leader
The Votes Are In: The Winners of the 2013 Community Awards are...
Blog - Free Flash Slideshow Widget
-
Mar 31, 2009, 16:46 #3
- 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.
-
Apr 3, 2009, 15:11 #4
- 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
-
Apr 3, 2009, 19:20 #5
- Join Date
- Mar 2004
- Location
- Caracas, Venezuela
- Posts
- 516
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
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);
Code:Array ( [a] => second a [b] => first b [c] => first c )
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