Counting specific rows after dynamical WHERE clause query in PDO

Question

I'm following this tutorial for a dynamical WHERE clause query in PDO.

Let's suppose I have a short version of the tutorial code, just like this:



// always initialize a variable before use!
$conditions = [];
$parameters = [];

// conditional statements
if (!empty($_GET['name']))
{
   // here we are using LIKE with wildcard search
   // use it ONLY if really need it
   $conditions[] = 'name LIKE ?';
   $parameters[] = '%'.$_GET['name']."%";
}

if (!empty($_GET['sex']))
{
   // here we are using equality
   $conditions[] = 'sex = ?';
   $parameters[] = $_GET['sex'];
}


// the main query
$sql = "SELECT * FROM users";

// a smart code to add all conditions, if any
if ($conditions)
{
   $sql .= " WHERE ".implode(" AND ", $conditions);
}

// the usual prepare/execute/fetch routine
$stmt = $pdo->prepare($sql);
$stmt->execute($parameters);
$data = $stmt->fetchAll();

By adding the line $count = $stmt->rowCount();, I could count the rows.

Now let's suppose I have data like this:

| ID | User name    | sex     |
| -- | ------------ | --------|
| 1  | Sandra Smith | female  | 
| 2  | Ben Smith    | male    | 
| 3  | John Lee     | male    | 
| 4  | John Smith   | male    |

If I searched for "Smith" now, I would get 3 results and when I echo $count, I would get displayed the number 3.

Question: I also want to echo how many female and male user I get by the exact same query. I searched a lot over the internet, but can't find a case using PDO where this is solved. Also, I am not sure if I need another extra query to get the number of female and male user.

score 3 · Accepted Answer · answered Jan 08 '21 at 18:20

Use CASE statement in your query to get count of rows based in condition :

<?php
// always initialize a variable before use!
$conditions = [];
$parameters = [];

// conditional statements
if (!empty($_GET['name']))
{
   // here we are using LIKE with wildcard search
   // use it ONLY if really need it
   $conditions[] = 'name LIKE ?';
   $parameters[] = '%'.$_GET['name']."%";
}

if (!empty($_GET['sex']))
{
   // here we are using equality
   $conditions[] = 'sex = ?';
   $parameters[] = $_GET['sex'];
}


// the main query
$sql = "SELECT count(*) as count_all,
sum(case when sex  = 'female ' then 1 else 0 end) AS count_female,
sum(case when sex  = 'male ' then 1 else 0 end) AS count_male
FROM users";

// a smart code to add all conditions, if any
if ($conditions)
{
   $sql .= " WHERE ".implode(" AND ", $conditions);
}

// the usual prepare/execute/fetch routine
$stmt = $pdo->prepare($sql);
$stmt->execute($parameters);
$row = $stmt->fetch();

// count all users
$count_all = $row['count_all'];

// count female users
$count_all = $row['count_female'];

// count male users
$count_all = $row['count_male'];

?>

check : How to get multiple counts with one SQL query?

But what do I do with the original query `$sql = "SELECT * FROM users";` then? I want to display the result of the original query (as a whole with names and so on) as well as `$row['count_female']` and `$row['count_female']` Should I just connect the two queries? — Fish, Jan 08 '21 at 18:42
you can add user informations to query by adding `*` or `column name` to query ex: `select *, count(*) as count_all, sum(case when sex = 'female ' then 1 else 0 end) AS count_female, sum(case when sex = 'male ' then 1 else 0 end) AS count_male FROM users` you will get all users informations and count columns as in each row, also use `fetchAll()` instead of `fetch()` to get all rows instead of one row, yes you will have unnecessary duplicates of count columns in each row but there is no way to do it in one query except this — Youssef, Jan 08 '21 at 19:25

Counting specific rows after dynamical WHERE clause query in PDO

1 Answers1