azukah Posted September 6, 2011 Share Posted September 6, 2011 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!! Link to comment https://forums.phpfreaks.com/topic/246580-php-mysql-one-to-many-query-help/ Share on other sites More sharing options...
WebStyles Posted September 7, 2011 Share Posted September 7, 2011 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); Link to comment https://forums.phpfreaks.com/topic/246580-php-mysql-one-to-many-query-help/#findComment-1266256 Share on other sites More sharing options...
azukah Posted September 7, 2011 Author Share Posted September 7, 2011 brilliant - thanks a lot mate !!! Link to comment https://forums.phpfreaks.com/topic/246580-php-mysql-one-to-many-query-help/#findComment-1266262 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.