can't figure this query out in mySQL

[sm43]

Hi,
I have created a temporary table (with a query that reads from 2 permanent tables) that lists items and its keywords. Each row defines relationship between one item and one keyword. e.g

Item Keyword

item1 keyword1
item2 keyword2
item3 keyword3
item1 keyword2
item3 keyword2


I want to display these items and keywords in an html table. Each unique item will be in one row of the html table, & ALL of it's keywords will be shown next to it stringed together with commas.

e.g.

item1 keyword1, keyword2

item2 keyword2

item3 keyword3, keyword2


I'm using mySQL 3.23 on UNIX and PHP for programming. I'm either overlooking something really simple, or this query is suppose to be indeed complicated. Or I'm finding it hard to do because of the absense of nested queries in mySQL. Nevertheless, I'm quite lost, and need somebody's pro eye to guide me. I've spent quite a few hours thinking about this query.

Thanks in-advance.


Saad

 

[Alkonaut]

I dont know exactly the php syntax, but I guess the easisest way is just making sure your table is sorted on
the item column first

item1 keyword1
item1 keyword2
item2 keyword2
item3 keyword3
item3 keyword2

Then when you create the html, as you step through the result set from this table, you make a new html table row (</tr><tr>) whenever the item value changes, new html columns (<td> tags) otherwise.

 

[JustKIDn]

I've done something like this before.

I'll look for it later. If you haven't resolved it yet I'll post here for you.

It's not in MySQL or even PHP. But it should be easy to convert. (if it needs it)

As I recall it used a join and alias's.
tgus

____________________________
Families can be together forever...

 

[atmoz]

It's not so difficult it looks like ;-)

first make connection to your mysql DB with PHP.

$dbrs = mysql_query(&quot;SELECT * FROM temporary ORDER BY Item&quot;
for($i=0;$i<mysql_num_rows($dbrs);$i++){
$rs = mysql_fetch_array($dbrs);
if($rs[&quot;Items&quot;]==$Item){
$rs[&quot;Items&quot;]=$Item;
echo $Item;}
echo $rs[&quot;keyword&quot;];}

hope your problem is solved,

Atmoz