Jump to content

Archived

This topic is now archived and is closed to further replies.

Colleen78

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

Recommended Posts

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

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
I think what you're looking for is the LIMIT clause.

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.