Your wonderful plugin has saved me so much time already! I'm working with a pretty unwieldy set of database tables, and so the query I want to use is a bit too clunky/complex for what I can build with placeholders.
It looks like the filter wpdatatables_filter_mysql_query( $query, $tableId ) is what I need, but while I'm fairly comfortable with PHP, I'm not that across WordPress filters and I keep hitting dead ends trying to get this working.
Basically all I'm wanting to do is add a few WHERE clauses to my query. Is the filter the right way to do this?
Is there any chance of a little code example for the filter to help me out, please? I couldn't find any code examples in the documentation and what I've cobbled together isn't working. (I won't post my filter attempts here yet because a) it's wrong, haha, and b) this is a public ticket.)
If it's possible to help me with a few lines that would help me add some WHERE clauses to the table with ID of "3", I'd be really grateful.
I'm not sure I understand what you wish to do with that filter, but you could add several WHERE clauses to the query itself. If it works in PHPMyAdmin, it should work in wpDataTables as well.
However, more complex the query is, more chance that editing/filtering/sorting and search may not work for the table.
If that happens, it's probably happening because wpDataTables server has problems with parsing of the query and building new queries dynamically (rarely happens, but does sometimes). To avoid this please prepare a MySQL view (a stored query), which will return the data that you need, call it e.g. “view1” and then build a wpDataTabled based on a simple query like “SELECT * FROM view1″.
Please note some this when working with the server-side processing feature:
Please do not use “LIMIT” in the SELECT statement. wpDataTables adds it automatically and it will be overridden.
Please do not use “ORDER BY” in the SELECT statement. wpDataTables has its own sorting engine so it makes no sense to use MySQL’s sorting, since it will be overridden. Also, server-side processing feature adds this part of statement automatically when users trigger the sorting on the front-end, and having it in initial statement may cause the table to crash.
If you still need the help with the filter, please explain in a bit more detail what your idea is with it, and I'll see if there's an example I can share with you.
Sorry for not being clear - basically the query involves an IN statement and a PHP array and it's all just a bit too messy to include neatly in a variable/shortcode solution, and as it's dynamic I can't hard-code the query either. The table is going to be pulling results that match a series of book ISBNs, but the ISBNs themselves are across a few columns, they're entered differently - basically the table is a massive headache, haha! But I can't change the way it's set up.
So the query itself is okay, it's just that I just can't get even a simple filter working for wpdatatables_filter_mysql_query. I really just need a little code example for actually applying the filter, if possible, because I can't find any workable code examples. So it's less about the query itself and more about actually using this filter and getting it functioning. E.g. something I can just paste into functions.php, test it, and then modify it for my table and my query.
Thanks so much for your help, I really appreciate it!
You're a gem, Aleksandar! That was exactly what I needed. I really appreciate your help. I don't know how to close the ticket so please feel free to close this now, and thanks again.
Hi there,
Your wonderful plugin has saved me so much time already! I'm working with a pretty unwieldy set of database tables, and so the query I want to use is a bit too clunky/complex for what I can build with placeholders.
It looks like the filter wpdatatables_filter_mysql_query( $query, $tableId ) is what I need, but while I'm fairly comfortable with PHP, I'm not that across WordPress filters and I keep hitting dead ends trying to get this working.
Basically all I'm wanting to do is add a few WHERE clauses to my query. Is the filter the right way to do this?
Is there any chance of a little code example for the filter to help me out, please? I couldn't find any code examples in the documentation and what I've cobbled together isn't working. (I won't post my filter attempts here yet because a) it's wrong, haha, and b) this is a public ticket.)
If it's possible to help me with a few lines that would help me add some WHERE clauses to the table with ID of "3", I'd be really grateful.
Thanks for your help!
Hello Ally
Thank you for your purchase.
I'm not sure I understand what you wish to do with that filter, but you could add several WHERE clauses to the query itself. If it works in PHPMyAdmin, it should work in wpDataTables as well.
However, more complex the query is, more chance that editing/filtering/sorting and search may not work for the table.
If that happens, it's probably happening because wpDataTables server has problems with parsing of the query and building new queries dynamically (rarely happens, but does sometimes). To avoid this please prepare a MySQL view (a stored query), which will return the data that you need, call it e.g. “view1” and then build a wpDataTabled based on a simple query like “SELECT * FROM view1″.
Please note some this when working with the server-side processing feature:
If you still need the help with the filter, please explain in a bit more detail what your idea is with it, and I'll see if there's an example I can share with you.
Kind Regards,
Aleksandar Vuković
[email protected]
Rate my support
wpDataTables: FAQ | Facebook | Twitter | Instagram | Front-end and back-end demo | Docs
Amelia: FAQ | Facebook | Twitter | Instagram | Amelia demo sites | Docs | Discord Community
You can try wpDataTables add-ons before purchasing on these sandbox sites:
Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables
Hi Aleksandar,
Sorry for not being clear - basically the query involves an IN statement and a PHP array and it's all just a bit too messy to include neatly in a variable/shortcode solution, and as it's dynamic I can't hard-code the query either. The table is going to be pulling results that match a series of book ISBNs, but the ISBNs themselves are across a few columns, they're entered differently - basically the table is a massive headache, haha! But I can't change the way it's set up.
So the query itself is okay, it's just that I just can't get even a simple filter working for wpdatatables_filter_mysql_query. I really just need a little code example for actually applying the filter, if possible, because I can't find any workable code examples. So it's less about the query itself and more about actually using this filter and getting it functioning. E.g. something I can just paste into functions.php, test it, and then modify it for my table and my query.
Thanks so much for your help, I really appreciate it!
Ally
Hello Ally
Sure, here's an example for you, but you need to replace it with values from your tables if you have an SQL query based table:
You can use the hook to display only certain columns, like this:
function updateQuery( $query,$id ) { $query = "SELECT wp_wpdatatable_1.`imagesss`, wp_wpdatatable_1.`emailtest`, wp_wpdatatable_1.`urltest` FROM wp_wpdatatable_1"; return $query;I hope that helps.}
add_filter('wpdatatables_filter_mysql_query', 'updateQuery',10,2);
Kind Regards,
Aleksandar Vuković
[email protected]
Rate my support
wpDataTables: FAQ | Facebook | Twitter | Instagram | Front-end and back-end demo | Docs
Amelia: FAQ | Facebook | Twitter | Instagram | Amelia demo sites | Docs | Discord Community
You can try wpDataTables add-ons before purchasing on these sandbox sites:
Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables
You're a gem, Aleksandar! That was exactly what I needed. I really appreciate your help. I don't know how to close the ticket so please feel free to close this now, and thanks again.
You're welcome, Ally
Glad I could help.
If you have any further questions or issues, please feel free to open a new ticket, and we'll gladly help.
Kind Regards,
Aleksandar Vuković
[email protected]
Rate my support
wpDataTables: FAQ | Facebook | Twitter | Instagram | Front-end and back-end demo | Docs
Amelia: FAQ | Facebook | Twitter | Instagram | Amelia demo sites | Docs | Discord Community
You can try wpDataTables add-ons before purchasing on these sandbox sites:
Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables