Displaying Nested data contained within one Table

[adricist]

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

[ManiacDan]

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

[adricist]

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

 

[adricist]

I solved this by adding an additional query select inside the While loop used to display the selection of records.

 

Thanks again,

Adri

[awjudd]

Errr ... that is horribly inefficient especially when it could be done with a join like Dan said.

 

~awjudd

[adricist]

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

[ManiacDan]

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

[adricist]

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