PHP MySQL: One-To-Many Query ... help??

[azukah]

I have 2 tables (see code below)

(1) "tbl_users" has all users info, including "user_alias" (VARCHAR) and "user_id" (INT and PRIMARY KEY)

(2) "tbl_projects" has all project info, including "project_client" (INT) which i linked to "user_id" from "tbl_users" and displays "user_alias"

 

CREATE TABLE `tbl_users` (
  `user_id` int(11) NOT NULL auto_increment,
  `user_first` varchar(25) NOT NULL,
  `user_last` varchar(25) NOT NULL,
  `user_email` varchar(35) NOT NULL,
  `user_pw` varchar(12) NOT NULL,
  `user_role` int(1) NOT NULL,
  `user_alias` varchar(35) NOT NULL,
  PRIMARY KEY  (`user_id`)
)

CREATE TABLE `tbl_projects` (
  `project_id` int(11) NOT NULL auto_increment,
  `project_client` int(1) NOT NULL,
  PRIMARY KEY  (`project_id`)
) 

 

So... I managed to display all users and all projects but I want to show all users with ONLY their projects assigned. I mean, when I click on a user, I want to see his or hers contact info and all the projects that specific user is linked to.

 

For instance, i might have project 1, project 2, project 3 and project 4. Project 1, 2 and 4 are linked to user 1 so when i click on user 1 i see projects 1, 2 and 3 (not 4).

 

<?php 
require_once('config.php');
mysql_select_db($database, $makeconnection);

//this displays all projects
$sql_get_projects="SELECT *
FROM tbl_projects
ORDER BY project_id ASC";
$get_projects = mysql_query($sql_get_projects, $makeconnection) or die(mysql_error());
$row_get_roles = mysql_fetch_assoc($get_projects);
$totalRows_get_projects = mysql_num_rows($get_projects);

//this displays all users
$sql_find_users = "SELECT * 
FROM tbl_users 
WHERE user_id = $user_id";
$find_users = mysql_query($sql_find_users, $makeconnection) or die(mysql_error());
$row_get_users = mysql_fetch_assoc($find_users);
$totalRows = mysql_num_rows($find_users);

?>

 

Any help would be great!!

[WebStyles]

You want to choose a user and show the projects that belong to him/her? Since you said project_client stores the user id's... (assuming the user's id is in a variable called $userID)

 

$sql_get_projects=mysql_query("select `project_id` from `tbl_projects` where `project_client` = '$userID' order by `project_id`",$makeconnection);
while($result = mysql_fetch_assoc($sql_get_projects)){
    echo $result['project_id'].'<br />';
}
@mysql_close($makeconnection);

[azukah]

brilliant  - thanks a lot mate !!!