Page 1 of 1

Returning number of records from a different query??

Posted: Mon Jul 14, 2003 4:55 am
by nihilist
Hello,
I was wondering if someone out there could help me with this issue.

I am trying to get the number of products in a seperate table which share the same Category that is in its own table. The category table has 2 fields, Category and CatID, while the products table has each product with a CatID.

What I want to do is display the Category, and then next to each category have it display the number of products in the category. I have tried it a few ways, and while I am not getting any errors, I am unable to get it to display the number. Please help :)

The code is intertwined in some html so I did my best to pull out what I felt was important from the page.

Code: Select all

<?
 include 'conf.php';

// open database connection
$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!");

// select database
mysql_select_db($db) or die ("Unable to select database!");

// generate and execute query
$query = "SELECT Category.CatID, products.CatID, Category.Category, products.name, products.price, products.desc FROM products, Category where products.CatID = Category.CatID and products.CatID = '$CatID'";
$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());
$num_results = mysql_num_rows($result);

if (mysql_num_rows($result) > 0)
     $row = mysql_fetch_object($result)

// is displayed in heading portion of page
echo $row->Category; 

    ?>
<?
//second part, just the list of categories
// generate and execute query
$query2 = "SELECT Category, CatID FROM Category";
$result2 = mysql_query($query2) or die ("Error in query: $query2. " . mysql_error());

if (mysql_num_rows($result2) > 0)
{
	// check for categories and put them in the list
	while($row2 = mysql_fetch_object($result2))
	{
	// this is where I want the number of results to be displayed next to each link

	?>
      <a href ="products.php?CatID=<? echo $row2->CatID; ?>"><? echo $row2->Category .$num_results.?></a><br>
           
          <?
	  }
	  ?>
          <?
	}
	?>
Thank you in advance for any help.

Re: Returning number of records from a different query??

Posted: Mon Jul 14, 2003 6:25 am
by Tubbietoeter

Code: Select all

<?
 include 'conf.php';

// open database connection
$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!");

// select database
mysql_select_db($db) or die ("Unable to select database!");

// generate and execute query
$query = "SELECT Category.CatID, products.CatID, Category.Category, products.name, products.price, products.desc FROM products, Category where products.CatID = Category.CatID and products.CatID = '$CatID'";
$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());
$num_results = mysql_num_rows($result);

if (mysql_num_rows($result) > 0)
     $row = mysql_fetch_object($result)

// is displayed in heading portion of page
echo $row->Category; 

    ?>
<?
//second part, just the list of categories
// generate and execute query
$query2 = "SELECT Category, CatID FROM Category";
$result2 = mysql_query($query2) or die ("Error in query: $query2. " . mysql_error());

if (mysql_num_rows($result2) > 0)
{
	// check for categories and put them in the list
	while($row2 = mysql_fetch_object($result2))
	{
	// this is where I want the number of results to be displayed next to each link
                // i dont know how to get values from mysql ...
                foreach ($category_id=mysql_...(...); ) {
                      $query3="select count(*) from products where              category_id=$category_id";
                      // then get the results again
                      $num_results={results from query3};
                      echo "<a href ="products.php?CatID=".$row2->CatID.">";
                      echo $row2->Category." = ".$num_results."</a><br>";
                 }
  }
}
?>

Posted: Thu Jul 17, 2003 6:09 pm
by nihilist
I tried following your example, and perhaps my query line is incorrect or something.

I put the foreach in for the loop, and that portion is working fine, however it outputs like this

Category1 (a number)
Category2 (Same number)
so forth


even if category 1 and category 2 have different product amounts.

I need it to look at the Categories in the category table, then look at the products in the products table, and give me a number of products containing each category ID and then, putting the appropriate number next to the Category link.

Because its returning the same number of products in each category, I am just certain its a matter of the query I am using now.

Any ideas??


Code: Select all

<?php $query2 = "SELECT Category, CatID FROM Category";
$result2 = mysql_query($query2) or die ("Error in query: $query2. " . mysql_error());
$row2 = mysql_fetch_object($result2);
$query4 = "SELECT products.CatID, Category.CatID from products, Category where products.CatID = Category.CatID";
$result4 = mysql_query($query4) or die ("Error in query: $query4. " . mysql_error());
$row4 = mysql_fetch_object($result4);
if (mysql_num_rows($result2) > 0)
{
while($row2 = mysql_fetch_object($result2))
	{

	$query3="select count(*) from products,Category where Category.CatID = products.CatID"; 
					  
$result3 = mysql_query($query3) or die ("Error in query: $query3. " . mysql_error());
                      $num_results3=mysql_num_rows($result3);
                      foreach (mysql_fetch_object($result4)as $row){
				
					 
					  echo "<a href ="products.php?CatID=$row2->CatID">"; 

                      echo $row2->Category." ( ".$num_results3." )</a><br>";  
					  }
					
                	  
					  
		}
}


?>
Thanks

Posted: Fri Jul 18, 2003 5:50 pm
by nihilist
Well after pulling my hair out, I decided I was probably making it more complicated then it was.

I sat down and figured it out, and I thought I would just post my solution in case anyone else has a similar problem.

Code: Select all

<?
// includes
include 'conf.php';

// open database connection
$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!");

// select database
mysql_select_db($db) or die ("Unable to select database!");
// generate and execute query to get category
$catquery = "SELECT * FROM Category";
$catresult= mysql_query($catquery) or die ("Error in query: $catquery. " . mysql_error());
while($catrow = mysql_fetch_object($catresult))
{

	// loop the following while there is something in the query above
	$productquery= "SELECT PartsID, CatID from products where CatID = '$catrow->CatID'";
	$productresult= mysql_query($productquery) or die ("Error in query:  $productquery. " . mysql_error());
	$num_results=mysql_num_rows($productresult);
	echo "<a href ="products.php?CatID=$catrow->CatID">";
	echo $catrow->Category." ( ".$num_results." )</a><br>";
}

?>