three tables with intermediate table(m:n) should produce multidimensional array?

[alex999]

Hello,

 

I think it's a beginner question but I don't get any further 

There are three tables:

 

User

User_MM  <-- intermediate table to build a m:n relation between User and MM

MM

 

should produce an array like this with less effort as possible (means I want to have all User's with all their multimedia files as multidimensional array)

Array
(
    [0] => Array
        (
[user] => user1
[real] => Tom
[mm] => Array
	(
	    [0] => file1
	    [1] => file2
	    [2] => file3
	)
)

    [1] => Array
        (
[user] => user2
[real] => AnotherName
[mm] => Array
	(
	    [0] => file1
	    [1] => file99
	)
)
)

 

 

I have a (my-)SQL statement like this:

SELECT User.*, MM.*
FROM User
INNER JOIN User_MM ON User_MM.user_id = User.id
INNER JOIN MM ON MM.id = User_MM.multimedia_id
WHERE 1 = 1

 

Now I get much to much rows, every User multiple times, depending on how much multimedia files are related to this user.

 

How can I do that in a better way?

 

Thank you for help,

Alex

 

 

 

 

 

 

 

 

 

 

[mikosiko]

I get much to much rows, every User multiple times, depending on how much multimedia files

 

with that query:

- implement logic in your code to fill the user's portion of the array one time, the multimedia portion as long as the user doesn't change and if does  start a new element in the array.

 

or

 

make two queries each one to fill a portion of the array... I will no choose this option

[alex999]

Thank you for your help!

 

with that query:

Can I tweak that query somehow, to achieve the same result and to push the data to a multidimensional array more smoothely?

[mikosiko]

no.... AFAIK

[fenway]

You query the DB in whatever way is more efficient -- then it's to you to create you own objects to house the data -- not MySQL.