PHP实现MySQL中active/inactive/deleted状态数据统计的问题

Hey there! Let's get those active/inactive/deleted counts working properly. The issue with your current code is twofold: you're pulling every single row to count manually, and you're trying to call num_rows() on a row object (that method belongs to the query result, not individual rows). Instead, let's let MySQL handle the aggregation efficiently with a single query.

Option 1: Grouped Query (Flexible for Any Status) #

This approach groups results by post_status so you get a count for each status that exists in the data. We'll also fix the SQL injection risk (never directly interpolate user data into queries!) using parameter binding.

Updated SQL & PHP Code #

<?php
$user_id = $this->session->userdata('user_id');

// Use parameter binding to avoid SQL injection
$query = $this->custom_query(
    "SELECT post_status, COUNT(*) as status_count 
     FROM listings 
     WHERE user_id = ? 
     GROUP BY post_status",
    [$user_id] // Pass user ID as a safe parameter
);

// Initialize counts to 0 (handles cases where a status has no entries)
$status_counts = [
    'active' => 0,
    'inactive' => 0,
    'deleted' => 0
];

if ($query->num_rows() > 0) {
    foreach ($query->result() as $row) {
        // Update our count array only if the status is one we care about
        if (array_key_exists($row->post_status, $status_counts)) {
            $status_counts[$row->post_status] = $row->status_count;
        }
    }
}

// Display the counts however you need
echo "Active Listings: " . $status_counts['active'] . "<br>";
echo "Inactive Listings: " . $status_counts['inactive'] . "<br>";
echo "Deleted Listings: " . $status_counts['deleted'] . "<br>";
?>

Option 2: Single-Row Aggregation (Direct Counts) #

If you want all counts returned in one row (great for straightforward display), use SUM(CASE...) to calculate each status count directly:

Updated SQL & PHP Code #

<?php
$user_id = $this->session->userdata('user_id');

$query = $this->custom_query(
    "SELECT 
        SUM(CASE WHEN post_status = 'active' THEN 1 ELSE 0 END) as active_count,
        SUM(CASE WHEN post_status = 'inactive' THEN 1 ELSE 0 END) as inactive_count,
        SUM(CASE WHEN post_status = 'deleted' THEN 1 ELSE 0 END) as deleted_count
     FROM listings 
     WHERE user_id = ?",
    [$user_id]
);

if ($query->num_rows() > 0) {
    $counts = $query->row();
    echo "Active Listings: " . $counts->active_count . "<br>";
    echo "Inactive Listings: " . $counts->inactive_count . "<br>";
    echo "Deleted Listings: " . $counts->deleted_count . "<br>";
} else {
    echo "You don't have any listings yet!";
}
?>

Key Notes to Remember #

• SQL Injection Protection: Always use parameter binding for user-supplied data (like user_id) instead of directly inserting it into your query. This keeps your database safe.
• Handling Empty Statuses: Initializing counts to 0 ensures you don't get missing values if a user has no listings of a certain status.

内容的提问来源于stack exchange，提问作者chris keenan