loop, loop & loop.

[vent]

i thought i knew how to do this but its spinning me out.
got 2 tables, one for project info, 1 for file ref.

projects table=
client, project_title, info, file_ref.
files table=filename, file_ref.

project can be put in for more than 1 client so i have used group by CLIENT. works file. but the i want to list the projects for that client before the next client gets listed and so on. so list projects where client = client... anf then i want to list the files for each project.

so i need something that come out like this.

client1
-project1
--filename1
--filename2
-project2
--filename1

client2
-project1
--filename1
--filename2
-project2
-filename1
--filename2

and so on. How is this laid out in php, what goes where?
many thanks. dan.

<?PHP
require("setup.php");

$sql = mysql_query("SELECT * FROM jynk_projects GROUP BY client");
if($myrow = @mysql_fetch_array($sql)){
$i = 0;
do{
$id = $myrow["id"];
$project = $myrow["project"];
$file_ref = $myrow["file_ref"];
$date_e = $myrow["date_e"];
$p_copy = $myrow["p_copy"];
$status = $myrow["status"];
$client = $myrow["client"];

    	print "&id".$i."=$id";
    	print "&client".$i."=$client&<br>

";
$i++;
}while($myrow = mysql_fetch_array($sql));
}
print("&client_result=$i");
mysql_close($db);
?>

[tsinka]

Hi,

I think a different database layout would be the first step to solve the problem:

tblClient (contains client info):

lngClientID (auto_increment, primary key)
strClientName (varchar(x) x=>maximum client name length, maybe a key over 10-15 characters if you want to order by name)

tblProject (contains project info):

lngProjectID (auto_increment, primary key)
strProjectTitle (varchar(x))
strInfo (varchar(x))

tblFile (contains file info):
lngFileID (auto_increment, primary key)
strFilename (varchar(x))

tblClient2Project (map clients to projects):
lngClientID ( no auto_increment, key)
lngProjectID (no auto_increment, key)

tblProject2File (map files to projects):
lngFileID (no aut_increment, key)
lngProjectID (no auto_increment, key)

This should reduce redundancies in your database.

This looks a bit complicated at the first look but might make things easier (there might be an easier solution, though 🙂)

You might be able to do stuff with just one query but I'd prefer a solution with several queries:

$result = mysql_query("SELECT * FROM tblClient ORDER BY strClientName"); 

while ($clientRow=@mysql_fetch_array($result)) {
    $lngClientID = $clientRow["lngClientID"];
    $strClientName = $clientRow["strClientName"];
    echo "ID: ".$lngClientID." - Client: ".$strClientName."<br>\n";

$result2 = mysql_query("SELECT prj.* FROM tblClient2Project c2p, tblProject prj WHERE c2p.lngClientID='".$lngClientID."'");
while ($projectRow=@mysql_fetch_array($result2)) {
    $strProjectTitle = $projectRow["strProjectTitle"];
    $lngProjectID   = $projectRow["lngProjectID"];
    echo "- Project: ".$strProjectTitle."<br>\n";

    $result3 = mysql_query("SELECT fil.* FROM tblProject2File p2f, tblFile fil WHERE p2f.lngProjectID=$lngProjectID");
    while ($fileRow=@mysql_fetch_array($result3)) {
        $strFilename = $fileRow["strFileName"];
        echo "-- File: ".$strFilename."<br>\n";
    }
}
}

You could do this with just one query but you would then have to do a lot more in PHP to find out when you would have to print which elements of the result.

I couldn't test the above code for any erros but something like that should work.

Thomas