How to find out how many rows match a rule?

Question

I want to find out how many rows in a table of my database meet a certain rule, specifically that "category" matches another variable and "published" is today or earlier. Then I want to simply echo that number.

I have no idea how to go about doing this, though.

I thought I could use count() but I'm not sure how to call just a single column and put the results of each row in an array.

Thanks!

Well, for starters, do you know how to connect to your db and execute a query against it? — Patrick Q, Apr 20 '18 at 20:08

score 0 · Answer 1 · answered Apr 20 '18 at 20:31

Do this using SQL:

Try this in your database (your columns/tables may be different):

SELECT count(*) FROM blog_posts WHERE category = 'hot_stuff' and published <= NOW();

Then to execute this in PHP, depending on your framework and connection to the database:

    $myCategory = 'hot_stuff';
    $myTable = 'blog_posts';

    $sql = "SELECT count(*) FROM {$myTable} WHERE category = '{$myCategory}' and published <= NOW();";

    $rowCount = $db->query($sql);
    echo $rowCount;

Don't Panic · Answer 2 · 2018-04-20T20:43:22.227

Connect to your database.

$pdo = new PDO($dsn, $user, $password);

Create a prepared statement. This is essential because you need to pass a value for category from your application to the query. It is not necessary to pass a value for the current date because the database can provide that for you. The ? is a placeholder where the parameter you pass will be bound.
```
$stmt = $pdo->prepare("SELECT COUNT(*) FROM your_table 
                   WHERE category = ? AND published  <= CURDATE()");
```
Do not concatenate the parameter into your SQL string (like WHERE category = '$category') because this will create an SQL injection vulnerability.

Execute the prepared statement using your specified value for category.

$stmt->execute([$category]);   // assuming you have already defined $category

Use fetchColumn to return a single value, the count of rows that matched your criteria.
```
echo $stmt->fetchColumn();
```

How to find out how many rows match a rule?

2 Answers2