Extract values from lookup tables and display

guoxin · Joined: 11 Feb 2008 Posts: 7

Hi All,

I've encountered a problem which i find for answers but to no avail and i hope you all can help. I've got 4 tables namely, User_Login, Admin_Login, Projects and Mapping.

User_Login
-user_id (PK) INTEGER NOT NULL AUTO INCREMENT
-user_username VARCHAR(30) NOT NULL
-user_password VARCHAR(30) NOT NULL

Admin_Login
-admin_username VARCHAR(30) NOT NULL
-admin_password VARCHAR(30) NOT NULL

Projects
-project_id (PK) INTEGER NOT NULL AUTO INCREMENT
-project_name VARCHAR(30) NOT NULL

Mapping
-user_id (FK)
-project (FK)

Mapping is used as a lookup table when the administrator want to assign projects to the newly created user so we use the user_id from User_Login and project_id form Projects as a reference.

But here comes the problem, i execute the following command, $sql = "SELECT project_name FROM Projects, Mapping, User_Login WHERE Mapping.user_id=User_Login.user_id AND Mapping.project_id=Projects.project_id"; so the scenario is when a particular user is logged in, the projects will be displayed in a dropdown list where user can select which project he/she want to submit. But the dropdown list won't show up anything so i'm really asking you all for help. I'll be really grateful Thanks.

Smile

lostboy · Joined: 02 May 2004 Posts: 5915 Location: toronto, canada

have you run the query thru phpmyadmin or another gui tool for your db? test that the query works and brings back results , then look at your code to make sure you have not spelled a variable incorrectly
_________________
Lostboy

Cat, the other other white meat

Please read Posting Etiquette before posting

You can always try Google

guoxin · Joined: 11 Feb 2008 Posts: 7

I use mysql administrator to run the query and it shows the project "SL220" which i created earlier.

lostboy · Joined: 02 May 2004 Posts: 5915 Location: toronto, canada

so the query works, now show the code you are using to parse out the result and place it into the dropdown
_________________
Lostboy

Cat, the other other white meat

Please read Posting Etiquette before posting

You can always try Google

guoxin · Joined: 11 Feb 2008 Posts: 7

The code is as follows:

$sql = "SELECT project_name FROM Projects, Mapping, User_Login WHERE Mapping.user_id=User_Login.user_id AND Mapping.project_id=Projects.project_id";
$result = @mysql_query($sql,$dbcnx) or die(mysql_error());

while ($row = mysql_fetch_array($result))
{
$display_block .="<option value=\"$id \">$row[0]</option><br/>";
}

<b>Project:     </b>
<select name="acd" size="1">
$display_block

</select>

lostboy · Joined: 02 May 2004 Posts: 5915 Location: toronto, canada

you forgot to echo the $display_block

guoxin · Joined: 11 Feb 2008 Posts: 7

Hi,

I got this code from another forum but there is this sql statement that i'm confused:

<?php
$sql = "SELECT p.project_name FROM Projects p JOIN Mapping m ON m.project_id = p.project_id JOIN User_Login u ON m.user_id = u.user_id WHERE u.user_id ='<user id here>'";
$result = @mysql_query($sql,$dbcnx) or die(mysql_error());
$display_block='';
while($row = mysql_fetch_array($result)){
$display_block .="<option value=\"$id \">$row[0]</option><br/>";
}
?>
<b>Project:     </b>
<select name=\"acd\" size=\"1\">
<?php echo $display_block;?>
</select>

What actually must i put into the '<user id here>' ? Must i put a variable or something like this? Question

lostboy · Joined: 02 May 2004 Posts: 5915 Location: toronto, canada

$user_id or whatever you call the variable that holds the user id for that user
_________________
Lostboy

Cat, the other other white meat

Please read Posting Etiquette before posting

You can always try Google