bevc Posted May 30, 2017 Share Posted May 30, 2017 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. Link to comment Share on other sites More sharing options...
Larry Posted May 30, 2017 Share Posted May 30, 2017 Hey! This is complicated, so don't feel bad if it seems that way. You really want to start by getting the query right outside of PHP: using phpMyAdmin, the command line mysql client, or whatever. The goal is to get the right query that gives you the proper table of data. Once you have that, you can go back to the PHP script, use a while loop to fetch each row and then use the array within the while loop that represents a single row of data. To build up the JOIN across 4 or 5 tables, start with just 2 or 3. Select your ID columns--the foreign keys--accordingly. Then, for each foreign key column, build up your query to replace the ID value with what you want from the related table, adding in the JOIN needed. Do this one additional table at a time and you'll get there. Let me know if you have any other questions or problems along the way! 1 Link to comment Share on other sites More sharing options...
bevc Posted June 1, 2017 Author Share Posted June 1, 2017 That worked, thank you very much indeed, I should have been concentrating on the SQL statement(s) and checking them first in phpmyadmin! Once I had got that right I felt much more confident in what I was doing Link to comment Share on other sites More sharing options...
Larry Posted June 1, 2017 Share Posted June 1, 2017 Awesome. I'm glad to hear it and thanks for letting us know! Link to comment Share on other sites More sharing options...
Recommended Posts