PHP and one-to-many output

Question

I have only dealt with one-to-one relationships in php so far, but I am stuck in a problem that is related to the one-to-many relationship. I sat on this for several days with no luck, so I desperately need someone to step in and show me the solution before I go crazy.

My database has a number of URLs that are retrieved using a SELECT query along with various other fields from different tables. Each url has at least one category associated with it, but can have several categories. Therefore, in my results, I could see something similar to 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 this to work. When I scroll and print them using a while loop and mysql fetch, the result looks exactly the same as above. This is great, except that I need it to read something like:

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

So, so that all categories for each URL are somehow combined as they print. My first attempt made me try the nested while loop, but that didn't work, and I'm not sure how possible this is. Also, I am wondering if I might need a multidimensional array (a complete hunch, I never had to use it before).

, , , , - -, . , .

?

Answer 1

mysql "GROUP_CONCAT". , .

- :

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

Answer 2

.

1

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

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

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

.

Answer 3

id url :

$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);

:

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

This is not the “right” way to do this - indeed, relationships should be defined in a separate table with a 1-to-many relationship.

Answer 4

you need to use the control interrupt 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 general "working" event, you could output it to an array structure, etc. instead of this...