?for each, group by, while?? Not sure what to use.

[nantucketer]

I have made some emendations in hopes of clarifying what I am doing - please see post of May 6 below. Thanks
-----------------------------------

I am relatively new to PHP and am having trouble figuring out how to list items from 2 databases where db2 is joined to db1 by a category_id.

This is what I would like to have happen:

Category 1
Item 1
Item 2

Category 2
Item 3
Item 4

This is what I am getting:

Category 1
Item 1

Category 2
Item 1

Category 1
Item 2

Category 2
Item 2

Category 1
Item 3 etc.

Here is my code:
---------------------------------------

$catquery = mysql_query("SELECT category_id FROM dir_cat WHERE category_name = '$TITLE'");

while ($row = mysql_fetch_array($catquery))
{
extract($row);
$find = $category_id;
}

$subquery = mysql_query("SELECT category_id FROM dir_cat WHERE category_id_parent = $find");

WHILE ($row = mysql_fetch_array ($subquery))
{

extract($row);
$sub = $category_id;



$query = "SELECT * FROM dir_cat, dir_item WHERE dir_item.category_id = $sub AND dir_cat.category_id_parent = $find";

$result = mysql_query($query)
or die ("Sorry, there is a problem with the web site at this time.");
while ($row = mysql_fetch_array($result))


{
extract($row);
$category_name = "";
$item_name = "";

extract($row);
echo"<p><b>$category_name</b><br>$item_name";

}
}
--------------------------------------

I have tried FOR EACH & WHILE statements also GROUP BY with no success. Suggestions please?

[nantucketer]

I have made a little progress:

With the code below I am now getting the following

Category 1
Item 1
Item 2

Category 2
Item 1
Item 2

Category 1
Item 3
Item 4

Category 2
Item 3
Item 4

I think I need to do a for each or a manual iteration of query1 but how to have Items 1 and 2 only show under Category 1 and items 2 and 3 only show under category 2 is still eluding me.

Here is the new code:
------------------------------------
$catquery = mysql_query("SELECT category_id FROM dir_cat WHERE category_name = '$TITLE'");

while ($row = mysql_fetch_array($catquery))
{
extract($row);
$find = $category_id;
}

$subquery = mysql_query("SELECT category_id FROM dir_cat WHERE category_id_parent = $find");

WHILE ($row = mysql_fetch_array ($subquery))
{

extract($row);
$sub = $category_id;



$query = "SELECT * FROM dir_cat WHERE category_id_parent = $find";

$result = mysql_query($query)
or die ("Sorry, there is a problem with the web site at this time.");
while ($row = mysql_fetch_array($result))

{

$category_name = "";
extract($row);

echo"<p><b>$category_name</b><br>";


$query2 = "SELECT * FROM dir_item WHERE category_id = $sub";

$result2 = mysql_query($query2)
or die ("Sorry, there is a problem with the web site at this time.");
while ($row2 = mysql_fetch_array($result2))
{

$item_name = "";
extract($row2);

echo "<p><b>$item_name</b></p>";
}


}
}

mysql_close($connection);
?>
-------------------------------------

I would appreciate any help.

[arborint]

Very hard to tell what you are doing. Maybe something like:

SELECT category_id FROM dir_cat JOIN dir_item ON dlr_cat.category_id=dlr_item.category_id WHERE dlr_cat.category_id_parent = $find ORDER BY dlr_cat.category_name,dlr_item.item_name

Then as you loop through the results, check to see if the category changes and if it does, display the category heading.

[nantucketer]

OK, I've stripped it down to its bare bones:

This is what I want

Category1
Item1
item2

Category2
item3
item4

I am getting this:

Category1
Item1
Item2
Item3
Item4

Category2
Item1
Item2
Item3
Item4

Here's the code:
-----------------------------
$query = "SELECT * FROM test1";

$result = mysql_query($query)
or die ("Sorry, there is a problem with the web site at this time.");
while ($row = mysql_fetch_array($result))

{

$category_name = "";
extract($row);

echo"<p><b>$category_name</b><br>";

/*Find the items that go with the categories*/
$query2 = "SELECT * FROM test1, test2 WHERE test1.ID = test2.ID_parent";

$result2 = mysql_query($query2)
or die ("Sorry, there is a problem with the web site at this time.");
while ($row2 = mysql_fetch_array($result2))
{

$item_name = "";
extract($row2);

echo "<p><b>$Item_name</b></p>";

}
}

mysql_close($connection);
?>
---------------------------

Does this make it clearer?

Thanks so much.

[nantucketer]

And with a little more work I've gotten this (thanks to posting from Christopher above)

category1
item1
category1
item2
category2
item3
category2
item4

here's the code
---------------------
$query = "SELECT * FROM test1, test2 WHERE test1.ID = test2.ID_parent ORDER BY test1.category_name, test2.Item_name";

$result = mysql_query($query)
or die ("Sorry, there is a problem with the web site at this time.");
while ($row = mysql_fetch_array($result))

{

$category_name = "";$item_name = "";
extract($row);

echo"<p><b>$category_name</b><br><p><b>$Item_name</b></p>";

}

mysql_close($connection);
?>
------------------
Thanks

[nantucketer]

I am still working on this and here is a further clarification:

This is what I want to get:

category1 ID is 1


item1 ID parent is 1

item2 ID parent is 1

category2 ID is 2

item3 ID parent is 2

item4 ID parent is 2

Here is what I am currently getting:

category1 ID is 1


item1 ID parent is 1

item2 ID parent is 1

item3 ID parent is 2

item4 ID parent is 2

category2 ID is 2


item1 ID parent is 1

item2 ID parent is 1

item3 ID parent is 2

item4 ID parent is 2

Here is the Code:
----------------------
/*get the Category Names*/
$query = "SELECT * FROM test1";

$result = mysql_query($query)
or die ("Sorry, there is a problem with the web site at this time.");
while ($row = mysql_fetch_array($result))

{

$category_name = "";
$ID = "";
extract($row);

echo"<p><b>$category_name</b> ID is $ID<br>";


/*get the item names that go with the categories*/
$query2 = "SELECT * FROM test1, test2 WHERE test1.ID = test2.ID_parent";


$result2 = mysql_query($query2)
or die ("Sorry, there is a problem with the web site at this time.");
while ($row2 = mysql_fetch_array($result2))
{

$item_name = "";
$ID_parent = "";
extract($row2);

echo "<blockquote><p><b>$Item_name</b> ID parent is $ID_parent</p></blockquote>";
}


}
---------------------------------------
again my thanks for any help for this newbie