sql issue

[robpoe]

Going to try my best to explain this without being muddy.

Basically I have two tables. On one table, is a list of items, and another table there is a list of subsets for those items. They're tied together with a primary key. To put this into an example, we'll use a shopping cart analogy (though in this case this is not what it is!).

Table1:
Item Number Item
101 Balloon
102 Slippers



Table2:

Item Number Item Description
101 Red
101 Blue
101 Pink
102 Crystal
102 Leather



I didn't design the table, the database, or how this works. I'm just trying to put together a web view.

What my intended output is is this:

Item Number Item Name Types
101 Balloon Red, Blue, Pink
102 Slippers Crystal, Leather

My current query looks a little like this:

Select Item.ItemNumber, Item.ItemName, Description.ItemType from Item INNER JOIN Description ON Item.Itemnumber = Description.ItemNumber;

What I'm getting for output is this:

Item Number Item Name Types
101 Balloon Red
101 Balloon Blue
101 Balloon Pink
102 Slippers Crystal
102 Slippers Leather

Does anyone have any great ideas on what I can do to accomplish what I was trying. Using UNIX_ODBC with FreeTDS to connect. I tried doing a second connect (including making a $connect2, $query2, $result2, but then I get SQL Cursor errors). I thought maybe doing the second connect, I'd skip the inner join, and just subloop, and for each item number, do a select itemtype from description where itemnumber = $itemnumber. No such joy there.

I can't seem to post my code, I get a permission error on index.php, so I posted my full post, plus the code at [a href=\"http://mlky.com/forumspost.txt\" target=\"_blank\"]http://mlky.com/forumspost.txt[/a]

I'm not an advanced PHP guy, learning still, but trying :)

Thanks in advance!

Rob

[jvrothjr]

here is a basic Idea

$FirstQuery = "Select ItemNumber, ItemName from Item";
$FirstTableQuery = mysql_query($FirstQuery);
if ($FirstTableRow = mysql_fetch_array($FirstTableQuery)) {
do{
$cnt = 0;
$displayvariable = "";
$displayvariable = $FirstTableRow['ItemNumber']." ".$FirstTableRow['ItemName']." ";
$SearchItem = $FirstTableRow['Item'];
$SecondQuery = "Select ItemType from Description where ItemNumber = '$SearchItem'"
$SecondTableQuery = mysql_query($SecondQuery);
if ($SecondTableRow = mysql_fetch_array($SecondTableQuery)) {
do{
$cnt = $cnt + 1;
if ($cnt == "1") {
$displayvariable = $displatvariable.$SecondTableRow['ItemType'];
} else {
$displayvariable = $displatvariable.",".$SecondTableRow['ItemType'];
}
} while($SearchTableRow = mysql_fetch_array($SecondTableQuery));}
// Code for Output Here
} while($FirstTableRow = mysql_fetch_array($FirstTableQuery));}