Need help fixing up code, it's pulling duplicate data, only need one instance.

[Colleen78]

I have this code to display the subcategories in a Category, but it's showing the subcategories over and over like so.
    * Bears (0)
    * Bird (0)
    * Cats (0)
    * Dogs (1)
    * Horse (0)

    * Bears (0)
    * Bird (0)
    * Cats (0)
    * Dogs (1)
    * Horse (0)

    * Bears (0)
    * Bird (0)
    * Cats (0)
    * Dogs (1)
    * Horse (0)

    * Bears (0)
    * Bird (0)
    * Cats (0)
    * Dogs (1)
    * Horse (0)

    * Bears (0)
    * Bird (0)
    * Cats (0)
    * Dogs (1)
    * Horse (0)

I am new to this so I need a little guidance how to limit it to only display each subcategory once.

Here's the code.

[code=php:0]//create subcategories
$qc = "select * from dd_subcategories order by SubcategoryName";
$rc = mysql_query($qc) or die(mysql_error());

while($ac = mysql_fetch_array($rc))

{
$subcategories .= "<ul style=\"margin-top:0; margin-bottom:0; margin-left:15\">";
//get the subcategories
$qsc = "select * from dd_subcategories where CategoryID = '$_GET[CategoryID]' order by SubcategoryName ";
$rsc = mysql_query($qsc) or die(mysql_error());

if(mysql_num_rows($rsc) > '0')
{
while($asc = mysql_fetch_array($rsc))
{
//get the items number at this subcategory
$qin2 = "select count(*) from dd_items where ItemCategory = '$_GET[CategoryID]' and ItemSubcategory = '$asc[SubcategoryID]' and ItemStatus = 'approved' ";
$rin2 = mysql_query($qin2) or die(mysql_error());
$ain2 = mysql_fetch_array($rin2);

$subcategories .= "<li><a href=\"ShowCategory.php?CategoryID=$ac[CategoryID]&SubcategoryID=$asc[SubcategoryID]\">$asc[SubcategoryName] ($ain2[0])</a></li>";
}
}
$subcategories .= "</ul>";
}
[/code]

Thanks,
Colleen

[paul2463]

