Jump to content

Displaying Nested data contained within one Table


Recommended Posts

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

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

 

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

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

Dan thank you heaps!  :D

 

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.