CODEWITHSUNDEEP
phpmysqlsqloutput
Astrid
Astrid

Reputation: 1312

PHP MYSQL query sort per category

i have the following query:

SELECT projects.project_name, files.file_name
FROM `projects` JOIN `files` ON (projects.project_id = files.project_id)
ORDER BY projects.project_name ASC, files.file_name ASC

which will for example output:

Project1 - file1
Project1 - file2
Project2 - file3
Project2 - file4

in PHP, i extract these values using:

while ($row = mysql_fetch_assoc($result)) {}

I somehow want an output like this:

Project1 - file1, file2
Project2 - file3, file4

How can i do this? Thanks.

Upvotes: 1

Views: 546

Answers (2)

Jazzepi
Jazzepi

Reputation: 5480

Please stop using any functions labeled mysql* mysql has been depreceated. PDO is MUCH better to work with and is being actively developed.

http://www.php.net/manual/en/mysqlinfo.api.choosing.php

Notice the big red warning banner on the mysql_fetch_assoc() function.

http://php.net/manual/en/function.mysql-fetch-assoc.php

Upvotes: 0

John Woo
John Woo

Reputation: 263723

You can do that directly in MySQL using GROUP_CONCAT()

SELECT  projects.project_name, 
        GROUP_CONCAT(files.file_name) file_name
FROM    projects
        INNER JOIN files 
            ON (projects.project_id = files.project_id)
GROUP   BY projects.project_name
ORDER   BY projects.project_name ASC, 
           files.file_name ASC

Upvotes: 1

Related Questions