count in (one field) from 2 tables

[Anon]

o.k my problem is that I´m trying to count how many pictures are in a picture archive. I have one table to declare the archives(MYNDASAFN) and another(MYND) to keep the pictures in it. Here´s the code I´m using:

<?
$db = mysql_connect("localhost",$user,$pass);
mysql_select_db($dbname,$db);

$query = "SELECT COUNT(MYND.msid) AS mcount, MYNDASAFN.msid AS msid, MYNDASAFN.texti AS texti, MYNDASAFN.dags AS dags
FROM MYND, MYNDASAFN WHERE MYNDASAFN.msid = MYND.msid ORDER BY MYNDASAFN.dags desc";
$result = mysql_db_query($dbname, $query);

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

$count = $r["mcount"];
$msid = $r["msid"];
$texti = $r["texti"];
$dags = $r["dags"];

print ("<a href=\"?sida=birtamyndir&msid=$msid\" class=\"dags\">$texti, $count</a><br><br>\n");
}
// the print() is supposed to echo the
// archive name and the number of
// pictures it contains (and link to it)

// sorry for all the icelandic variable
// names :-)

?>

[Anon]

Your sql is a little disorderly, what you need to do is take advantage of GROUP BY

SELECT COUNT(MYND.msid) AS mcount, MYNDASAFN.* FROM MYND LEFT JOIN MYNDASAFN ON MYNDASAFN.msid = MYND.msid GROUP BY MYNDASAFN.msid ORDER BY MYNDASAFN.dags

That should do what you want it to. (I think, I really don't know what the hell a Myndasafn is, sounds pretty scary though) .. hope that helps!

Chris King

[halez]

Thanx,

I just pasted your SQL syntax into my code and it works great ! Thank you.

-halez