php menu w/submenu :(

Discuss Programming
Post Reply
User avatar
xyle_one
programmer
programmer
Posts: 129
Joined: Mon Jan 13, 2003 1:02 pm
Contact:

php menu w/submenu :(

Post by xyle_one » Wed Nov 19, 2003 11:39 pm

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...

User avatar
Void Main
Site Admin
Site Admin
Posts: 5716
Joined: Wed Jan 08, 2003 5:24 am
Location: Tuxville, USA
Contact:

Post by Void Main » Thu Nov 20, 2003 12:14 am

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...

User avatar
xyle_one
programmer
programmer
Posts: 129
Joined: Mon Jan 13, 2003 1:02 pm
Contact:

Post by xyle_one » Thu Nov 20, 2003 1:11 am

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.

User avatar
xyle_one
programmer
programmer
Posts: 129
Joined: Mon Jan 13, 2003 1:02 pm
Contact:

Post by xyle_one » Fri Nov 21, 2003 11:05 pm

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>";
	}
}

Post Reply