Search the Community

I'm having a bit of a problem which I think I might know the solution, but I'm really struggling to know the correct syntax. If somebody could point me in the right direction I'd be very grateful. I've created an application where users can create projects, and within the projects there are 'outputs' and 'inputs'. All of which is stored in separate MySql tables. It works fine. I have now been asked to create an addition layer, a 'programme' layer, which sits above projects (ie there can be multiple projects within one programme). I think I have got my table normalisation correct, I created 2 tables, 'programmes' which stores programme id and name etc, and 'programmes_projects' which stores only the programme id and the project id. That seems to work ok. What I'm having trouble with, is displaying the results correctly. I need to retrieve information from 4 (actually 5) related MySql tables. Phew. I think I need to create an associative array and a foreach loop, but because of the number of tables and the relationships, I'm getting very confused. I'm not sure if I should be focussing on the sql statements, the php, or what. I tried using while loops, because that's what I know, but wasn't surprised that it didn't work. progId = $_GET['prog']; //get the individual projects from the linking db table $get_projects = "SELECT * FROM programmes_projects WHERE prog_id=$progId"; $run_projects = mysqli_query($conn, $get_projects); $i = 0; while($row_projects=mysqli_fetch_array($run_projects)){ $prog_proj_id = $row_projects['prog_proj_id']; $prog_id = $row_projects['prog_id']; $projectId = $row_projects['proj_id']; $i++; //attempting to collect project details from the outputs table $get_proj_details = "SELECT * FROM projects WHERE project_id=$projectId"; $fetch_projects = mysqli_query($conn, $get_proj_details); $i = 0; while($row_details=mysqli_fetch_array($fetch_projects)){ $proj_name = $row_details['project_name']; $proj_whatever = $row_details['date']; $i++; // project outputs $fetch_outputs = "SELECT * FROM projects_outputs WHERE project_id=$projectId"; $get_outputs = mysqli_query($conn, $fetch_outputs); $i = 0; while($row_outputs=mysqli_fetch_array($get_outputs)){ $out_id = $row_outputs['output_id']; $output_desc = $row_outputs['output_desc']; $i++; }}}?> Please could someone help?? Even if it's just a nudge in the right direction. Thank you very much.