sql queries count(id)

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,

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

*UPDATE
i placed a semicolon here

$_overwrite_sql = <<< SQL;

but i have the same error

now i have this error to add_filter();

sir can i add you on skype? or can you give me your email?

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,

My shortcode

My theme`s fuctions.php

Test Page
No data in this table.

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.

Here is my new code

display table in my test page

my backoffice table in wordpress w/o code in fuctions.php
my backoffice table in wordpress after code

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.

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,

you were right ??
Talbe with bets

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
Before

can you understand what I mean?

Thank you!

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,

i did it but still i cant display my |date,time,league,match,tip,odds,book,win_loss| ??
check my test page

Hmm…
Please show me source code that you have added in the “functions.php”.

Thank you for taking care of it.

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;

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,