Jump to content

Archived

This topic is now archived and is closed to further replies.

robpoe

sql issue

Recommended Posts

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

Share this post


Link to post
Share on other sites
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));}

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.