Logo Questions Linux Laravel Mysql Ubuntu Git Menu

Return MySQL query as multidimensional array

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,
                    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

like image 784
Mads K Avatar asked Dec 20 '22 21:12

Mads K

1 Answers

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

like image 77
Noam Rathaus Avatar answered Dec 24 '22 02:12

Noam Rathaus