Select data four times from 3 tables with a single query in PHP from MySQL

Hello experts!

I need to make a joined query in PHP to get data from one table, depending on which user makes the request. In a previous question, I learned how to do exactly that but, this time I also need to get data from a second table. This is my schema:

DB schema
What I need can be described in the following scenario:

- User John (user_id 1) needs to see all the entries to which he has access to.
- PHP fetches him all the entries because the matrix table says that users with the user_category 1 can see every entry from entry_category 1 and 2.

I've got so far. The problem is that I also need the info for the author of the entry. The way I'm getting it, it brings me the entries and the info for the user requesting it. I guess (a wild guess) I need to add an OUTTER JOIN or something like that to my query? This is what I have:

SELECT entry.*, user.*
FROM entries entry
INNER JOIN matrix mx ON entry.entry_category = mx.matrix_entry_category
INNER JOIN users user ON mx.matrix_user_category = user.user_category
WHERE user.user_id = :uid
ORDER BY entry.entry_id ASC
LIMIT 10

Open in new window

Later, the :uid param will be binded to the user requesting the entries (browsing the page).

This will give me a multidimensional array with one key for each entry and user info, but not the user that wrote the entry. It will just repeat 10 times the info for the person browsing.

What I need would be something like this:

Array
(
    [0] => Array
        (
            [entry_id] => 1
            [entry_category] => 1
            [author_id] => 1
            [entry_description] => Description
            [user_name] => John
        )

    [1] => Array
        (
            [entry_id] => 2
            [entry_category] => 1
            [author_id] => 3
            [entry_description] => Another description
            [user_name] => Helen
        )

    [2] => Array
        (
            [entry_id] => 3
            [entry_category] => 2
            [author_id] => 2
            [entry_description] => Yet another one
            [user_name] => Carl
        )

    [3] => Array
        (
            [entry_id] => 4
            [entry_category] => 2
            [author_id] => 4
            [entry_description] => Different description
            [user_name] => Laura
        )

    [4] => Array
        (
            [entry_id] => 5
            [entry_category] => 2
            [author_id] => 2
            [entry_description] => Yes, a decription
            [user_name] => Carl
        )
)

Open in new window


Thanks in advance!
LVL 6
Cesar AracenaPHP EnthusiastAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Cesar AracenaPHP EnthusiastAuthor Commented:
Ok, I found it with the following query:

SELECT entry.*, userx.*
FROM entries entry
INNER JOIN matrix mx ON entry.entry_category = mx.matrix_entry_category
INNER JOIN users user ON mx.matrix_user_category = user.user_category
INNER JOIN users userx ON entry.author_id = userx.user_id
WHERE user.user_id = :uid
ORDER BY entry.entry_id ASC
LIMIT 10

Open in new window

;)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.