PHP and outputting one-to-many results

I've only dealt with one-to-one relationships in php so far, but I'm stuck on a problem which involves a one-to-many relationship. I've been sitting on this for a few days with no luck, so I'm desperate for someone to step in and show me a solution before I lose my mind.

In my database have a series of urls, which are received by a SELECT query along with various other fields, from different tables. Every url has at least one category associated with it, but can have multiple categories. So in my results I might see something that looks a bit like this:

link_id = 3   url= 'http://www.somesite1.com'   category = 'uncategorised'
link_id = 4   url= 'http://www.somesite2.com'   category = 'travel'
link_id = 4   url= 'http://www.somesite2.com'   category = 'fun'
link_id = 4   url= 'http://www.somesite2.com'   category = 'misc'
link_id = 3   url= 'http://www.somesite3.com'   category = 'uncategorised'

I have got this to work, kind of. When I loop through and print them off, using a while loop and mysql fetch array, the result looks exactly like it does above. Thats great, except what I need is for it to read something like:

link_id = 4   url = 'http://www.somesite2.com'   category = 'travel fun misc'

So that basically all of the categories for each url get combined somehow, as they are printed out. My first attempt led me to try a nested while loop, but it didn't work and i'm not sure if this is feasible. Apart from that I'm wondering if I might need a multidimensional array (complete guess, i've never had to use one before).

I'm ordering these results by link id as above, so I know if the link id in the current loop iteration, matches the one in the last iteration - then I have something which has more than one category.. I think I'm really close, but I just can't figure it out.

Any ideas?

From stackoverflow

• You should be using a connection table.

  1st you have a table of links

  id = 1 url = something
  id = 2 url = something else
  

  Then you have a table of categories

  id = 1 category = something
  id = 2 category = something else
  

  Then you have a connection table

  url_id = 1 category_id = 1
  url_id = 1 category_id = 2
  url_id = 2 category_id = 1
  

  This should atleast get you started.

  Jon : ahh sorry, I should have made it a bit more clear. If I understand correctly, I do have a set up like this. A table called categories, just being a repository of all the categories that exist, then a table that just contains urls, then a table with url_id's and cat_id's to join them together.

  Ólafur Waage : No problem :) I'll keep the answer until you update the question if you want to do that.

• use an array keyed on the id and url iterate through the values and add to it as follows:

  $link_categories[ $id ] .= $category." ";
  
  $result = mysql_query("SElECT * FROM LINKS");
  
  $link_categories = array();
  
  while ($row = mysql_fetch_array($result,MYSQL_ASSOC))
  {
      if (!isset($link_categories[$row['link']]))
          $link_categories[$row['link']] = " ";
      else
          $link_categories[$row['link']] .= " ";
  
      $link_categories[$row['link']] .= $row['category'];
  }
  
  print_r($link_categories);
  

  Results in:

  Array
  (
      [http://a.com] =>  test evaluate performance
      [http://b.com] =>  classify reduce
      [http://c.com] =>  allocate
  )
  

  This isn't the 'right' way of doing this - really the relationships should be defined in a seperate table with a 1-many relationship.

• you need to use a control break algorithm.

  set last_link variable to null
  set combined_category to null
  exec query
  
  loop over result set {
      if last_link == null {
          last_link=fetch_link
      }
      if fetch_link==last_link {
          set combined_category+=ltrim(' '.fetch_category)
      } else {
          display html for last_link and combined_category
          set last_link=fetch_link
          set combined_category=fetch_category
      }
  }//loop
  
  display html for last_link and combined_category
  

  I used "display html" as a generic "work" event, you could push this out to a array structure, etc. instead...

• There is also the "GROUP_CONCAT" function in mysql. That should do exactly what you want to achieve.

  Something like :

  SELECT url, GROUP_CONCAT(category) AS categories FROM yourtable GROUP BY url
  

  Jon : This worked perfectly when I ran the query, and again when I plugged it into my php script. I wasn't aware of that function, but it was exactly what I was after. Thanks!