php menu w/submenu :(

[xyle_one]

Im not going to ask anyone to do this for me, however i would love some direction.

What i want is to have a nav:

Client x
----project1
----project2
Client y
----project1
Client z
----project3
----project3
----project3

My database looks like:
projects
+-----+-----------------+-----+
| PID | PName | CID |
+-----+-----------------+-----+
| 15 | Project1 | 1 |
| 16 | Project2 | 6 |
| 14 | Project3 | 2 |
| 17 | Project4 | 2 |
+-----+-----------------+-----+
clients
+------------+-----------------------------+
| clients_ID | clients_Name |
+------------+-----------------------------+
| 1 | Client1 |
| 2 | Client2 |
| 3 | Client3 |
| 5 | Client4 |
| 6 | Client5 |
+------------+-----------------------------+

I am trying to figure out where to start. I thought i could use a loop within a loop, but my meager attempt ended in frustration.
Maybe i a going about this all wrong, but here is what i have:

Code: Select all

$sql=mysql_query("SELECT * FROM  clients ORDER BY clients_Name ASC LIMIT 0, 10");
$sql2=mysql_query("SELECT * FROM  projects ORDER BY PName ASC LIMIT 0, 10");
while ($select=mysql_fetch_array($sql)){
     //get client names
     $var1=$select["clients_Name"];
     $var2=$select["clients_ID"];
     while ($select2=mysql_fetch_array($sql2)){
          //get projects where CID = $clients_ID;
          $var3=$select2["PName"];
          $var4=$select2["PID"]; 
          $var5=$select2["CID"];
          }
     echo"$var1:$var2<br>$var3:$var4<br>";
}


I have been looking for help elsewhere, but really havent found much. Im currently looking through the sql documentation and scouring the web for more php info.

Thanks in advance...

[Void Main]

If I understand you right and you want this to be a relational database then I think what you want is a project table with "pid" and "project name" columns only. You want the client table to have the "cid" and "client name" columns only Each of those tables will contain only unique rows. Then you'll have a 3rd table that would contain "pid" and "cid" columns only.

projects:

Code: Select all

pid       project
 1         project 1
 2         project 2
 3         project 3


clients:

Code: Select all

cid      client
 1        client 1
 2        client 2
 3        client 3


nav:

Code: Select all

 cid    pid
   1      1
   1      2
   2      1
   3      1
   3      2
   3      3


Then you put them together with a carefully crafted piece of SQL:

Code: Select all

SELECT client,project FROM nav
   INNER JOIN clients ON nav.cid = clients.cid
   INNER JOIN projects ON nav.pid = projects.pid


which returns something like:

Code: Select all

+----------+-----------+
| client   | project   |
+----------+-----------+
| client 1 | project 1 |
| client 1 | project 2 |
| client 2 | project 1 |
| client 3 | project 1 |
| client 3 | project 2 |
| client 3 | project 3 |
+----------+-----------+


But I could be completely missing what you are getting at...

[xyle_one]

i think you understood what i was getting at.

I want to pull the names from the clients table for the main nav. Then, under each client, list the projects that belong to that client as a link to project.php.

I thought that i could use the design i had now to do this effectively.

I will have to go back to the drawing board so to speak. And read alot more about relational database design.

[xyle_one]

Well. I got it working. Mostly. It does what i need it to right now. I downloaded the sql documentation and am going to read what i can tonight. This script would be alot better using the join you outlined above. With the way it is now, it displays the clients reagardless of whether or not there is a project associated with them.
Anyways. That is what im doing tonight

Code: Select all

$sql=mysql_query("SELECT * FROM clients");
while($select = mysql_fetch_array($sql)){
   $client=$select["clients_Name"];
   $clientid=$select["clients_ID"];
   echo "<div class=menu>$client:</div>";
   $sql1=mysql_query("SELECT * FROM projects WHERE CID = '$clientid'");
   while($select1 = mysql_fetch_array($sql1)){
   $projectid=$select1["PID"];
   $projectname=$select1["PName"];
   echo "<div class=sub><a href=?content=2&port=$projectid>$projectname</a></div>";
   }
}