Title: sql queries count(id)
Last modified: August 31, 2016

---

# sql queries count(id)

 *  Resolved [shadowgr](https://wordpress.org/support/users/shadowgr/)
 * (@shadowgr)
 * [9 years, 11 months ago](https://wordpress.org/support/topic/sql-queries-countid/)
 * [Check This Photo](http://prntscr.com/asaiai)
    Hello again 🙂
 * i want to update my table from wordpress`s backoffice
    and display i home page/
   time/league/match/tip/odds/book/win_loss
 *     ```
       SELECT date,time,league,match,tip,odds,book,win,loss
        FROM BC_TIPS
       ```
   
 *  and in another page i want to display BETS/WIN/LOSS
 *     ```
       SELECT count(ID) AS Bets,count(win_loss) AS WIN
        FROM BC_TIPS
        WHERE win_loss="yes"
       ```
   
 * its possible to do this?
    if yes where i must write my sql code and where i can
   take my shortcode to display my data ???
 * [https://wordpress.org/plugins/custom-database-tables/](https://wordpress.org/plugins/custom-database-tables/)

Viewing 15 replies - 1 through 15 (of 20 total)

1 [2](https://wordpress.org/support/topic/sql-queries-countid/page/2/?output_format=md)
[→](https://wordpress.org/support/topic/sql-queries-countid/page/2/?output_format=md)

 *  Plugin Author [ka2](https://wordpress.org/support/users/ka2/)
 * (@ka2)
 * [9 years, 11 months ago](https://wordpress.org/support/topic/sql-queries-countid/#post-7278377)
 * Yes, you can.
 * If your “BC_TIPS” table is already created, you can manage the data from the 
   WordPress administration screen by importing the table to this plugin.
 * If you want to display the table on the home page can be displayed in the following
   shortcode:
 *     ```
       [cdbt-view table="bc_tips" order_cols="date,time,league,match,tip,odds,book,win_loss"]
       ```
   
 * If you want to output by the short code via query of select statement used an
   alias, please use the filter as follows:
 * shortcode:
 *     ```
       [cdbt-view table="bc_tips" order_cols="ID"]
       ```
   
 * filterhooks(in “functions.php of your theme”):
 *     ```
       function bc_tips_get_data_sql( $sql, $table_name, $sql_clauses ) {
         if ( get_post()->ID === {Displayed Post ID} && 'bc_tips' === $table_name ) {
           $_overwrite_sql = <<<SQL
       SELECT count(ID) AS bets,count(win_loss) AS win
       FROM %s
       WHERE win_loss = 'yes'
       SQL;
   
       $sql = sprintf( $_overwrite_sql, $table_name );
         }
         return $sql;
       }
       add_filter( 'cdbt_crud_get_data_sql', 'bc_tips_get_data_sql', 10, 3 );
   
       function bc_tips_shortcode_custom_columns( $columns, $shortcode_name, $table ){
         if ( get_post()->ID === {Displayed Post ID} && 'cdbt-view' === $shortcode_name && 'bc_tips' === $table ) {
           $columns = [];
           $columns[] = [
             'label' => 'Bets',
             'property' => 'bets',
             'sortable' => false,
             'sortDirection' => 'asc',
             'dataNumric' => true,
             'truncateStrings' => '0',
             'className' => '',
           ];
           $columns[] = [
             'label' => 'WIN',
             'property' => 'win',
             'sortable' => false,
             'sortDirection' => 'asc',
             'dataNumric' => true,
             'truncateStrings' => '0',
             'className' => '',
           ];
         }
         return $columns;
       }
       add_filter( 'cdbt_shortcode_custom_columns', 'bc_tips_shortcode_custom_columns', 10, 3 );
       ```
   
 * Please try it.
 * Thank you,
 *  Thread Starter [shadowgr](https://wordpress.org/support/users/shadowgr/)
 * (@shadowgr)
 * [9 years, 11 months ago](https://wordpress.org/support/topic/sql-queries-countid/#post-7278445)
 * Parse error: syntax error, unexpected ‘{‘
 * here: `if ( get_post()->ID === {Displayed Post ID} && 'bc_tips' === $table_name){`
 * i m trying your code to understand how this works
    but i have this error and 
   all brackets its ok…
 * [check this img](http://prntscr.com/asmka6)
 * *UPDATE
    i placed a semicolon here
 * > $_overwrite_sql = <<< SQL;
 *  but i have the same error
 *  Thread Starter [shadowgr](https://wordpress.org/support/users/shadowgr/)
 * (@shadowgr)
 * [9 years, 11 months ago](https://wordpress.org/support/topic/sql-queries-countid/#post-7278466)
 * [now i have this error to add_filter();](http://prnt.sc/asoslu)
 *  Thread Starter [shadowgr](https://wordpress.org/support/users/shadowgr/)
 * (@shadowgr)
 * [9 years, 11 months ago](https://wordpress.org/support/topic/sql-queries-countid/#post-7278475)
 * sir can i add you on skype? or can you give me your email?
 *  Plugin Author [ka2](https://wordpress.org/support/users/ka2/)
 * (@ka2)
 * [9 years, 11 months ago](https://wordpress.org/support/topic/sql-queries-countid/#post-7278480)
 * Sorry, description of sample code was leaking.
 *     ```
       if ( get_post()->ID === {Displayed Post ID} && 'cdbt-view' === $shortcode_name && 'bc_tips' === $table ) {
       ```
   
 * You should convert to post id of page that you put shortcode from the “{Displayed
   post ID}” in the above part of code. Or, at the “get_post()->ID === {Displayed
   Post ID} &&” please remove.
 * Thank you,
 *  Thread Starter [shadowgr](https://wordpress.org/support/users/shadowgr/)
 * (@shadowgr)
 * [9 years, 11 months ago](https://wordpress.org/support/topic/sql-queries-countid/#post-7278491)
 * [My shortcode](http://prntscr.com/asxdzq)
 * [My theme`s fuctions.php ](http://prntscr.com/asxeyq)
 * [Test Page](http://prntscr.com/asxfr0)
    No data in this table.
 *  Plugin Author [ka2](https://wordpress.org/support/users/ka2/)
 * (@ka2)
 * [9 years, 11 months ago](https://wordpress.org/support/topic/sql-queries-countid/#post-7278505)
 * In the source that you added in “functions.php” has mistake.
 * Your source:
 *     ```
       if ( get_post()->ID === (ID) && 'cdbt-view' === $shortcode_name && 'bc_tips' === $table )
       ```
   
 * Correct source:
 *     ```
       if ( 'cdbt-view' === $shortcode_name && 'bc_tips' === $table )
       ```
   
 * Please try it.
 *  Thread Starter [shadowgr](https://wordpress.org/support/users/shadowgr/)
 * (@shadowgr)
 * [9 years, 11 months ago](https://wordpress.org/support/topic/sql-queries-countid/#post-7278510)
 * [Here is my new code](http://prntscr.com/atehjc)
 * [display table in my test page](http://prntscr.com/atew9a)
 * [my backoffice table in wordpress w/o code in fuctions.php](http://prntscr.com/atenci)
   
   [my backoffice table in wordpress after code](http://prntscr.com/atejz0)
 * i cant display data like this
 * > [cdbt-view table=”wp_tips” order_cols=”date,time,league,match,tip,odds,book,
   > win_loss”]
 * cause if i have write code in functions.php my data in wp_tips has gone and BETS/
   WINS/LOSS/VOID had 0 columns to count :/
 * Thanks for You help!
    sorry for being annoying.
 *  Plugin Author [ka2](https://wordpress.org/support/users/ka2/)
 * (@ka2)
 * [9 years, 11 months ago](https://wordpress.org/support/topic/sql-queries-countid/#post-7278583)
 * Sorry for my late reply.
 * You need to be careful about the uppercase/lowercase of alphabet of the column
   named as aliased. At the property values of the column definition, you must be
   the same of aliased the column name.
 *     ```
       $_overwrite_sql = <<<SQL
       SELECT count(id) AS BETS,
         Sum(IF(win_loss = 'win', 1, 0)) AS Wins,
         Sum(IF(win_loss = 'loss', 1, 0)) AS Looses,
         Sum(IF(win_loss = 'void', 1, 0)) AS Void
       FROM %s;
       SQL;
         $sql = sprintf( $_overwrite_sql, $table_name );
       ```
   
 *     ```
       $columns[] = [
           'label' => 'BETS', // Actual displayed strings
           'property' => 'BETS', // Alias column name
           ...
         $columns[] = [
           'label' => 'Wins',
           'property' => 'Wins',
           ...
       ```
   
 * Please try again with reference to the above code.
 * Thank you,
 *  Thread Starter [shadowgr](https://wordpress.org/support/users/shadowgr/)
 * (@shadowgr)
 * [9 years, 11 months ago](https://wordpress.org/support/topic/sql-queries-countid/#post-7278604)
 * you were right 🙂
    [Talbe with bets](http://prntscr.com/ay8oz9)
 * but now how can i display data from my table ..when i go to make a new shortcode
   my data has lost..
 * [Table with code in functions.php](http://prntscr.com/ay8pgn)
    [Before ](http://prntscr.com/ay8q11)
 * can you understand what I mean?
 * Thank you!
 *  Plugin Author [ka2](https://wordpress.org/support/users/ka2/)
 * (@ka2)
 * [9 years, 11 months ago](https://wordpress.org/support/topic/sql-queries-countid/#post-7278606)
 * I’m glad to carry out a your wishes.
 * Please enclose the filter hook by the conditional statement as shown below to
   disable the customization of the table output on the management screen:
 *     ```
       if ( ! is_admin() ) :
   
       function bc_tips_get_data_sql( $sql, $table_name, $sql_clauses ) {
   
       omission ...
   
       }
       add_filter( 'cdbt_crud_get_data_sql', 'bc_tips_get_data_sql', 10, 3 );
   
       function bc_tips_shortcode_custom_columns( $columns, $shortcode_name, $table ){
   
       omission ...
   
       }
       add_filter( 'cdbt_shortcode_custom_columns', 'bc_tips_shortcode_custom_columns', 10, 3 );
   
       endif;
       ```
   
 * Thank you,
 *  Thread Starter [shadowgr](https://wordpress.org/support/users/shadowgr/)
 * (@shadowgr)
 * [9 years, 11 months ago](https://wordpress.org/support/topic/sql-queries-countid/#post-7278607)
 * i did it but still i cant display my |date,time,league,match,tip,odds,book,win_loss
   | 🙁
    [check my test page](http://prntscr.com/ay9lo3)
 *  Plugin Author [ka2](https://wordpress.org/support/users/ka2/)
 * (@ka2)
 * [9 years, 11 months ago](https://wordpress.org/support/topic/sql-queries-countid/#post-7278608)
 * Hmm…
    Please show me source code that you have added in the “functions.php”.
 * Thank you for taking care of it.
 *  Thread Starter [shadowgr](https://wordpress.org/support/users/shadowgr/)
 * (@shadowgr)
 * [9 years, 11 months ago](https://wordpress.org/support/topic/sql-queries-countid/#post-7278609)
 *     ```
       if( ! is_admin()):
       function wp_tips_get_data_sql( $sql, $table_name, $sql_clauses ) {
         if ('wp_tips' === $table_name ) {
           $_overwrite_sql = <<< SQL
       SELECT
           count(id) AS BETS,
           Sum(IF(win_loss = 'win', 1, 0)) As Wins,
           Sum(IF(win_loss = 'loss', 1, 0)) As Looses,
           Sum(IF(win_loss = 'void',1, 0)) AS Void
   
       FROM %s ;
       SQL;
   
       $sql = sprintf( $_overwrite_sql, $table_name );
         }
         return $sql;
       }
       add_filter( 'cdbt_crud_get_data_sql', 'wp_tips_get_data_sql', 10, 3 );
   
       function wp_tips_shortcode_custom_columns( $columns, $shortcode_name, $table ){
         if ( 'cdbt-view' === $shortcode_name && 'wp_tips' === $table ) {
           $columns = [];
           $columns[] = [
             'label' => 'BETS',
             'property' => 'BETS',
             'sortable' => false,
             'sortDirection' => 'asc',
             'dataNumric' => true,
             'truncateStrings' => '0',
             'className' => '',
           ];
           $columns[] = [
             'label' => 'Wins',
             'property' => 'Wins',
             'sortable' => false,
             'sortDirection' => 'asc',
             'dataNumric' => true,
             'truncateStrings' => '0',
             'className' => '',
           ];
            $columns[] = [
             'label' => 'Looses',
             'property' => 'Looses',
             'sortable' => false,
             'sortDirection' => 'asc',
             'dataNumric' => true,
             'truncateStrings' => '0',
             'className' => '',
           ];
            $columns[] = [
             'label' => 'Void',
             'property' => 'Void',
             'sortable' => false,
             'sortDirection' => 'asc',
             'dataNumric' => true,
             'truncateStrings' => '0',
             'className' => '',
           ];
         }
         return $columns;
       }
       add_filter( 'cdbt_shortcode_custom_columns', 'wp_tips_shortcode_custom_columns', 10, 3 );
       endif;
       ```
   
 *  Plugin Author [ka2](https://wordpress.org/support/users/ka2/)
 * (@ka2)
 * [9 years, 11 months ago](https://wordpress.org/support/topic/sql-queries-countid/#post-7278610)
 * Thank you for providing a code, however I could not found a problem in your code.
 * Please try to change as following. Then you still will not display the original
   table structure in the table management screen?
 *     ```
       function wp_tips_get_data_sql( $sql, $table_name, $sql_clauses ) {
         if ( ! is_admin() && 'wp_tips' === $table_name ) {
           $_overwrite_sql = <<< SQL
       SELECT
           count(id) AS BETS,
           Sum(IF(win_loss = 'win', 1, 0)) As Wins,
           Sum(IF(win_loss = 'loss', 1, 0)) As Looses,
           Sum(IF(win_loss = 'void',1, 0)) AS Void
   
       FROM %s ;
       SQL;
   
       $sql = sprintf( $_overwrite_sql, $table_name );
         }
         return $sql;
       }
       add_filter( 'cdbt_crud_get_data_sql', 'wp_tips_get_data_sql', 10, 3 );
   
       function wp_tips_shortcode_custom_columns( $columns, $shortcode_name, $table ){
         if ( ! is_admin() && 'cdbt-view' === $shortcode_name && 'wp_tips' === $table ) {
           $columns = [];
           $columns[] = [
             'label' => 'BETS',
             'property' => 'BETS',
             'sortable' => false,
             'sortDirection' => 'asc',
             'dataNumric' => true,
             'truncateStrings' => '0',
             'className' => '',
           ];
           $columns[] = [
             'label' => 'Wins',
             'property' => 'Wins',
             'sortable' => false,
             'sortDirection' => 'asc',
             'dataNumric' => true,
             'truncateStrings' => '0',
             'className' => '',
           ];
            $columns[] = [
             'label' => 'Looses',
             'property' => 'Looses',
             'sortable' => false,
             'sortDirection' => 'asc',
             'dataNumric' => true,
             'truncateStrings' => '0',
             'className' => '',
           ];
            $columns[] = [
             'label' => 'Void',
             'property' => 'Void',
             'sortable' => false,
             'sortDirection' => 'asc',
             'dataNumric' => true,
             'truncateStrings' => '0',
             'className' => '',
           ];
         }
         return $columns;
       }
       add_filter( 'cdbt_shortcode_custom_columns', 'wp_tips_shortcode_custom_columns', 10, 3 );
       ```
   
 * Thank you,

Viewing 15 replies - 1 through 15 (of 20 total)

1 [2](https://wordpress.org/support/topic/sql-queries-countid/page/2/?output_format=md)
[→](https://wordpress.org/support/topic/sql-queries-countid/page/2/?output_format=md)

The topic ‘sql queries count(id)’ is closed to new replies.

 * ![](https://s.w.org/plugins/geopattern-icon/custom-database-tables_e4e2e2.svg)
 * [Custom DataBase Tables](https://wordpress.org/plugins/custom-database-tables/)
 * [Frequently Asked Questions](https://wordpress.org/plugins/custom-database-tables/#faq)
 * [Support Threads](https://wordpress.org/support/plugin/custom-database-tables/)
 * [Active Topics](https://wordpress.org/support/plugin/custom-database-tables/active/)
 * [Unresolved Topics](https://wordpress.org/support/plugin/custom-database-tables/unresolved/)
 * [Reviews](https://wordpress.org/support/plugin/custom-database-tables/reviews/)

 * 20 replies
 * 2 participants
 * Last reply from: [ka2](https://wordpress.org/support/users/ka2/)
 * Last activity: [9 years, 11 months ago](https://wordpress.org/support/topic/sql-queries-countid/page/2/#post-7278630)
 * Status: resolved