php - 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.

Answer 1

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?