Question 284117 in Wordpress Question posted in
The official Wordpress documentation can be found here.

WordPress – Showing total wordcount and earnings using str_word_count

I am trying to create a table showing a list of staff earnings by month and year filter in a custom plugin.

I have troubleshoot but couldn’t solve the problem any further. Here is the code;

I have set cost per word in database to use for the calculations but I’m confused why this code is not working. Anyone to correct or give full code please:

function hdm_admin_earnings_page() {
    global $wpdb;
    $table_name = $wpdb->prefix . 'hdm_article_keywords';

    // Fetch the cost per word from the database
    $cost_per_word = get_option('hdm_cost_per_word');

    // Get selected month and year from the form
    $selected_month = isset($_POST['earnings_month']) ? intval($_POST['earnings_month']) : date('m');
    $selected_year = isset($_POST['earnings_year']) ? intval($_POST['earnings_year']) : date('Y');

    // Query to get user earnings for the selected month and year
    $query = "SELECT u.ID, u.user_login, u.display_name,
                     COUNT(p.ID) as post_count,
                     SUM(str_word_count(p.post_content)) as word_count,
                     SUM(str_word_count(p.post_content)) * %f as earnings
              FROM $wpdb->users u
              LEFT JOIN $wpdb->posts p ON u.ID = p.post_author
              WHERE p.post_status = 'publish'
                AND MONTH(p.post_date) = %d
                AND YEAR(p.post_date) = %d
              GROUP BY u.ID
              ORDER BY earnings DESC";

    $query = $wpdb->prepare($query, $cost_per_word, $selected_month, $selected_year);

    // Get the results from the database
    $results = $wpdb->get_results($query);

    // Display the form for filtering by month and year
    echo '<form method="post">';
    echo '<label for="earnings_month">Select Month:</label>';
    echo '<select name="earnings_month" id="earnings_month">';
    for ($month = 1; $month <= 12; $month++) {
        echo '<option value="' . $month . '"' . selected($selected_month, $month, false) . '>' . date('F', mktime(0, 0, 0, $month, 1)) . '</option>';
    }
    echo '</select>';

    echo '<label for="earnings_year">Select Year:</label>';
    echo '<select name="earnings_year" id="earnings_year">';
    for ($year = 2019; $year <= intval(date('Y')); $year++) {
        echo '<option value="' . $year . '"' . selected($selected_year, $year, false) . '>' . $year . '</option>';
    }
    echo '</select>';

    echo '<input class="hdm-button" type="submit" value="Filter">';
    echo '</form>';

    // Display the table header
    echo '<table class="hdm-earnings-table">';
    echo '<thead>';
    echo '<tr>';
    echo '<th>Serial Number</th>';
    echo '<th>Username</th>';
    echo '<th>Display Name</th>';
    echo '<th>Number of Posts</th>';
    echo '<th>Total Word Count</th>';
    echo '<th>Earnings</th>';
    echo '</tr>';
    echo '</thead>';
    echo '<tbody>';

    // Display the results in the table
    if ($results) {
        $serial_number = 1;
        foreach ($results as $result) {
            echo '<tr>';
            echo '<td>' . $serial_number . '</td>';
            echo '<td>' . $result->user_login . '</td>';
            echo '<td>' . $result->display_name . '</td>';
            echo '<td>' . $result->post_count . '</td>';
            echo '<td>' . $result->word_count . '</td>';
            echo '<td>$' . number_format($result->earnings, 2) . '</td>';
            echo '</tr>';
            $serial_number++;
        }
    } else {
        echo '<tr><td colspan="6">No data available for the selected month and year.</td></tr>';
    }

    echo '</tbody>';
    echo '</table>';

    // Display total earnings at the bottom
    if ($results) {
        $total_earnings = array_sum(wp_list_pluck($results, 'earnings'));
        $month_name = date('F', mktime(0, 0, 0, $selected_month, 1));
        echo '<tr class="total-row">';
        echo '<td colspan="5">Total Earnings for ' . $month_name . ', ' . $selected_year . '</td>';
        echo '<td>$' . number_format($total_earnings, 2) . '</td>';
        echo '</tr>';
    } else {
        echo '<tr class="total-row">';
        echo '<td colspan="6">Total Earnings for ' . date('F', mktime(0, 0, 0, $selected_month, 1)) . ', ' . $selected_year . '</td>';
        echo '<td>$0.00</td>';
        echo '</tr>';
    }

    // Query to get top 5 all-time earners
    $query_top_5 = "SELECT u.ID, u.user_login, u.display_name,
                        COUNT(p.ID) as post_count,
                        SUM(str_word_count(p.post_content)) as word_count,
                        SUM(str_word_count(p.post_content)) * %f as earnings
                    FROM $wpdb->users u
                    LEFT JOIN $wpdb->posts p ON u.ID = p.post_author
                    WHERE p.post_status = 'publish'
                    GROUP BY u.ID
                    ORDER BY earnings DESC
                    LIMIT 5";

    $query_top_5 = $wpdb->prepare($query_top_5, $cost_per_word);

    // Get the results for top 5 all-time earners
    $results_top_5 = $wpdb->get_results($query_top_5);

    // Display the table header for top 5 all-time earners
    echo '<table class="hdm-earnings-table">';
    echo '<thead>';
    echo '<tr>';
    echo '<th>Serial Number</th>';
    echo '<th>Username</th>';
    echo '<th>Display Name</th>';
    echo '<th>Number of Posts</th>';
    echo '<th>Total Word Count</th>';
    echo '<th>Earnings</th>';
    echo '</tr>';
    echo '</thead>';
    echo '<tbody>';

    // Display the results for top 5 all-time earners in the table
    if ($results_top_5) {
        $serial_number_top_5 = 1;
        foreach ($results_top_5 as $result_top_5) {
            echo '<tr>';
            echo '<td>' . $serial_number_top_5 . '</td>';
            echo '<td>' . $result_top_5->user_login . '</td>';
            echo '<td>' . $result_top_5->display_name . '</td>';
            echo '<td>' . $result_top_5->post_count . '</td>';
            echo '<td>' . $result_top_5->word_count . '</td>';
            echo '<td>$' . number_format($result_top_5->earnings, 2) . '</td>';
            echo '</tr>';
            $serial_number_top_5++;
        }
    } else {
        echo '<tr><td colspan="6">No data available for top 5 all-time earners.</td></tr>';
    }

    echo '</tbody>';
    echo '</table>';
}
?>
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thanks to everyone that contributed. I have solved it and final code used is:

    foreach ($published_posts as $post) {
// Get author name
$author_name = get_the_author_meta('display_name', $post->post_author);

// Calculate earnings for each post
$word_count = str_word_count(strip_tags($post->post_content));
$post_earnings = $word_count * $cost_per_word;

// Accumulate data in the author_data array
if (!isset($author_data[$author_name])) {
    $author_data[$author_name] = array(
        'post_count' => 0,
        'word_count' => 0,
        'earnings'   => 0,
    );
}

$author_data[$author_name]['post_count']++;
$author_data[$author_name]['word_count'] += $word_count;
$author_data[$author_name]['earnings'] += $post_earnings;

    }


  2. 0

    Your code attempts to use the php function called str_word_count() in a SQL query. You Can’t Do That™. You need to read each post_content column into a php string then count the words in that string.

    Programs that use SQL are a bit funny-looking when you first see them, because SQL is an entirely different language embedded in the application language (php for WordPress). They have different syntax, different ways of running, different bedded functions, and even different security vulnerabilities. str_word_count() as a runtime function doesn’t exist in SQL, only php.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search