Help: I'm trying to COUNT, SORT, and Link Tables..

[JohnnyKennedy]

Okay, my second post.. Here it goes.

*I know a little bit, but not a lot*

 

I have two tables in MySQL:  debates_com (comments for debates)  AND debates (debate data such as title side1 side2 keywords etc.)

Right now I have (this) as my PHP code -- which is working and returning the right values..ß

 

  <?php require_once('Connections/NewConnection.php'); ?>

<?php

mysql_select_db('sprekn');

 

$query = "SELECT did, owner, COUNT(did) AS didcount FROM debate_com GROUP BY did ORDER BY didcount DESC";

$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){

echo $row['didcount'].": ".$row[did]."<br />";

}

?>

 

 

 

The above code sorts and groups my data accordingly, so that my page displays the number of comments associated with a particular debate.  The problem is the row 'did' (e.g. data 32942349234) doesn't mean anything to my viewers - so i'd like to link the DID value to the 'Debate' table which holds this 'did' value as well as the debate name.

 

So that the value returns  (COMMENT COUNT):  *debate name from debate table*

 

Sorry if this isn't very clear, I'm having a really hard time linking these, and I'm just not sure how to go about it..

Any help would be great, no matter how small.

 

Regards,

 

Jonathan

[silkfire]

You should post this on the MySQL forum not PHP. Anyhow...

 

SELECT debate.did, owner, COUNT(did) AS didcount

FROM debate_com

JOIN debate

ON (debate.did = debate_com.did)

GROUP BY did

ORDER BY didcount DESC

 

 

This "linking" you're talking about is called JOIN. Hope that helped.

[Maq]

You should post this on the MySQL forum not PHP.

Moved.  It should, but in the future if you see something misplaced, don't hesitate to click "report to moderator".