join tables

[richardsanchez@hotmail]

Hello, I'm trying to get a join with 2 tables but cannot get it to work. It's a contentsystem with an ID for the author. Another table tells the name of the author. Now I want to join these things. This is what i've coded so far:

 

<?

$query = "SELECT rmnl_content.content_aid, COUNT(rmnl_content.content_id), rmnl_crew.crew_id, rmnl_crew.crew_name

 

FROM rmnl_content, rmnl_crew" "where rmnl_content.content_aid = rmnl_crew.crew_id";

 

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

 

// Print out result

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

echo " <b>". $row['COUNT(rmnl_content.content_id)'] ."</b> posted messages by ". $row

 

['crew_rmnl.crew_name'] ."</td> .";

echo "<br />";

}

?>

 

[inversesoft123]

Your query is not clear. but try something like this..

 

$sql = "SELECT a.id, a.file, a.task,  b.id, b.file, b.task  FROM pf_table a INNER JOIN pf_table1 b ON (a.id = b.id) OR (a.fie = b.file) WHERE (b.file='".$file."' OR b.id='".$id."')  GROUP BY 1,2 ORDER BY $something DESC"; 

[richardsanchez@hotmail]

Can't get your method working eather. Anyone else  ?

[PFMaBiSmAd]

You forgot to tell us what result you are getting vs what result you expect.

[kickstart]

Hi

 

You need a GROUP BY to go with the aggregate field (ie, the COUNT).

 

However not sure which fields are shared where. I would guess at the following

 

SELECT rmnl_content.content_aid, rmnl_crew.crew_name, COUNT(rmnl_content.content_id)
FROM rmnl_content
INNER JOIN rmnl_crew
ON rmnl_content.content_aid = rmnl_crew.crew_id
GROUP BY rmnl_content.content_aid, rmnl_crew.crew_name

 

All the best

 

Keith

[richardsanchez@hotmail]

Hi

 

You need a GROUP BY to go with the aggregate field (ie, the COUNT).

 

However not sure which fields are shared where. I would guess at the following

 

SELECT rmnl_content.content_aid, rmnl_crew.crew_name, COUNT(rmnl_content.content_id)
FROM rmnl_content
INNER JOIN rmnl_crew
ON rmnl_content.content_aid = rmnl_crew.crew_id
GROUP BY rmnl_content.content_aid, rmnl_crew.crew_name

 

All the best

 

Keith

 

THX Keith, you helped me a lot! Now only one thing left to get it 100% working. The echo now shows the group by but not the crew_name. I canot figger out whats wrong with it. The code for the echo is:

 

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

 

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

echo " <b>". $row['COUNT(rmnl_content.content_id)'] ."</b> posted by ". $row['rmnl_crew.crew_name'] ." ." ; 

echo "<br />";

 

}

[kickstart]

Hi

 

Alias the count and use the alias name in the PHP.

 

Something like this

 

<?php

$query = "SELECT rmnl_content.content_aid, rmnl_crew.crew_name, COUNT(rmnl_content.content_id) AS ContentCount

FROM rmnl_content

INNER JOIN rmnl_crew

ON rmnl_content.content_aid = rmnl_crew.crew_id

GROUP BY rmnl_content.content_aid, rmnl_crew.crew_name";

 

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

 

// Print out result

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

 

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

echo " <b>". $row['ContentCount'] ."</b> posted by ". $row['rmnl_crew.crew_name'] ." ." ;

echo "<br />";

 

}

?>

 

All the best

 

Keith

[richardsanchez@hotmail]

Hi

 

Alias the count and use the alias name in the PHP.

 

Something like this

 

<?php

$query = "SELECT rmnl_content.content_aid, rmnl_crew.crew_name, COUNT(rmnl_content.content_id) AS ContentCount

FROM rmnl_content

INNER JOIN rmnl_crew

ON rmnl_content.content_aid = rmnl_crew.crew_id

GROUP BY rmnl_content.content_aid, rmnl_crew.crew_name";

 

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

 

// Print out result

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

 

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

echo " <b>". $row['ContentCount'] ."</b> posted by ". $row['rmnl_crew.crew_name'] ." ." ;

echo "<br />";

 

}

?>

 

All the best

 

Keith

 

Thx Keith it works! I also had to alias the crew_name field, so both had to be an allias