SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Apr 2005
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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?
    Last edited by nantucketer; May 6, 2005 at 08:26.

  2. #2
    SitePoint Member
    Join Date
    Apr 2005
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    SitePoint Wizard
    Join Date
    Aug 2004
    Location
    California
    Posts
    1,672
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Christopher

  4. #4
    SitePoint Member
    Join Date
    Apr 2005
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    SitePoint Member
    Join Date
    Apr 2005
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  6. #6
    SitePoint Member
    Join Date
    Apr 2005
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •