PHP and outputting one-to-many results

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

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