Return MySQL query as multidimensional array

Question

I have an SQL query, where i do some joins, but when i var_dump the result in PHP i get an array, with a lot of duplicates. Is there any way to get the result back as an multidimensional array like:

array( array("projectname", "projectdescription", "projectthumb" , "activestate"),
           array("img1.jpg", "img2.jpg", "img3.jpg"),
           array("tech1", "tech2", "tech3") 
         );

At the moment i get the following result: http://pastebin.com/ZF7yiafS

The SQL query:

SELECT    p.name,
                              p.description,
                              p.img_thumb,
                              p.active,
                              t.name,
                              i.filename
                    FROM      projects as p
                    JOIN      projects_x_techs as pt
                    ON        p.id = pt.project_id
                    JOIN      techs as t
                    ON        pt.techs_id = t.id
                    JOIN      images as i
                    ON        p.id = i.projects_id
                    WHERE p.slug =  :slug

The ER-diagram ER diagram

From looking at that pastebin that is a multi dimensional array, and I can't seem to see any duplicates? — SamV, Nov 20 '13 at 13:49
@FruityP Take for example position 0,1 and 3 in the array, both contains the word "jQuery", because it's looping over the "filename" — Mads K, Nov 20 '13 at 13:51
But the image is different for each one, my misunderstanding then. — SamV, Nov 20 '13 at 14:29

score 11 · Accepted Answer · answered Nov 20 '13 at 13:50

11

You are asking MySQL do something that related to presentation of data. MySQL cannot show anything beside "rows". It can make anything you can describe as a row, but it cannot do rows within rows - it simply can't.

I suggest to return your "rows" and then have PHP pack them as you wish, rather than work hard on some very complex SQL query that might get you the output you desire, but would probably be: 1) Inefficient 2) Very hard to maintain one year from now

answered Nov 20 '13 at 13:50

Noam Rathaus

5,405
2
28
37

1

Point taken, so the solution would be some kind of PHP, that loops through it all, and pushes everything into an multidimensional array? – Mads K Nov 20 '13 at 13:53
Yes, and then have a (I am guessing based on the pastebin) JSON module generate the structure and return it. – Noam Rathaus Nov 20 '13 at 13:54
Yes, that was my plan! Can you suggest any JSON modules/libraries? – Mads K Nov 20 '13 at 13:58

Return MySQL query as multidimensional array

1 Answers1