I have only worked with one-to-one associations in php to date, but I am stuck on the problem that involves a 1-to-many relationship. I have been looking at this for any couple of days without any luck, so I am eager for anyone to part of and show us a solution before I lose my thoughts.

During my database have a number of web addresses, that are received with a Choose query together with many other fields, from different tables. Every url has a minumum of one category connected by using it, but could have multiple groups. So during my results I would see something which looks a little such as 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've got this to operate, type of. After I loop through and print them off, utilizing a while loop and mysql fetch array, the end result looks just like it does above. Thats great, except things i require is for this to see something similar to:

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

To ensure that essentially all the groups for every url get combined in some way, because they are printed out. My first attempt brought me to test a nested while loop, however it did not work and i am unsure if this sounds like achievable. Aside from that I am wondering basically may need a multidimensional array (complete guess, i have never needed to play one before).

I am ordering these results by link id as above, and so i determine if the hyperlink id in the present loop iteration, matches the main one within the last iteration - i quickly have a thing that has several category.. I believe I am really close, however i cannot decipher it.

Any ideas?

There's even the "GROUP_CONCAT" function in mysql. Which should just do what you would like to attain.

Something similar to :

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

You ought to be utilizing a connection table.

first you've got a table of links

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

Then you've a table of groups

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

Then you've an association table

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

This will atleast enable you to get began.

make use of an array keyed around the id and url iterate with the values and increase it the following:

$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']] = " ";
        $link_categories[$row['link']] .= " ";

    $link_categories[$row['link']] .= $row['category'];


Leads to:

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

This is not the 'right' method of carrying this out - truly the associations ought to be defined inside a seperate table having a 1-many relationship.

you should utilize a control break formula.

set last_link variable to null
set combined_category to null
exec query

loop over result set {
    if last_link == null {
    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

display html for last_link and combined_category

I made use of "display html" like a generic "work" event, you can push this to a array structure, etc. rather...