How to use global $wpdb query to run SQL query and echoing the result-CodePudding

I am trying to count the total events using the following SQL query. I am using global $wpdb to echo the results and to store them into a variable.

global $wpdb;
$current_d_t = strtotime(date("y-m-d"));
$sql = "SELECT COUNT(post.ID) as eventCountUpcoming FROM wp_posts AS post LEFT JOIN wp_postmeta AS meta ON post.ID = meta.post_id WHERE post.post_type =  'ajde_events' AND meta.meta_key = 'evcal_erow' AND meta.meta_value > $current_d_t  AND post.post_status =  'publish' GROUP BY post.ID";
$result = $wpdb->get_results($sql, ARRAY_A);
$result_more = $result->fetch_assoc();
$countEvent = $result_more['eventCountUpcoming'];

getting this error

Call to a member function fetch_assoc() on an array

Tried for each loop too, using the following code,

foreach ($result  as $result_more){
$countEvent = $result_more['eventCountUpcoming'];
}

but getting this error:

CodePudding user response：

$result = $wpdb->get_results($sql, ARRAY_A); returns an array of results.

In order to echo the results, try looping the array:

$result = $wpdb->get_results($sql, ARRAY_A);
foreach ($result  as $res){
   var_dump($result);
}

CodePudding user response：

If you're just looking for one variable as the result. You can try using $wpdb->get_var()

global $wpdb;
$current_d_t = strtotime(date("y-m-d"));
$sql = "SELECT COUNT(post.ID) as eventCountUpcoming
    FROM {$wpdb->posts} AS post LEFT JOIN {$wpdb->postmeta} AS meta
    ON post.ID = meta.post_id
    WHERE post.post_type = 'ajde_events'
      AND meta.meta_key = 'evcal_erow'
      AND meta.meta_value
        > {$current_d_t}
      AND post.post_status = 'publish';"
$result = $wpdb->get_var($sql);

Then $result should be your count.

Groupby shouldn't be necessary if you're just counting Post ID's