Jump to content


Photo

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


  • Please log in to reply
15 replies to this topic

#1 Colleen78

Colleen78
  • Members
  • PipPipPip
  • Advanced Member
  • 66 posts
  • LocationCanada

Posted 05 September 2006 - 05:26 PM

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.

//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>";
		}

Thanks,
Colleen

#2 paul2463

paul2463
  • Members
  • PipPipPip
  • Advanced Member
  • 955 posts
  • LocationUK

Posted 05 September 2006 - 05:51 PM

you have one too many while loops in your code
whats this part of the code give you that the origonal query did not?
$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))

you cannot affect the past but you can ruin a perfectly good present by worrying about the future

*********************************************************************
The <A HREF="http://www.php.net/d...php">MANUAL</A> is actually a useful resource

#3 Colleen78

Colleen78
  • Members
  • PipPipPip
  • Advanced Member
  • 66 posts
  • LocationCanada

Posted 05 September 2006 - 05:54 PM

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:
//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>";

Thanks paul2463

#4 ober

ober
  • Staff Alumni
  • Advanced Member
  • 5,337 posts
  • LocationEast Coast, USA

Posted 05 September 2006 - 06:05 PM

Try this:

<?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;
?>

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

Info: PHP Manual


#5 roopurt18

roopurt18
  • Staff Alumni
  • Advanced Member
  • 3,749 posts
  • LocationCalifornia, southern

Posted 05 September 2006 - 06:15 PM

You can also use the DISTINCT modifier in your SQL.

// 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";

http://dev.mysql.com...timization.html
PHP Forms : Part I | Part II

JavaScript: Singleton

http://www.rbredlau.com

#6 Colleen78

Colleen78
  • Members
  • PipPipPip
  • Advanced Member
  • 66 posts
  • LocationCanada

Posted 05 September 2006 - 06:20 PM

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:

Category
subcat, subcat, subcat...

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

//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>";
		}

	}
}


#7 ober

ober
  • Staff Alumni
  • Advanced Member
  • 5,337 posts
  • LocationEast Coast, USA

Posted 05 September 2006 - 06:22 PM

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

Info: PHP Manual


#8 Colleen78

Colleen78
  • Members
  • PipPipPip
  • Advanced Member
  • 66 posts
  • LocationCanada

Posted 05 September 2006 - 06:31 PM

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

Any help on the other thing? :)

#9 roopurt18

roopurt18
  • Staff Alumni
  • Advanced Member
  • 3,749 posts
  • LocationCalifornia, southern

Posted 05 September 2006 - 06:49 PM

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

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

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

Or did I miss understand something?
PHP Forms : Part I | Part II

JavaScript: Singleton

http://www.rbredlau.com

#10 Colleen78

Colleen78
  • Members
  • PipPipPip
  • Advanced Member
  • 66 posts
  • LocationCanada

Posted 05 September 2006 - 06:56 PM

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.

#11 ober

ober
  • Staff Alumni
  • Advanced Member
  • 5,337 posts
  • LocationEast Coast, USA

Posted 05 September 2006 - 06:58 PM

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.

Info: PHP Manual


#12 Colleen78

Colleen78
  • Members
  • PipPipPip
  • Advanced Member
  • 66 posts
  • LocationCanada

Posted 05 September 2006 - 07:07 PM

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.

#13 roopurt18

roopurt18
  • Staff Alumni
  • Advanced Member
  • 3,749 posts
  • LocationCalifornia, southern

Posted 05 September 2006 - 07:26 PM

Psuedo 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;

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.

PHP Forms : Part I | Part II

JavaScript: Singleton

http://www.rbredlau.com

#14 Colleen78

Colleen78
  • Members
  • PipPipPip
  • Advanced Member
  • 66 posts
  • LocationCanada

Posted 05 September 2006 - 07:32 PM

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.

#15 roopurt18

roopurt18
  • Staff Alumni
  • Advanced Member
  • 3,749 posts
  • LocationCalifornia, southern

Posted 05 September 2006 - 08:06 PM

No, the psuedo code I gave will do:
  • Cat 1
    • Sub 1
    • Sub 2
  • Cat 2
    • Sub 1
    • Sub 2

If you want
  • Cat 1 - Sub 1, Sub 2, Sub 3
  • Cat 2 - Sub 1, Sub 2, Sub 3

Then:
$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;

PHP Forms : Part I | Part II

JavaScript: Singleton

http://www.rbredlau.com

#16 Colleen78

Colleen78
  • Members
  • PipPipPip
  • Advanced Member
  • 66 posts
  • LocationCanada

Posted 06 September 2006 - 12:47 AM

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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users