Forum

I am in the process of converting our church website to WordPress and found this plugin (much appreciated, thanks!)

I found the cause of this bug, in the 'admin.php' page, the MySQL queries for preachers, series, and services all have the same bug.

Each does something like this:

$preachers = $wpdb->get_results("SELECT {$wpdb->prefix}sb_preachers.*, COUNT({$wpdb->prefix}sb_sermons.id) AS sermon_count FROM {$wpdb->prefix}sb_preachers LEFT JOIN {$wpdb->prefix}sb_sermons ON {$wpdb->prefix}sb_preachers.id=preacher_id GROUP BY preacher_id ORDER BY name ASC");

Which in this case returns zero records if there are no sermons. To show preachers regardless of whether there are sermons (which I think is the intent), the "GROUP BY preacher_id" clause needs to be changed to "GROUP BY id". The reason is even though the initial query finds preachers without sermons, the group by clause removes them since there are no sermons. By changing it to "id" (the column on the sb_preachers table), it works as I expected.

The corrected query is:

$preachers = $wpdb->get_results("SELECT {$wpdb->prefix}sb_preachers.*, COUNT({$wpdb->prefix}sb_sermons.id) AS sermon_count FROM {$wpdb->prefix}sb_preachers LEFT JOIN {$wpdb->prefix}sb_sermons ON {$wpdb->prefix}sb_preachers.id=preacher_id GROUP BY id ORDER BY name ASC");

There is a similar problem with the GROUP BY for the series and services queries in admin.php

Doug

I also fixed the same bug with the series using this code

$series = $wpdb->get_results("SELECT {$wpdb->prefix}sb_series.*, COUNT({$wpdb->prefix}sb_sermons.id) AS sermon_count FROM {$wpdb->prefix}sb_series LEFT JOIN {$wpdb->prefix}sb_sermons ON series_id = {$wpdb->prefix}sb_series.id GROUP BY id ORDER BY name ASC");

instead of this code

$series = $wpdb->get_results("SELECT {$wpdb->prefix}sb_series.*, COUNT({$wpdb->prefix}sb_sermons.id) AS sermon_count FROM {$wpdb->prefix}sb_series LEFT JOIN {$wpdb->prefix}sb_sermons ON series_id = {$wpdb->prefix}sb_series.id GROUP BY series_id ORDER BY name ASC");

in admin.php.