Count?

[mikenl]

I need to know the number of categories (cat_id).

 

I have tried to use COUNT but cannot get it to work... Please assist if you know how to do it?

 

$lookup = "
    
SELECT 

prostorage_roh_eingang.id as subcat_id, 
prostorage_roh_eingang.charge, 
prostorage_roh_eingang.lieferant, 
prostorage_roh_eingang.qty_kg,
prostorage_roh_eingang.qty_l,
prostorage_charge.charge as cat_id,
prostorage_charge.date,
prostorage_roh.id,
prostorage_roh.name,
prostorage_roh.abb
    
FROM 

prostorage_charge JOIN prostorage_roh_eingang JOIN prostorage_roh
    
ON

prostorage_roh_eingang.charge = prostorage_charge.charge

WHERE 

prostorage_roh_eingang.id = prostorage_roh.id

AND

prostorage_charge.date < '$today'

ORDER BY prostorage_roh_eingang.charge

";

  $query = mysql_query($lookup);
  
  $num_rows = mysql_num_rows($query);  

$current_catid = '';

while ($row = mysql_fetch_assoc($query)) {

    if ($current_catid != $row['cat_id']) {

// Close previous subcat table if not 1st time through
      if ($current_catid != '') { echo '</div>'; }

      //First entry for this category - show category header
      $current_catid = $row['cat_id'];

} // End if 

    //Display the subcat data

} //End while loop

[NArc0t1c]

$query = mysql_query("SELECT * FROM table");
$get_rows = mysql_num_rows($query);
echo 'There is ' . $get_rows . ' rows in table.';

[mikenl]

THX but I don't need to know the rows in the table, I only need to know the number in one column 'prostorage_charge.charge as cat_id'

[fenway]

You're trying to do what, exactly?

[mikenl]

Hi, I want to know the number of found rows for prostorage_charge.charge as cat_id, see the code.

 

So, not the total number of rows returned, that is not the problem. Only the number of rows that were found for prostorage_charge.charge as cat_id.

 

I need this because there are apparently 4 categories (prostorage_charge.charge as cat_id) returned by this query, and 5 subcategories (prostorage_roh_eingang.id as subcat_id).

 

I hope this makes it a bit clearer?

[teng84]

use a group by cause

something like

 

select count (id) from table  group by skills

 

 

[mikenl]

Hi,

 

Thx for the feedback.

 

I have tried it but this influences the total number of rows found.

 

If I do a GROUP BY, the total number of rows will be limited to that clause.

 

I need to find the number of categories, subcategories and total number of rows in one query.

[fenway]

Assuming that you can retrieve all of these with a single query, WITH ROLLUP with help with your counts.

[mikenl]

I will study this, thx. I saw the topic was moved to javascript, which I don't think is correct, because it's a pure MySQL problem.

 

Meanwhile I have split the query in 2. I first get the categories, and use IN to search in the array that is the first query.

[fenway]

I will study this, thanks. I saw the topic was moved to javascript, which I don't think is correct, because it's a pure MySQL problem.

Huh?

[mikenl]

The problem is not with javascript.

 

I only need to get the counts from mysql in order to use them in javascript, so the problem is with mysql.

 

I just wanted to do all in one query, so that I don't have to "bother" the db twice for every page load.

[Illusion]

try this

SELECT count(prostorage_roh_eingang.id) as cnt_subcat_id,count(prostorage_charge.charge) as cnt_cat_id FROM prostorage_charge,prostorage_roh_eingang,prostorage_roh where prostorage_roh_eingang.charge = prostorage_charge.charge AND prostorage_roh_eingang.id = prostorage_roh.id AND prostorage_charge.date < '$today' ORDER BY prostorage_roh_eingang.charge;

I didn't check what I did