SQL Question ... should I use a Group By?

Question

I have a situation where I need to group data by a portfolio name, then sort through the data and display check boxes. Here's the table structure.

Table A
--------
portfolio_id
portfolio_name

Table B
-------
file_id
portfolio_id (Foreign Key fk_port_id references portfolio_id in table A)
file_name

Basically Table A links to table B in a "one-to-many" relationship.

Here's what i want to be able to do and years ago I was able to accomplish it with an informix database, but I don't remember how to do it or even if its possible with MySQL. I'm using php and mysql. I want to use SQL to group the filenames (table b) under their respective portfolio (table A).

So, for my output I want it to look like:

 **Portfolio 1**
      Jack's File
      Robyn's File
 **Portfolio 2**
      Joey's file
 **Portfolio 3**
      John's File

Is there some SQL I can use that would group the files underneath the portfolio, then allow me to use PHP to loop through the "GROUPS" and display the data?

I tried using two different loops one for the portfolio, then inside that using another loop to get all the files by doing an "if" statement to compare if it belonged to the portfolio, but I would prefer to find out if I can get it out of MySQL first.

When I used Informix, I did something like a Group By in the sql, then in the shell script I used a foreach "Group By Header" to get the name of the "Group", then used something like "Group By Data" to display contents of the data that belonged to the Header....obviously my informix syntax isn't correct, ...it was a great feature...can I accomplish this with MySQL, or something like it?

Thanks.

Answer 1

There is no vanilla SQL construct that will do this for you naturally. My advice would be to pull out the rows in a standard statement:

Portfolio 1, Jack's file
Portfolio 1, Robyn's File
Portfolio 2, Joey's file
...

and then implement the break condition you want in code. In PHP this would look something like:

...
$prevPortfolio = '';
while ($row = mysql_fetch_assoc($query)) {
  $portfolio = $row['portfolio'];
  $file = $row['file'];
  if (prevPortfolio != $portfolio) {
    echo "**$portfolio**\n";
  }
  echo "  $file\n";
  $prevPortfolio = $portfolio;
}