Hi,

I've trying to build some mySQL tables / reports in our Wordpress site, and I want to give leaders the ability to do a quick search on their team members.... however, the managers need to be able to click on one of the staff members in the first table (using a link or other reference), then opening a more detailed / second mySQL table.

So 1st table has list of staff with HTTP / Hyperlink to their own "user_id" table field, which opens a 2nd table using the "user_id" field from 1st table to create a new mySQL query just for that individual user.

I've gone through the documentation but can't see where I can set a unique key, to pass to another mySQL query.

We can do this in PHP, but would prefer to use wpDataTables.

Kind regards,
Miles 
Australia

I've done a little bit more work on this with good results.

If you build the SQL query with "select concat" commands, you can pass HTML code and SQL results into your output table, then it gets rendered in the web browser, so you can follow a working link.

This SQL code works fine in wpDataTable, and creates a HREF link with the "user.ID" value against each user:

[wpdatatable id=1]
SELECT
  t1.id, t1.user_email AS 'Email Address',
  MAX(CASE WHEN t2.meta_key = 'first_name' THEN meta_value END) AS 'First Name',
  MAX(CASE WHEN t2.meta_key = 'last_name' THEN meta_value END) AS 'Last Name',
  (SELECT CONCAT('<a href="https://example.com/user_id=', t1.id, '">Review ',t1.id,'</a>')) AS "Review Users"
FROM wp_users AS t1 
INNER JOIN wp_usermeta AS t2 ON t1.id = t2.user_id
GROUP BY t1.id, t1.user_email

If I craft the HTML code and SQL values to go to a page which has another wpDataTable, then I can run [wpdatatable id=2] using the output values from [wpdatatable id=1] passed on URL string, or a preset variable, which ever is easiest to implement. You could pass as many KV pairs as you wanted, you just need to know how to add them into next SQL query (if possible).

[wpdatatable id=2]
SELECT *
FROM wp_usermeta
WHERE user_id = %WPDB-URL1% AND something_else = %WPDB-URL2%

Do you know if the variable or URL strings will work across pages / tables?

As far as output into Excel, users could just hide or delete that column from export, or leave it there and still be able to click on it from an Excel spreadsheet after its extracted.

UPDATE: I've just tested an Excel export and only the values are exported, not the HTML code injected in the SQL search, so you could use the "SELECT CONCAT" on their username, or some other column in the table, if you don't need redundant data such as "Review User 1, 2, 3" etc...

Cheers,
Miles

Hi Bogdan,

We've got everything set up as best we can, with working embedded links from [wpdatatable id=1] to [wpdatatable id=2] using the code below (on different WP pages), we just need a mechanism to pass a unique key between tables, so we can use that key in the second mySQL query to build table 2.

Are the developers able to review this and see if there's currently a way to to pass variables between queries / tables, or build it into a future update. It would make the plugin so much more powerful if users could click values in one table, and be redirected to a second table.

I would prefer to use this option than going back to PHP coding.

Regards,
Miles

Thanks for the heads-up Bogdan,

Please let me know if you need any testers, we'd be happy to look at this with you.

Regards,
Miles