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.
Advertisement
Answer
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']; ?>