SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    <? echo "Kick me"; ?> petesmc's Avatar
    Join Date
    Nov 2000
    Location
    Hong Kong
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here is the code:
    ---------
    <HTML>
    <HEAD>
    <TITLE></TITLE>
    <META name="description" content="">
    <META name="keywords" content="">
    <META name="generator" content="CuteHTML">
    </HEAD>
    <BODY BGCOLOR="#FFFFFF" TEXT="#000000" LINK="#0000FF" VLINK="#800080">

    <?php

    include("/home2/ianc/phplib/articles.inc");
    mysql_select_db('ianc_articles');


    $article = mysql_query("SELECT C.ID as CatID, C.Name as CatName, S.ID as SubID, S.Name as SubName, " .
    "S.CID as SubCID FROM categories as C, subcategories as S WHERE S.CID=$id");
    if (!$article) {
    echo("<P>Error retrieving authors from database!<BR>".
    "Error: " . mysql_error());
    exit();
    }

    $title = mysql_fetch_array($article);
    $titlename = $title["CatName"];
    echo("<font size='5'>$titlename</font><br><br>");

    while ($select = mysql_fetch_array($article)) {
    $subid = $select["SubID"];
    $subname = $select["SubName"];
    echo("<a href='subcat.php?id=$subid'>$subname</a><br>");
    }

    ?>

    </BODY>
    </HTML>
    ----------

    basically, as you can see at: http://www.pcreview.co.uk/category.php?id=2

    It does really funny loops and i have no idea why. Can anyone help me out on this?

  2. #2
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by petesmc
    "SELECT C.ID as CatID, C.Name as CatName, S.ID as SubID, S.Name as SubName, " .
    "S.CID as SubCID FROM categories as C, subcategories as S WHERE S.CID=$id
    Your problem is not in the loop but in your SQL query. The part which states

    "FROM categories as C, subcategories as S"

    creates a join of the two tables, which is the product of the two tables. What this produces follows this logic (lets assume that there is 2 rows in table category and 3 in table subcategory):

    category X subcategory

    category.row1 + subcategory.row1
    category.row1 + subcategory.row2
    category.row1 + subcategory.row3
    category.row2 + subcategory.row1
    category.row2 + subcategory.row2
    category.row2 + subcategory.row3

    So your WHERE clause "WHERE S.CID=$id"

    if applied to my example above will match two rows of the joined table:

    category.row1 + subcategory.row1
    category.row2 + subcategory.row1

    That's why you are getting all those extra results that you don't want.

    What you need is a WHERE clause that says this "WHERE CatID=$id AND S.CID=$id".

    However, with all that said and done, I'm not sure what it is you are trying to achieve. Looking further into your code, you are not using any of the data from table category, so you could just as easily create the same results as what I suggest above by using:

    SELECT S.ID as SubID, S.Name as SubName, S.CID as SubCID FROM subcategories as S WHERE S.CID=$id"


  3. #3
    <? echo "Kick me"; ?> petesmc's Avatar
    Join Date
    Nov 2000
    Location
    Hong Kong
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanx allot, that solved the problem...

    What you need is a WHERE clause that says this "WHERE CatID=$id AND S.CID=$id".


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
  •