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

Cesar Aracena
Cesar Aracena used Ask the Experts™
on
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!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
PHP Enthusiast
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

;)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial