Hi I’m having an issue with an sql table. There are 7 records of a total of 7 records showing (unfiltered). But the table info shows 1 to 7 of 7 entries (filtered from a 5 total entries).
First query shows 5 entries (out of 5 entries). The third query shows filtered table "iqd_pswfund_products", where "active" column = 1. So, if I SELECT * FROM iqd_pswfund_products, I will see 23 entries. The second query is the combination of two tables, where "iqd_pswfund_trainers" has 7 entries. That's why the table info says "Showing 1 to 7 of 7 entries (filtered from 5 total entries)". So, 7 entries from table iqd_pswfund_trainers are included in "iqd_pswfund_homes" table (which has 5 entries) through a join. Since only one table can be seen as the source table (while the other one is joined), the source table is seen as the total number (5), when in reality, after joining, there are 7 entries.
Thanks for the reply. This doesn't really help me. If I'm loading 5 records from the sql query it should show a total of 5. If I then filter using the table filters, I would understand it saying x records filtered from 5. Regardless of how many records are in my db, it should be basing the total number on my sql query setup in the table settings.
Your query CONCATS "admin.php?page=pswfund&home-id=" with column "organization_id" from database table "iqd_pswfund_homes"; separates it with pipes ( || ), and appends column "organization_name" from table "iqd_pswfund_homes" AS "organization_name_url". Table "iqd_pswfund_homes" has 5 entries, where Organization IDs are 1026, 1027, 1028, 1029 and 1030. You join the iqd_pswfund_homes table ON that Organization ID, and there are 7 entries related - 3 are referring to 1028, one to 1029 and 3 to 1030. So, you are joining a table that has 7 entries, but all those 7 entries are related to the 3 entries from the original table. If you don't want to see the table info, you can disable it in the Display tab above the table, but the query returns exactly what you are telling it to return.
Do let us know if there is anything else we can assist you with.
Hi I’m having an issue with an sql table. There are 7 records of a total of 7 records showing (unfiltered). But the table info shows 1 to 7 of 7 entries (filtered from a 5 total entries).
Hi Greg
Thank you for reaching out to us.
Could you please send us the query so we can try and replicate the issue on our end?
Hi Greg
Tank you.
First query shows 5 entries (out of 5 entries). The third query shows filtered table "iqd_pswfund_products", where "active" column = 1. So, if I SELECT * FROM iqd_pswfund_products, I will see 23 entries. The second query is the combination of two tables, where "iqd_pswfund_trainers" has 7 entries. That's why the table info says "Showing 1 to 7 of 7 entries (filtered from 5 total entries)". So, 7 entries from table iqd_pswfund_trainers are included in "iqd_pswfund_homes" table (which has 5 entries) through a join. Since only one table can be seen as the source table (while the other one is joined), the source table is seen as the total number (5), when in reality, after joining, there are 7 entries.
Thanks for the reply. This doesn't really help me. If I'm loading 5 records from the sql query it should show a total of 5. If I then filter using the table filters, I would understand it saying x records filtered from 5. Regardless of how many records are in my db, it should be basing the total number on my sql query setup in the table settings.
Greg
Hi Greg
Your query CONCATS "admin.php?page=pswfund&home-id=" with column "organization_id" from database table "iqd_pswfund_homes"; separates it with pipes ( || ), and appends column "organization_name" from table "iqd_pswfund_homes" AS "organization_name_url". Table "iqd_pswfund_homes" has 5 entries, where Organization IDs are 1026, 1027, 1028, 1029 and 1030. You join the iqd_pswfund_homes table ON that Organization ID, and there are 7 entries related - 3 are referring to 1028, one to 1029 and 3 to 1030. So, you are joining a table that has 7 entries, but all those 7 entries are related to the 3 entries from the original table. If you don't want to see the table info, you can disable it in the Display tab above the table, but the query returns exactly what you are telling it to return.
Do let us know if there is anything else we can assist you with.
Do you have a hook available so I can correct this data before it is displayed.
For future reference, in case anyone else is struggling with this issue my workaround is below.
function reset_total_records( $json, $id, $get ){
global $wpdb, $wdtVar1, $wdtVar2, $wdtVar3;
$table_name = $wpdb->prefix . 'wpdatatables';
$query = $wpdb->get_var("SELECT content FROM $table_name WHERE id=$id");
$wdt_var1 = isset($_GET['wdt_var1']) ? $_GET['wdt_var1'] : '';
$query = str_replace('%VAR1%', $wdtVar1, $query);
$wdt_var2 = isset($_GET['wdt_var2']) ? $_GET['wdt_var2'] : '';
$query = str_replace('%VAR2%', $wdtVar2, $query);
$wdt_var3 = isset($_GET['wdt_var3']) ? $_GET['wdt_var3'] : '';
$query = str_replace('%VAR3%', $wdtVar3, $query);
$query = str_replace('DELETE', '', $query);
$query = str_replace('DROP', '', $query);
$query = str_replace('INSERT', '', $query);
$query = stripslashes($query);
$wpdb->get_results($query);
$data = json_decode($json, true);
$data['recordsTotal'] = $wpdb->num_rows;
$json = json_encode($data);
return $json;
}
add_filter('wpdatatables_filter_server_side_data', 'reset_total_records', 3, 10);
Hi Greg
Thank you for sharing this with us.
I will forward it to our developers to see if there is anything else that can be done.