Jump to content


sql issue

  • Please log in to reply
1 reply to this topic

#1 robpoe

  • New Members
  • Pip
  • Newbie
  • 1 posts

Posted 22 June 2006 - 04:33 PM

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!).

Item Number Item
101 Balloon
102 Slippers


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!


#2 jvrothjr

  • Members
  • PipPipPip
  • Advanced Member
  • 300 posts
  • LocationOhio

Posted 22 June 2006 - 07:32 PM

here is a basic Idea

$FirstQuery = "Select ItemNumber, ItemName from Item";
$FirstTableQuery = mysql_query($FirstQuery);
if ($FirstTableRow = mysql_fetch_array($FirstTableQuery)) {
$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)) {
$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));}
If you understand everything you know nothing!


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users