Multidimensional arrays with join table

[designergav]

:'(
I'm a designer who's been made to develop a site on the fly and I have only 3months php exp. but have learnt loads. However I'm now really stuck. Here goes.

I have a mysql db. Table 1 has list of countries with loads of fields including [i]country , status[/i] used here. Table 2 is projects including [i]country (matches table 1) , projectname , status (no relation to table 1)[/i]

This is what I'm trying to do with no luck. create an array that uses this format (where project names are links in <ul><li> format):
Country 1
     Projectname1
     Projectname2
     Projectname3
Country2
     Projectname4
     Projectname5
etc...

I need to select only countries that have status=active in table 1 find equivalent country name in table 2, check that status=past [i](there are three lists req. past, present, future but i assume the simply answer would be to duplicate code and change status=)[/i] in table 2 and display each entry ORDER BY country then project name ASC. All in an unordered list.

Here's my (unfinished) code. I get "Query was empty" error message.

Any help would be greatly appreciated.

[code]
<?
$query = mysql_query("SELECT projects.projectname, projects.country, projects.status, country.country, country.status FROM projects JOIN country ON projects.country = country.country WHERE projects.status='past', country.status='active' ORDER BY projects.country, projects.projectname ASC");
$result = mysql_query($query)
or die (mysql_error());
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_array($result)) {
$pastarray[$row['country']] [] = $row['projectname'];
  }
  foreach ($pastarray as $key => $value) {
    $projects[$key] = implode(", ", $value);
echo "<li><a href=\"projectdetail.php?project=";
echo $key;
echo "#\">";
echo $value;
echo "</a></li>";
}
}
[/code]

Thanks everyone.
Gavin
KEEPING THE CREATIVE FIRE BURNING BRIGHT
www.thecreativefire.co.uk
???

[fenway]

Well, first, your query as a comma instead of an "AND" in the where clause.

[designergav]

Thanks.
I changed that but still no joy. I think there may be a bigger issue with the relationship between all those WHERE statements.

[fenway]

So you're still getting back an error, or no results?