How to count rows from query?

[kilnakorr]

Hi

I'm not at all good at PHP and haven't really played with it for some years, so bare with me if code examples are old

I'm looking for a simple solution to count rows (MySQL), without using multiple queries.

The below code is quite simple, counting rows where 'Department' is 'Sales':

$sql ="SELECT * FROM DB WHERE Department ='Sales'";
$result=mysqli_query($con,$sql);
$rowcount=mysqli_num_rows($result);

But what If I need to count the rows for the different departments?:

$sql ="SELECT * FROM DB WHERE Department ='Sales'";
$result=mysqli_query($con,$sql);
$rowcount=mysqli_num_rows($result);

$sql ="SELECT * FROM DB WHERE Department ='Support'";
$result=mysqli_query($con,$sql);
$rowcount=mysqli_num_rows($result);

$sql ="SELECT * FROM DB WHERE Department ='Online'";
$result=mysqli_query($con,$sql);
$rowcount=mysqli_num_rows($result);

There must be a better / simpler way to this with a single query?

Hope for someone to show me the light

 

PS. Once this is resolved, I'll probably need further help, to accomplish my final result.

[trochia]

Are sales, support  and online...all in the same column? Are they text?

 

Have a sample structure, with some data?

 

Jim

Edited March 13, 2019 by trochia

[trochia]

•  
• 
• Members
• 0
• 73 posts

• Report post
•  

Posted just now

Are sales, support  and online...all in the same column? Are they text?

 

Have a sample structure, with some data?

 

Jim

[Barand]

Use the aggregation function COUNT() with GROUP BY

SELECT department
     , COUNT(*) as total
FROM DB
GROUP BY department

 

[kilnakorr]

Thanks for the fast replies!

They are all in same column called 'Department'.

Maybe I should try and explain the entire problem, and what I wish to achieve.

First the database:

+------+----------+-------------+
| ID   | Step     | Department  | 
+------+----------+-------------+
|    1 | step1    | Sales       | 
|    2 | step1    | Support     |        
|    3 | step2    | Online      |
|    4 | step2    | Online      | 
|    5 | step1    | Support     | 
|    6 | step2    | Support     | 
|    7 | step3    | Online      | 
|    8 | step2    | Sales       |
|    9 | step4    | Support     | 
+------+----------+-------------+

What are actually need as end result is to count the number of rows for EACH Department and EACH step:

so I need the rows for:

Sales step1

Sales step2

Sales step3

etc

Support step1

Support step2

Support step3

and so on...

 

They will be 4 or 5 Values (text) in column 'Department', but hundreds of values in column 'Step'.

So whats the simplest approach to get all these counts?

 

I will afterwards need to copy an image from a folder based on the count of rows - so below code (just example) needs to work for every row count:

if($result  > 0 && < 25) {
copy(image1.png,'other_image.png');
}
elseif($result >= 25 && $cpr_errors < 75 {
$copy(image2.png,'other_image.png');
}
elseif($result <= 75) {
$copy(image3.png,'other_image.png');
}

 

This means hundreds of images copied and replaced.

Hope you get the idea

 

 

 

[Barand]

SELECT department
     , step
     , COUNT(*) as total
FROM DB
GROUP BY department, step

 

[kilnakorr]

16 minutes ago, Barand said:

SELECT department
     , step
     , COUNT(*) as total
FROM DB
GROUP BY department, step

 

So how will perform the remaining code?:

if($result  > 0 && < 25) {
copy(image1.png,'other_image.png');
}

How can I get the '$result' for example 'Sales' 'step1'?

And will I be forced to do above if statement for all variations or is there a better way?

[Barand]

If you had bothered to run that query you would have seen results like these (using the data you posted)

mysql> SELECT department
    ->      , step
    ->      , COUNT(*) as total
    -> FROM testdb1
    -> GROUP BY department, step;
+------------+-------+-------+
| department | step  | total |
+------------+-------+-------+
| Online     | step2 |     2 |
| Online     | step3 |     1 |
| Sales      | step1 |     1 |
| Sales      | step2 |     1 |
| Support    | step1 |     2 |
| Support    | step2 |     1 |
| Support    | step4 |     1 |
+------------+-------+-------+

From which, you should be able to see how to continue.

[kilnakorr]

9 minutes ago, Barand said:

If you had bothered to run that query you would have seen results like these (using the data you posted)

mysql> SELECT department
    ->      , step
    ->      , COUNT(*) as total
    -> FROM testdb1
    -> GROUP BY department, step;
+------------+-------+-------+
| department | step  | total |
+------------+-------+-------+
| Online     | step2 |     2 |
| Online     | step3 |     1 |
| Sales      | step1 |     1 |
| Sales      | step2 |     1 |
| Support    | step1 |     2 |
| Support    | step2 |     1 |
| Support    | step4 |     1 |
+------------+-------+-------+

From which, you should be able to see how to continue.

It's not that I'm lazy.  As mentioned in my original post  'I'm not at all good at PHP'.

I do appreciate the time you and everyone else is spending trying to show me possible solutions, but what you have already shown me simply isn't enough for me to understand, or the shown code isn't what I'm looking for.

I do get the fact that the query sums up the number of unique steps for each department, which truly was my first hurdle, but how do I get those 'totals' into the next part of the code?:

// total for Online, step1
if(totalgoeshere  > 0 && < 25) {
copy(image1.png,'other_image.png');
}

// total for Online, step2
if(totalgoeshere  > 0 && < 25) {
copy(image1.png,'other_image.png');
}
// total for Online, step3
if(totalgoeshere  > 0 && < 25) {
copy(image1.png,'other_image.png');
}

// total for Sales, step1
if(totalgoeshere  > 0 && < 25) {
copy(image1.png,'other_image.png');
}
// and so on and on and on.....

And will I be forced to do the above for each department and step?

 

[ginerjm]

Change the query:

mysql> SELECT department
          , step
          , COUNT(*) as total
     FROM testdb1
	  order by department, step 
     GROUP BY department, step;

Then run the query and do this with results:

//  begin an html table 
echo "<table>
	<tr>
	<th>Department</th>
    <th>Step</th>
    <th>Total</th>
    </tr>";
  //  run a loop to grab each row using your query results
  while ((EXECUTE A FETCH ON YOUR QUERY RESULTS HERE))
  {  // assign the fetch to $row variable
     echo "<tr>
  	<td>".$row['department']."</td>
  	<td>".$row['step']."</td>
  	<td>".Rwos['total']."</td></tr>";
  }
  // end table
  echo "</table>";

I assume this is the help you were fishing for?

[kilnakorr]

16 hours ago, ginerjm said:

I assume this is the help you were fishing for?

It helps a bunch, and works great for outputting a neat table.

I figured out how to use it for the rest of my code. Thank you so much for helping out a noob

[ginerjm]

HTH!