I just have bought the plugin and I want to get a table using the wordpress table function. The data are stored in custom post types via ACF (advanced custom fields Plugin). I think that's not important for my question but for understand.
I have a list of printers with technical data and I want to display the printers in one column per printer. Actually it will show each technical data in one column.
SELECT drucker_meta_hersteller_tbl.meta_value AS drucker_meta_hersteller,
drucker_meta_modell_tbl.meta_value AS drucker_meta_modell,
drucker_meta_preis_tbl.meta_value AS drucker_meta_preis,
posts_drucker.ID AS drucker_ID
FROM wp_posts AS posts_drucker
INNER JOIN (SELECT drucker_meta_hersteller_tbl_posts.ID as id, meta_value, meta_key FROM wp_postmeta AS drucker_meta_hersteller_tbl_postmeta INNER JOIN wp_posts AS drucker_meta_hersteller_tbl_posts ON drucker_meta_hersteller_tbl_postmeta.post_id = drucker_meta_hersteller_tbl_posts.ID AND drucker_meta_hersteller_tbl_posts.post_type = 'drucker') AS drucker_meta_hersteller_tbl
ON drucker_meta_hersteller_tbl.meta_key = 'hersteller' AND drucker_meta_hersteller_tbl.id = posts_drucker.ID
INNER JOIN (SELECT drucker_meta_modell_tbl_posts.ID as id, meta_value, meta_key FROM wp_postmeta AS drucker_meta_modell_tbl_postmeta INNER JOIN wp_posts AS drucker_meta_modell_tbl_posts ON drucker_meta_modell_tbl_postmeta.post_id = drucker_meta_modell_tbl_posts.ID AND drucker_meta_modell_tbl_posts.post_type = 'drucker') AS drucker_meta_modell_tbl
ON drucker_meta_modell_tbl.meta_key = 'modell' AND drucker_meta_modell_tbl.id = posts_drucker.ID
INNER JOIN (SELECT drucker_meta_preis_tbl_posts.ID as id, meta_value, meta_key FROM wp_postmeta AS drucker_meta_preis_tbl_postmeta INNER JOIN wp_posts AS drucker_meta_preis_tbl_posts ON drucker_meta_preis_tbl_postmeta.post_id = drucker_meta_preis_tbl_posts.ID AND drucker_meta_preis_tbl_posts.post_type = 'drucker') AS drucker_meta_preis_tbl
ON drucker_meta_preis_tbl.meta_key = 'preis' AND drucker_meta_preis_tbl.id = posts_drucker.ID
WHERE 1=1
AND posts_drucker.post_type = 'drucker'
I am sorry to disappoint you, but unfortunately something like this is not possible with the plugin's built-in features.
You would need to create a Pivot table in the database; then prepare a MySQL view (a stored query), which will return the data from that Pivot table, call it e.g. “view1” and then build a wpDataTable based on a simple query like “SELECT * FROM view1″.
I hope this helps, do let us know if you need any further assistance.
Of course, we are sorry to hear that, but we completely understand.
Please log into http://store.tms-plugins.com/ with the credentials you received in your e-mail when you purchased the plugin, then navigate to Purchases/wpDataTables, and if you're still within 15 days from purchase you will see the "Request Refund" button.
If you don't see it, and you're using a screen with a width lower than 1600px, you will need to scroll through that screen horizontally to see the button.
After you submit a refund request, you will not receive any confirmation emails, but you will see that the button is grayed out:
That means that the refund request is successfully submitted and that you will receive your refund in 5 - 7 business days.
SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(`subject` = "', `subject`,'", `grade`,"")) AS "',`subject`,'"') ) INTO @sql
FROM table1;
SET @sql = CONCAT('SELECT `regno`, ', @sql, ' FROM table1 s GROUP BY s.`regno` ORDER BY s.`regno`');
#SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE stmt;
Hello,
I just have bought the plugin and I want to get a table using the wordpress table function. The data are stored in custom post types via ACF (advanced custom fields Plugin). I think that's not important for my question but for understand.
I have a list of printers with technical data and I want to display the printers in one column per printer. Actually it will show each technical data in one column.
I will explain it in a screenshot (see attachment here: https://ibb.co/BnWKgP5).
And the generated MySQL Code:
SELECT drucker_meta_hersteller_tbl.meta_value AS drucker_meta_hersteller, drucker_meta_modell_tbl.meta_value AS drucker_meta_modell, drucker_meta_preis_tbl.meta_value AS drucker_meta_preis, posts_drucker.ID AS drucker_ID FROM wp_posts AS posts_drucker INNER JOIN (SELECT drucker_meta_hersteller_tbl_posts.ID as id, meta_value, meta_key FROM wp_postmeta AS drucker_meta_hersteller_tbl_postmeta INNER JOIN wp_posts AS drucker_meta_hersteller_tbl_posts ON drucker_meta_hersteller_tbl_postmeta.post_id = drucker_meta_hersteller_tbl_posts.ID AND drucker_meta_hersteller_tbl_posts.post_type = 'drucker') AS drucker_meta_hersteller_tbl ON drucker_meta_hersteller_tbl.meta_key = 'hersteller' AND drucker_meta_hersteller_tbl.id = posts_drucker.ID INNER JOIN (SELECT drucker_meta_modell_tbl_posts.ID as id, meta_value, meta_key FROM wp_postmeta AS drucker_meta_modell_tbl_postmeta INNER JOIN wp_posts AS drucker_meta_modell_tbl_posts ON drucker_meta_modell_tbl_postmeta.post_id = drucker_meta_modell_tbl_posts.ID AND drucker_meta_modell_tbl_posts.post_type = 'drucker') AS drucker_meta_modell_tbl ON drucker_meta_modell_tbl.meta_key = 'modell' AND drucker_meta_modell_tbl.id = posts_drucker.ID INNER JOIN (SELECT drucker_meta_preis_tbl_posts.ID as id, meta_value, meta_key FROM wp_postmeta AS drucker_meta_preis_tbl_postmeta INNER JOIN wp_posts AS drucker_meta_preis_tbl_posts ON drucker_meta_preis_tbl_postmeta.post_id = drucker_meta_preis_tbl_posts.ID AND drucker_meta_preis_tbl_posts.post_type = 'drucker') AS drucker_meta_preis_tbl ON drucker_meta_preis_tbl.meta_key = 'preis' AND drucker_meta_preis_tbl.id = posts_drucker.ID WHERE 1=1 AND posts_drucker.post_type = 'drucker'I hope you can help.
Best regards.
Hi Denis
I am sorry to disappoint you, but unfortunately something like this is not possible with the plugin's built-in features.
You would need to create a Pivot table in the database; then prepare a MySQL view (a stored query), which will return the data from that Pivot table, call it e.g. “view1” and then build a wpDataTable based on a simple query like “SELECT * FROM view1″.
I hope this helps, do let us know if you need any further assistance.Hi Denis
Of course, we are sorry to hear that, but we completely understand.
Please log into http://store.tms-plugins.com/ with the credentials you received in your e-mail when you purchased the plugin, then navigate to Purchases/wpDataTables, and if you're still within 15 days from purchase you will see the "Request Refund" button.
If you don't see it, and you're using a screen with a width lower than 1600px, you will need to scroll through that screen horizontally to see the button.
After you submit a refund request, you will not receive any confirmation emails, but you will see that the button is grayed out:
That means that the refund request is successfully submitted and that you will receive your refund in 5 - 7 business days.
Here is a good example to change rows and columns in sql, maybe this could help you!
CREATE TABLE table1 ( `regno` VARCHAR(6), `subject` VARCHAR(16), `grade` VARCHAR(2) ); INSERT INTO table1 (`regno`, `subject`, `grade`) VALUES ('7007gd', 'mathematics', 'C6'), ('7007gd', 'english language', 'CS'), ('7007gd', 'economics', 'C5'), ('7007gd', 'government', '83'), ('7007gd', 'biology', 'C6'), ('7010gd', 'mathematics', 'C6'), ('7010gd', 'english language', 'C5'), ('7010gd', 'economics', 'C5'), ('7010gd', 'government', 'C5'), ('7010gd', 'biology', 'C5'), ('70151f', 'mathematics', 'C6'), ('70151f', 'english language', 'C5'), ('70151f', 'economics', 'C6'), ('70151f', 'government', 'C5'), ('70151f', 'biology', 'C5'), ('70151f', 'chemistry', 'C5'), ('70151f', 'physics', '83');SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(`subject` = "', `subject`,'", `grade`,"")) AS "',`subject`,'"') ) INTO @sql FROM table1; SET @sql = CONCAT('SELECT `regno`, ', @sql, ' FROM table1 s GROUP BY s.`regno` ORDER BY s.`regno`'); #SELECT @sql; PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE stmt;You have to play with it to meet your own needs!
Tim
Hi Tim
Thank you for sharing this I am sure it will come in handy!
Do let us know if there is anything we can assist you with.
Have a wonderful day