[RESOLVED] Count number of returned rows

[CRichardson]

I have developed the following to script to return products from the selected category. Products within that categories sub-categories are also shown.

I am struggling to work out the correct number of rows returned.

		//search for matching sub cats
		$query = $db->query("SELECT * FROM cart_blk_shop_categories");
		while ( $dx = $db->fetch_obj($query) ) {

	$position = $dx->position;
	$id = $dx->id;

		$categories = explode(">",$position);

		foreach ( $categories as $c ) {

			if($c == $catID) {

				$abovequery = $db->query("SELECT * FROM cart_blk_shop_products WHERE productLive = 1 AND productCATID = $id");

				while ( $dz = $db->fetch_obj($abovequery) ) {

					$prodName 		= $dz->productName;
					$prodNewName 	= addUnderscore($prodName);
					$prodPrice 		= $dz->productPrice;
					$prodPrice 		= number_format($prodPrice, 2, ".", ",");
					$prodID 		= $dz->productID;

						$thumbquery 	= $db->query("select * from cart_blk_shop_product_images where photoProductID='$prodID'");
						$objects 		= $db->fetch_obj($thumbquery);
						$prodThumb 		= $objects->photoThumb;

					eval ("\$productBrief = \"".gettemplate("product_brief")."\";");
					$blk_shop_products .= $productBrief;

				}								

			}
		}

😕

[NogDog]

Which database interface are you using? (Most have some sort of num_rows() method or attribute.)

[CRichardson]

I use a MYSQL class. It does have a number of row counting functions however non work as I don't want to just count the one query, but a number of queries.

[NogDog]

Could you not create a counter variable and simply increment it with the number of rows returned by each query?

[CRichardson]

NogDog wrote:

Could you not create a counter variable and simply increment it with the number of rows returned by each query?

I have tried that! Not had much luck. I believe it is due to the number of loops I have inside one-another.

[NogDog]

Just initialize the variable before you start the loops, then increment it whenever you do one of the queries you want to count:

$rowCount = 0;
while(....)
{
   // do query, then
   $rowCount += num_rows_function_for_your_db_class();
}
echo "Retrieved a total of $rowCount rows.";

[CRichardson]

Thank you for your suggestion however it doesn't want to work.

Where it should output 19 it gives me:

085917131513

Do remember that I have the following within the code:

• WHILE - pick all the categories out
• FOREACH - explode their parents
• IF - product category equals a category or a categories parent
• WHILE - query the database

[laserlight]

It looks like an error in your implementation rather than in the idea. What is the code?

[lazzerous]

so you want the counter to increment for every iteration of every loop?

$results_counter = 0;
//search for matching sub cats
        $query = $db->query("SELECT * FROM cart_blk_shop_categories");
        while ( $dx = $db->fetch_obj($query) ) {
              $results_counter++;

    $position = $dx->position;
    $id = $dx->id;

        $categories = explode(">",$position);

        foreach ( $categories as $c ) {
            $results_counter++;

            if($c == $catID) {

                $abovequery = $db->query("SELECT * FROM cart_blk_shop_products WHERE productLive = 1 AND productCATID = $id");

                while ( $dz = $db->fetch_obj($abovequery) ) {
                    $results_counter++;

                    $prodName         = $dz->productName;
                    $prodNewName     = addUnderscore($prodName);
                    $prodPrice         = $dz->productPrice;
                    $prodPrice         = number_format($prodPrice, 2, ".", ",");
                    $prodID         = $dz->productID;

                        $thumbquery     = $db->query("select * from cart_blk_shop_product_images where photoProductID='$prodID'");
                        $objects         = $db->fetch_obj($thumbquery);
                        $prodThumb         = $objects->photoThumb;

                    eval ("\$productBrief = \"".gettemplate("product_brief")."\";");
                    $blk_shop_products .= $productBrief;

                }                                

            }
        } 

echo $results_counter;

[CRichardson]

lazzerous- sorry you must have misunderstood me. I want the counter to count the number of products returned in multiple categories.

laserlight- not too sure what you mean? I am just echoing out the variable.

[NogDog]

I think the problem is that we do not understand exactly what it is you want to count. Also, my suspicion is that a join or two in one query might replace a lot of that looping and return only the stuff you need to process. But I'm really not clear on exactly what you want to accomplish, so it's all guesswork on my part right now. Perhaps a small sample of hypothetical data with the expected count result would help clarify things?

[Weedpacket]

I'll agree with NogDog, here. There may be some sort of confusion about what is being counted. When I looked at the code I saw:

foreach ( $categories as $c ) {
    if($c == $catID) {
        $abovequery = $db->query("SELECT * FROM cart_blk_shop_products WHERE productLive = 1 AND productCATID = $id");

As it stands, the results returned by this query don't depend on $categories at all, so probably shouldn't be in the loop ($catID doesn't seem to be defined).
I'm going to guess that the query is only supposed to run no more than once: in fact, the entire body of that loop is supposed to run no more than once (when $c==$catID), assuming that $c can only appear in $categories once. In which case the entire loop is redundant, since the only other thing it does is count the number of elements in the $categories array.

[CRichardson]

Thank you for your help. I seemed to have solved this problem.
Chris.