adricist Posted November 25, 2011 Share Posted November 25, 2011 Hi there, Here is the scenario. I have the following 2 Tables: Table 1 is called jos_cp_fb_moderation It has 2 fields catid userid Table 2 is called jos_cp_fb_categories It has 3 fields id parent name For the time being I will simulate some of the data in the tables: Table 1 catid | userid 1 | 62 1 | 809 2 | 62 2 | 809 3 | 617 4 | 617 Table 2 id | parent | name 1 | 0 | CLUB ADMINISTRATION 2 | 0 | MEMBERSHIP 3 | 1 | Attendance & Development 4 | 2 | Classification 5 | 1 | Administration Systems 6 | 1 | Communications 7 | 0 | GENERAL FEEDBACK Currently I am able to run the following command $result = @mysql_query('SELECT userid, catid, id, parent, name FROM jos_cp_fb_moderation INNER JOIN jos_cp_fb_categories ON catid = jos_cp_fb_categories.id WHERE jos_cp_fb_moderation.userid = 617 '); And produce the following view Id | Name | parent | Name 3 | Attendance & Development | 1 | 4 | Classification | 2 | How do I get the Name also filled for the parent? Basically I am after the following: Id | Name | parent | Name 3 | Attendance & Development | 1 | CLUB ADMINISTRATION 4 | Classification | 2 | MEMBERSHIP Thanks, Adri Quote Link to comment https://forums.phpfreaks.com/topic/251760-displaying-nested-data-contained-within-one-table/ Share on other sites More sharing options...
ManiacDan Posted November 25, 2011 Share Posted November 25, 2011 JOIN on the table again using an alias. SELECT Name AS child_name, Name AS parent_name FROM theTable AS child JOIN theTable AS parent ON child.parent_id = parent.id -Dan Quote Link to comment https://forums.phpfreaks.com/topic/251760-displaying-nested-data-contained-within-one-table/#findComment-1291056 Share on other sites More sharing options...
adricist Posted November 25, 2011 Author Share Posted November 25, 2011 Hi Dan, And thank you for your reply. Unfortunately you need to be quite gentle with me as my SQL skills are limited, though I am fast learner. I sort of understand what you have in mind but I do not seem to be able to make it work. What I have tried so far is the following (please note that this is my PHP code) $result = @mysql_query('SELECT userid, catid, id, parent, name FROM jos_cp_fb_moderation INNER JOIN jos_cp_fb_categories ON catid = jos_cp_fb_categories.id WHERE jos_cp_fb_moderation.userid = 617'); $result1 = @mysql_query('SELECT userid, catid, id, parent, name FROM jos_cp_fb_moderation INNER JOIN jos_cp_fb_categories ON catid = jos_cp_fb_categories.parent WHERE jos_cp_fb_moderation.userid = 617'); [/Code] Rather that running an Alias I thought I would run a separate Query Array $result1 and then I would try to interrogate both. But I am a bit lost in how to cycle through both arrays. The below code is not working. [Code] $row1 = mysql_fetch_array($result1); while ($row = mysql_fetch_array($result)) { echo '<table style="text-align: left; width: 100%;" border="0" cellpadding="0" cellspacing="0"> <tbody> <td style="font-size: 14px; font-family: Tahoma; width: 5%;">' . $row['catid'] . '</td> <td style="font-size: 14px; font-family: Tahoma; width: 25%;">' . $row['name'] . '</td> <td style="font-size: 14px; font-family: Tahoma; width: 5%;">' . $row['parent'] . '</td> <td style="font-size: 14px; font-family: Tahoma; width: 25%;">' . $row1['name'] . '</td> <td style="font-size: 14px; font-family: Tahoma; width: 40%;"></td> </tbody> </table>'; } [/Code] I know that this isn't the approach you suggested but your comment made me think of trying this. As this hasn't worked, I was wondering if you can be a bit more specific in detailing how exactly I should make the additional JOIN with the ALIAS. I know that you have given me the code as well but unfortunately I am not quite able to work out how to apply it to my PHP/SQL scenario. Basically what I am asking is 1) Do I need to run a separate $resultx SELECT ? or 2) Can I combine both SELECT/JOINS in the same $result ? Thanks heaps for all your help, Adri Quote Link to comment https://forums.phpfreaks.com/topic/251760-displaying-nested-data-contained-within-one-table/#findComment-1291059 Share on other sites More sharing options...
adricist Posted December 2, 2011 Author Share Posted December 2, 2011 I solved this by adding an additional query select inside the While loop used to display the selection of records. Thanks again, Adri Quote Link to comment https://forums.phpfreaks.com/topic/251760-displaying-nested-data-contained-within-one-table/#findComment-1293296 Share on other sites More sharing options...
awjudd Posted December 2, 2011 Share Posted December 2, 2011 Errr ... that is horribly inefficient especially when it could be done with a join like Dan said. ~awjudd Quote Link to comment https://forums.phpfreaks.com/topic/251760-displaying-nested-data-contained-within-one-table/#findComment-1293311 Share on other sites More sharing options...
adricist Posted December 2, 2011 Author Share Posted December 2, 2011 awjudd, I am sure you are correct but unfortunately my inexperience did not allow me to understand what Dan had suggested. Furthermore, if you read my second post, I asked Dan for further explanations but I never heard back. Don't get me wrong, I am not blaming Dan for not spending the time to further explain a basic SQL concept that most developers would have understood, but as I said, I am new to SQL and I have just started learning it. If however you care to explain it further I would be very happy to hear from you and very keen to improve my SQL skills. Thanks again, Adri Quote Link to comment https://forums.phpfreaks.com/topic/251760-displaying-nested-data-contained-within-one-table/#findComment-1293319 Share on other sites More sharing options...
ManiacDan Posted December 2, 2011 Share Posted December 2, 2011 Missed your reply: SELECT jos_cp_fb_moderation.userid AS userId, child.id AS categoryId, child.name AS categoryName, parent.id AS parentCategoryId, parent.name AS parentCategoryName FROM jos_cp_fb_moderation JOIN jos_cp_fb_categories AS child ON jos_cp_db_moderation.catid = child.id LEFT JOIN jos_cp_fb_categories AS parent ON child.parent = parent.id WHERE jos_cp_fb_moderation.userid = 617 -Dan Quote Link to comment https://forums.phpfreaks.com/topic/251760-displaying-nested-data-contained-within-one-table/#findComment-1293503 Share on other sites More sharing options...
adricist Posted December 5, 2011 Author Share Posted December 5, 2011 Dan thank you heaps! I will test sometime this week and report back my findings, but I know get the idea of what you meant by additional JOIN and how the SQL command is structured! I really appreciate your help and teachings! Thanks heaps, Adri Quote Link to comment https://forums.phpfreaks.com/topic/251760-displaying-nested-data-contained-within-one-table/#findComment-1294456 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.