PHP MYSQL join shenanigens

[jominsons]

Hi all

I'm just getting my head around using joins.

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

$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

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.

[guido2004]

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

[jominsons]

Thanks for the response that queries both db as desired howevever when I iterate through as described in the below test code:

$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.

[jominsons]

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:

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

[captainccs]

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:

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

print_r ($test); 

will print out:

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

$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:

echo "Posted By " . $result['PostedByID'] . "<br/>";