you have one too many while loops in your code
whats this part of the code give you that the origonal query did not?
[code]
$qsc = "select * from dd_subcategories where CategoryID = '$_GET[CategoryID]' order by SubcategoryName ";
$rsc = mysql_query($qsc) or die(mysql_error());
if(mysql_num_rows($rsc) > '0')
{
            while($asc = mysql_fetch_array($rsc))[/code]

[Colleen78]

After removing that code the subcats now list like this:
    *  (10)
    * (10)
    * (10)
    * (10)
    * (10)
    * (10)
    * (10)
    * (10)

Ok, I have this and it now works:
[code=php:0]//create subcategories
$subcategories .= "<ul style=\"margin-top:0; margin-bottom:0; margin-left:15\">";
//get the subcategories
$qsc = "select * from dd_subcategories where CategoryID = '$_GET[CategoryID]' order by SubcategoryName ";
$rsc = mysql_query($qsc) or die(mysql_error());
while($asc = mysql_fetch_array($rsc))
{
//get the items number at this subcategory
$qin2 = "select count(*) from dd_items where ItemCategory = '$_GET[CategoryID]' and ItemSubcategory = '$asc[SubcategoryID]' and ItemStatus = 'approved' ";
$rin2 = mysql_query($qin2) or die(mysql_error());
$ain2 = mysql_fetch_array($rin2);

$subcategories .= "<li><a href=\"ShowCategory.php?CategoryID=$ac[CategoryID]&SubcategoryID=$asc[SubcategoryID]\">$asc[SubcategoryName] ($ain2[0])</a></li>";
}
$subcategories .= "</ul>";[/code]

Thanks paul2463

[ober]

Try this:

[code]<?php//create subcategories
$qc = "select * from dd_subcategories order by SubcategoryName";
$rc = mysql_query($qc) or die(mysql_error());
$subcategories .= "<ul style=\"margin-top:0; margin-bottom:0; margin-left:15\">";
while($ac = mysql_fetch_array($rc))

{

//get the subcategories
$qsc = "select * from dd_subcategories where CategoryID = '$_GET[CategoryID]' order by SubcategoryName ";
$rsc = mysql_query($qsc) or die(mysql_error());

if(mysql_num_rows($rsc) > '0')
{
while($asc = mysql_fetch_array($rsc))
{
//get the items number at this subcategory
$qin2 = "select count(*) from dd_items where ItemCategory = '$_GET[CategoryID]' and ItemSubcategory = '$asc[SubcategoryID]' and ItemStatus = 'approved' ";
$rin2 = mysql_query($qin2) or die(mysql_error());
$ain2 = mysql_fetch_array($rin2);

$subcategories .= "<li><a href=\"ShowCategory.php?CategoryID=$ac[CategoryID]&SubcategoryID=$asc[SubcategoryID]\">$asc[SubcategoryName] ($ain2[0])</a></li>";
}
}

}
$subcategories .= "</ul>";
echo $subcategories;
?>
[/code]

I think your problem was that you were creating the <ul> each time you entered the loop.

[roopurt18]

You can also use the [b]DISTINCT[/b] modifier in your SQL.

[code]
// Select only unique rows from the table
$sql = "SELECT DISTINCT * FROM SomeTable WHERE 1";

// Select only rows with unique row1, row2, ..., rowN combinations
$sql = "SELECT DISTINCT row1, row2, ..., rowN FROM SomeTable WHERE 1";
[/code]

[url=http://dev.mysql.com/doc/refman/4.1/en/distinct-optimization.html]http://dev.mysql.com/doc/refman/4.1/en/distinct-optimization.html[/url]

[Colleen78]

Hey ober and roopurt18, thanks, I got it fixed, I posted above. I had too many while loops. The ul is only being created once so all is welll.

I do have another question though.

On the homepage I list the categories and want 3 subcategories to show under each category like this:

[b]Category[/b]
[size=8pt]subcat, subcat, subcat...[/size]

I don't know how, and here's the code. I'd really appreciate any ideas on this.

[code=php:0]//create categories
$qc = "select * from dd_categories order by CategoryName";
$rc = mysql_query($qc) or die(mysql_error());

if(mysql_num_rows($rc) > '0')
{
while($ac = mysql_fetch_array($rc))
{
//get the items number at this category
$qin = "select count(*) from dd_items where ItemCategory = '$ac[CategoryID]' and ItemStatus = 'approved' ";
$rin = mysql_query($qin) or die(mysql_error());
$ain = mysql_fetch_array($rin);

$categories .= "<img src=\"images/arrow.gif\" alt=\">\" /><a class=\"title\" href=\"ShowCategory.php?CategoryID=$ac[CategoryID]\">$ac[CategoryName] ($ain[0])</a><br />\n\t\t\t";

if($_GET[CategoryID] == $ac[CategoryID])
{
$categories .= "<ul style=\"margin-top:0; margin-bottom:0; margin-left:15\">";
//get the subcategories
$qsc = "select * from dd_subcategories where CategoryID = '$_GET[CategoryID]' order by SubcategoryName ";
$rsc = mysql_query($qsc) or die(mysql_error());

if(mysql_num_rows($rsc) > '0')
{
while($asc = mysql_fetch_array($rsc))
{
//get the items number at this subcategory
$qin2 = "select count(*) from dd_items where ItemCategory = '$_GET[CategoryID]' and ItemSubcategory = '$asc[SubcategoryID]' and ItemStatus = 'approved' ";
$rin2 = mysql_query($qin2) or die(mysql_error());
$ain2 = mysql_fetch_array($rin2);

$categories .= "<li><a href=\"ShowCategory.php?CategoryID=$ac[CategoryID]&SubcategoryID=$asc[SubcategoryID]\">$asc[SubcategoryName] ($ain2[0])</a></li>";
}
}
$categories .= "</ul>";
}

}
}[/code]

[ober]

I think what you're looking for is the LIMIT clause.

[Colleen78]

It's fixed though. :) Not duplicating anymore.

Any help on the other thing? :)

[roopurt18]

I thought when using variable values inside of double quotes you had to enclose them in curly braces.

I.E. instead of
[code]
$qin = "select count(*) from dd_items where ItemCategory = '$ac[CategoryID]' and ItemStatus = 'approved' ";
[/code]

use
[code]
$qin = "select count(*) from dd_items where ItemCategory = '{$ac['CategoryID']}' and ItemStatus = 'approved' ";
[/code]

Or did I miss understand something?

[Colleen78]

I have no idea, my code is from a joke script I purchased years ago, I'm just cleaning it up and adding some more functionality.

[ober]

No... I think you misunderstood me.  If I understood you correct, you have the listing already, you just want to limit it to 3 subcategories, right?

You can either use the LIMIT clause or a counter variable.

[Colleen78]

No, what I want to do now is unrelated to the above. That was for a subpage.

I posted seperate code for the index page, which currently is only set to show Parent Categories. I now want on the index page, 3 subcats under each category.

Right now I have no subcats on the home page. I posted that code above.

Thanks.

[roopurt18]

Psuedo code:
[code]
$Cats = GetCategories();
$Html = "<ul>"
foreach($Cats as $Cat){ // while loop in your case
  $CatDets = GetCategoryDetails($Cat);
  $Html .= "<li>" . PrintCatHeader($Cat, $CatDets);
  $SubCats = GetSubCats($Cat);
  if($SubCats){
    $Html .= "<ul>";
    foreach($SubCats as $SubCat){
      $SubCatDets = GetSubCategoryDetails($SubCat);
      $Html .= "<li>" . PrintSubCatHeader($SubCat, $SubCatDets) . "</li>";
    }
    $Html .= "</ul>";
  }
  $Html .= "</li>";
}
$Html .= "</ul>";
echo $Html;
[/code]

Just make each <li> of the categories also contain a <ul> of the subcategories; this assumes there is nothing wrong with embedding unordered lists in HTML, which AFAIK is OK.

[Colleen78]

Thanks Roopurt, will it put the three subcats like so? one, two, three...

So comma, comma, dotdotdot? Like my directory... directorymonsters.com because I am planning to remove the list, I won't need it on the homepage as I want them inline anyway.

[roopurt18]

No, the psuedo code I gave will do:
[list]
[*]Cat 1[list]
[*]Sub 1
[*]Sub 2
[/list]
[*]Cat 2[list]
[*]Sub 1
[*]Sub 2
[/list]
[/list]

If you want
[list]
[*]Cat 1 - Sub 1, Sub 2, Sub 3
[*]Cat 2 - Sub 1, Sub 2, Sub 3
[/list]

Then:
[code]
$Cats = GetCategories();
$Html = "<ul>"
foreach($Cats as $Cat){ // while loop in your case
  $CatDets = GetCategoryDetails($Cat);
  $Html .= "<li>" . PrintCatHeader($Cat, $CatDets);
  $SubCats = GetSubCats($Cat);
  if($SubCats){
    $SubCatsArr = Array();
    foreach($SubCats as $SubCat){
      $SubCatDets = GetSubCategoryDetails($SubCat);
      $SubCatsArr[] = PrintSubCatHeader($SubCat, $SubCatDets);
    }
    if(count($SubCatsArr)){
      $Html .= ' - ' . implode(', ', $SubCatsArr);
    }
  }
}
$Html .= "</ul>";
echo $Html;
[/code]

[Colleen78]

Thanks. I'm not sure how to integrate this. I am a total beginner to PHP, I only edit existing code.

I am not sure how to add it into the code I post above, what to remove/keep. I will play around with it.