"use table wp_users as foreign key" (#1343552) / TMS-Plugins

Comments

5
Jottes started the conversation
October 23, 2017 at 7:39am
Hi,
I am trying to use the table "wp_users" as a foreign key to display user names on a custom table. However, all I get is the user ID number values.
I know how to do it with other tables but wp_users is not working. See column settings attached. Is the table protected?
The userid column on my custom table is defined as int(11). I am logged in as admin. If I display the users table using shortcode, everything is displayed nicely.
Thanks for your help.
Johannes
1,708
Miloš replied
October 23, 2017 at 12:06pm
Hi Johannes,
Thank you for your purchase.

The foreign key is the newest feature that came out with this version of plugin and there are some little bugs which will be fixed for the upcoming version.
Can you please tell me does the column name is the same like the display name for the column that you want to use as a foreign key.
Best regards.
Kind Regards,

Miloš Jovanović
[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
5
Jottes replied
October 23, 2017 at 12:17pm
I am trying to display the column name 'display_name' or 'user_email' (tried both). I used the column 'ID' as primary key for users and have a table called 'dienst' which has the column 'userid' defined as a foreign key.
The query looks something like this:
SELECT * FROM wp_users, dienst WHERE wp_users.ID = dienst.userid;
1,708
Miloš replied
October 23, 2017 at 2:09pm
Hi Johannes
Can you please go to this file:

File ../wp-content/plugins/wpdatatables/assets/js/wpdatatables/admin/table-settings/main.js and around line 854 find this line of code

displayColumnName: $('#wdt-foreign-column-display-value option:selected').html(),

and replace it with this one

displayColumnName: $('#wdt-foreign-column-display-value option:selected').data('orignal_header'),

Also in file wp-content/plugins/wpdatatables/assets/js/wpdatatables/admin/table-settings/table_config_object.js around line 635 find this code

var option_str = '<option value="'+columns[i].id+'">'+columns[i].display_header+'</option>';

and replace it with this one

var option_str = '<option value="'+columns[i].id+'" data-orignal_header="'+columns[i].orig_header+'">'+columns[i].display_header+'</option>';
Let me know the result.
Best regards.
Kind Regards,

Miloš Jovanović
[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
5
Jottes replied
October 23, 2017 at 2:20pm
I tried it but I still get the same result (see attachment).
It has been working well with other columns in the same table also before I changed the files.
1,708
Miloš replied
October 23, 2017 at 3:29pm
HI Johannes,
Please provide me a temporary WP-admin login for your site where this happens, so we could log in and take a look ‘from the inside’ as that’s the most efficient way to see and resolve the issue. We do not interfere with any data or anything else except for the plugin (in case that’s a production version of the site), and of course we do not provide login data to third party. You can write
entials here just check Private Reply so nobody can see them except us.
Also please give me the exact table ID and column names from which you want to make this relation so I can try to reproduce the issue.
Best regards.
Kind Regards,

Miloš Jovanović
[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
Jottes replied privately
Jottes replied privately
Jottes replied privately
1,708
Miloš replied
October 23, 2017 at 4:07pm
Hi Johannes,
Unfortunately Teamviewer or Skype calls are not included in regular support.
Support with such requests can be provided on a paid basis.
Can you please send me the two mysql files so I can try to reproduce the issue on my side.
Best regards.
Kind Regards,

Miloš Jovanović
[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

Jottes replied

October 23, 2017 at 6:29pm

Oh, that's a pity.

What mysql files do you mean?

Here's how I create the table:

CREATE TABLE `dienst` (
`dienstid` int(11) NOT NULL AUTO_INCREMENT, 
`userid` int(11) NOT NULL, 
`datum` date NOT NULL, 
`dauer` FLOAT(16,4) NOT NULL, 
`kategorieid` int(11) NOT NULL, 
`bereichid` int(11) NOT NULL, 
`fachbereichid` int(11) DEFAULT NULL, 
`taetigkeit` varchar(100) COLLATE latin1_german1_ci NOT NULL, 
`status` tinyint(1) NOT NULL DEFAULT '0', 
PRIMARY KEY (`dienstid`), 
UNIQUE KEY `dienstid` (`dienstid`), 
KEY `dienstid_2` (`dienstid`)) 
ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;

1,708
Miloš replied
October 24, 2017 at 9:34am
Hi Johannes,
I meant that if you can zip that table and send me as and sql file so I can just imported to my database and create it on my side.

Best regards.
Kind Regards,

Miloš Jovanović
[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
Jottes replied privately
5
Jottes replied
October 25, 2017 at 10:31am
at the moment, I have an empty dropdown list for users when I edit/create a data set (see attachment)
Jottes replied privately
5
Jottes replied
October 26, 2017 at 9:33am
I tried to create another dropdown field called 'Dauer' in table 'DiensteEintragen' with values from a custom created (using wpDataTable) table. I also do not see any values, the dropdown is simply empty.
Tried changing back what you suggested earlier (main.js and table_config_object.js), still the same. Some single value selectboxes are working, some aren't.
1,708
Miloš replied
October 26, 2017 at 12:43pm
Hi Johannes,
Please provide me a temporary WP-admin login for your site where this happens, so we could log in and take a look ‘from the inside’ as that’s the most efficient way to see and resolve the issue. We do not interfere with any data or anything else except for the plugin (in case that’s a production version of the site), and of course we do not provide login data to third party. You can write credentials here just check Private Reply so nobody can see them except us.
Also point me to the table ID and column names so I can take a look.
Kind Regards,

Miloš Jovanović
[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
Jottes replied privately
1,708
Miloš replied
October 27, 2017 at 12:50pm
Hi Johannes,
Ok now I can see it.
It looks like there is some issues with the MySQL tables and foreign keys and I forwarded it to my team so we can test it out further.
In meantime can you please try to create a table like from our documentation so we can be sure that the foreign keys are working on your side.
Best regards.
Kind Regards,

Miloš Jovanović
[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
Jottes replied privately
Jottes replied privately
5
Jottes replied
October 28, 2017 at 9:51am
Please escalate this issue to a higher priority because our web project will be delayed if this isn't resolved soon!
Thank you
1,708
Miloš replied
October 30, 2017 at 3:58pm
Hi Johannes,
Can you please tell me which column type is set for that columns (Dauer) in the database.
Best regards.
Kind Regards,

Miloš Jovanović
[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
5
Jottes replied
October 31, 2017 at 7:42pm
"Dauer" is set to float(16,4) because that's what's recommended by your documentation (but I thought about changing it to float(6,2))
5
Jottes replied
November 1, 2017 at 3:45pm
Please escalate this issue to a higher priority because our web project will be delayed if this isn't resolved very soon!

Thank you
1,708
Miloš replied
November 1, 2017 at 4:18pm
Hi Johannes,
When using foreign keys it is recommended to use integer instead of float. You can see that on our video documentation here.
Can you please try to change it to be integer first before you set it to be string and tell me do you still have the same issue.
Best regards.
Kind Regards,

Miloš Jovanović
[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
5
Jottes replied
November 1, 2017 at 4:26pm
Hi Bogdan,
"Dauer" is set to be float(16,4) in mysql and has to stay this way because my customer needs to enter decimal places.
However, this column isn't a problem. I just noticed the strange behavior and wanted to let you know because this might happen to other customers as well. Let's not worry about this for now.
The problem is that I cannot display and edit user names as foreign keys in my table (see below).
5
Jottes replied
November 6, 2017 at 12:19pm
Hi Bogdan,
any update on this issue? This bug is of very high priority to me.
1,708
Miloš replied
November 7, 2017 at 10:31am
Hi Johannes,
Can you just tell me do you want to just display that users or to be able to edit them also using foreign key.
Best regards.
Kind Regards,

Miloš Jovanović
[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
5
Jottes replied
November 7, 2017 at 12:22pm
Hi Bogdan,
I'd like to edit them as well (add/edit records and assign them to a certain user, for example).
Johannes
1,708
Miloš replied
November 9, 2017 at 2:37pm
Hi Johannes,
Can you just tell me the ID of the table where you have users and the ID of the table from which you are displaying user's name as a foreign keys.
Best regards.
Kind Regards,

Miloš Jovanović
[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
5
Jottes replied
November 9, 2017 at 2:54pm
Hi Bogdan,
The problem exists on several tables. However, you may want to use table 14 because it's not being used in the front end. Reference table is 11 (users table).
Please note that this is production environment. Do not interfere with any data. Thank you.
Jottes
1,708
Miloš replied
November 9, 2017 at 3:43pm
HI Johannes,
The Deuer is set to float as I can see from your post and as I can see when I create a table.
Because of that the values can not be return and the proper name of that user is displaying like numbers.

So foreign keys can not be made it if the column type is float.
Best regards.
Kind Regards,

Miloš Jovanović
[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
5
Jottes replied
November 9, 2017 at 10:03pm
Hi Bogdan,
as I said before, the problem is that the users aren't being displayed (column "Benutzer").
I have already found another solution on the column "Dauer".
Regards
1,708
Miloš replied
November 10, 2017 at 12:56pm
Hi Johannes,

Can you please check now.

I found the issue.

The query for table where are you pulling the values from was like this

select user_email, ID, display_name
from wp_users
where user_status = 0;

You will notice the " ; " sign at the end of the last line

Just remove that sign and you will get the result like I have in wpdatatables support table ID 14 that I created on your site. ( attachment)

Best regards.
Kind Regards,

Miloš Jovanović
[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

1 Like
5
Jottes replied
November 10, 2017 at 5:07pm
Hi Bogdan,
yes, this worked. Thank you, that's good to know.
Could you please remove the attachment from your last post? The information is not to be viewed by everyone. Thank you.
Johannes
1,708
Miloš replied
November 13, 2017 at 9:47am
Hi Johannes,
You are welcome.
Of course, attachment removed.
Best regards.
Kind Regards,

Miloš Jovanović
[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