Jump to content

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


Colleen78

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
Link to comment
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]
Link to comment
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
Link to comment
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.
Link to comment
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]
Link to comment
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]
Link to comment
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?
Link to comment
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.
Link to comment
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.
Link to comment
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]
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.