multidimensional arrrays using mysql values. Too complicated for me

[designergav]

I think I posted this in the wrong section before (mysql help) it's more of a php question. Apologies if you've seen it already, but I'm really stuck and need all the help I can get!
:'(
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
???

[Psycho]

There's a few things wrong. In your query's WHERE clause you have two conditions sparated by a comma - they should have the AND clause. Also, there is a problem because of these two lines:

[code]$query = mysql_query("SELECT projects.projectname, projects.country,...");
$result = mysql_query($query)[/code]

You are running mysql_query() twice! The second one is trying to run a query on the reslt set. I think what you meant to do was this:
[code]$query = "SELECT projects.projectname, projects.country,...";
$result = mysql_query($query)[/code]

Your query is pulling a lot of data that you do not need. Here's a rewrite, use it if you wish or not:
[code]<?php

$query = "SELECT p.projectname, p.country,
          FROM projects p
          JOIN country c
            ON p.country = c.country
          WHERE p.status='past' AND country.status='active'
          ORDER BY p.country ASC, p.projectname ASC";

$result = mysql_query($query) OR die ("The query:<br>".$query."<br>caused the following error:<br>".mysql_error());

if (!mysql_num_rows($result)) {
    echo "No records returned.";
} else {
    $country = "";
    while ($row = mysql_fetch_array($result)) {
        //If new country, close previous UL,
        // display country & open new UL
        if ($country != $row['country']) {
            if ($country) { echo "</ul>\n"; } //Only close UL if not the 1st country
            $country = $row['country'];
            echo $country . "\n<ul>\n";
        }
        //Display the project link list item
        echo "<li>";
        echo "<a href=\"projectdetail.php?project=" . $row[projectname] . "#\">";
        echo $row[projectname];
        echo "</a>"
        echo "</li>";
    }
    //Close the last UL
    echo "</ul>";
}
?>[/code]

[designergav]

Thanks so much. That looks a lot like something I tried and should be the answer.
I'm getting parse error T_CONSTANT_ENCAPSED_STRING on line 1 though.

I checked through and tweaked a few bits but kept getting that message across the first 5 lines or so.
I've seen and tried the format you used for SELECT but have never successfully used it. the format I used although incorrect worked. Is this a version issue with my mysql server?
Thanks

[Psycho]

I can only test this by using my own database, so I can't trouble shoot the problem with 100% certainty.

There is an error on the echo statement that creates the closing anchor tag - there should be a semicolon at the end - but that's nowhere near line 1. If you are getting parsing errors on line 1 then you must have additional code on the page before the code I posted.

The format of my select statement should not be version dependant. It merely creates shorthand 'handles' (forget the correct terminology) for the table names. The query I posted is almost identical to your's with just a few exceptions;
1) I create handles for the table names
2) I included the AND operator int he WHERE clause
3) I removed the additional fields from the SELECT clause that were not needed

Why don't you post the code from the top of the page so we can see what the erro may be.

[designergav]

;D
Thanks for your help on this. It now works. Seems when I was copy/pasting the whitespace was being interpreted as something weird and not as character spaces.

Also seems the JOIN function was causing "no records returned" but decided it wasn't 100% needed so dumped it.

Great stuff.