Jump to content
kilnakorr

How to count rows from query?

Recommended Posts

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.

Share this post


Link to post
Share on other sites
Posted (edited)

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

 

Have a sample structure, with some data?

 

Jim

Edited by trochia

Share this post


Link to post
Share on other sites

Use the aggregation function COUNT() with GROUP BY

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

 

Share this post


Link to post
Share on other sites

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

 

 

 

Share this post


Link to post
Share on other sites
SELECT department
     , step
     , COUNT(*) as total
FROM DB
GROUP BY department, step

 

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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?

 

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites
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 :P

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.