First, thanks for an amazing product - I set up in 2 hours what took me 2 months in Perl previously!
I've been trying to get the foreign key function working. We have a large database with several foreign keys - while I can get everything to work via a long select statement:
SELECT CONCAT('http://noahsarkive.cldavis.org/imgs/NA_Images/Thumbnail/', image_info.image_name, '.jpg||http://noahsarkive.cldavis.org/imgs/NA_Images/', image_info.image_name, '.jpg') as image, image_info.`IMAGE_NAME`, image_info.`contrib_id`, inst.`INST_NAME`, species.`SPECIES_NAME`, systems.`SYSTEM_NAME`, organ.`ORGAN_NAME`, genpath.`GENPATH_NAME`, image_info.`diagnosis`,image_info.`IMAGE_KEYWORD` FROM image_info AS image_info, inst AS inst, species AS species, systems AS systems, organ AS organ, genpath AS genpath WHERE `image_info`.`INST_ID` = `inst`.`INST_ID` AND `image_info`.`SPECIES_ID` = `species`.`SPECIES_ID` AND `image_info`.`SYSTEM_ID` = `systems`.`SYSTEM_ID` AND `image_info`.`ORGAN_ID` = organ.`ORGAN_ID` AND `image_info`.`GENPATH_ID` = `genpath`.`genpath_id` AND `image_info`.`IMAGE_ACTIVE` = 1 ORDER BY IMAGE_NAME;
I then can't filter properly on any of the foreign key items. I've tried setting up a table that is just:
select * from contrib;
Where contrib has 2 columns; one is contrib_id and the other is contrib_name. Then I alter the SELECT statement for the main table to display image_info.contrib_id, and set up the relationship in the column setup. However, it still only shows the numeric ID rather than the name. (images attached)
Is this database that you're using maybe connected as a separate database? If that's the case, it's a known issue, and our developers will work on it. Otherwise, I have no explanation as to why it's not working.
If it's not a separate DB connection, can you please provide me a temporary wp-admin (administrator) user, so I can log in, and check it out. This is a public ticket, so make sure to leave your credentials in a PRIVATE response.
Unfortunately, no. The issue is related to this being a separate DB, instead of WordPress database, regardless of the number of separate databases. The only thing I can recommend is importing the table in WP database, and using the WP Connection for it.
Our developers will work on resolving the issue in the future, but I can't say exactly when it will be resolved.
I've tried importing all the tables into the Wordpress database, turned off separate database connections, and still have the same results. I'll add a user and send the info in a message. Thanks!
All your queries had a semi-colon at the end, and the PHP SQL parser we use adds those automatically, so when you add them, they are added twice, actually, and that's what was causing the issue here.
I removed the semi-colons, and the "Contributor" column in table #11 now pulls the name correctly:
First, thanks for an amazing product - I set up in 2 hours what took me 2 months in Perl previously!
I've been trying to get the foreign key function working. We have a large database with several foreign keys - while I can get everything to work via a long select statement:
SELECT CONCAT('http://noahsarkive.cldavis.org/imgs/NA_Images/Thumbnail/', image_info.image_name, '.jpg||http://noahsarkive.cldavis.org/imgs/NA_Images/', image_info.image_name, '.jpg') as image, image_info.`IMAGE_NAME`, image_info.`contrib_id`, inst.`INST_NAME`, species.`SPECIES_NAME`, systems.`SYSTEM_NAME`, organ.`ORGAN_NAME`, genpath.`GENPATH_NAME`, image_info.`diagnosis`,image_info.`IMAGE_KEYWORD` FROM image_info AS image_info, inst AS inst, species AS species, systems AS systems, organ AS organ, genpath AS genpath WHERE `image_info`.`INST_ID` = `inst`.`INST_ID` AND `image_info`.`SPECIES_ID` = `species`.`SPECIES_ID` AND `image_info`.`SYSTEM_ID` = `systems`.`SYSTEM_ID` AND `image_info`.`ORGAN_ID` = organ.`ORGAN_ID` AND `image_info`.`GENPATH_ID` = `genpath`.`genpath_id` AND `image_info`.`IMAGE_ACTIVE` = 1 ORDER BY IMAGE_NAME;
I then can't filter properly on any of the foreign key items. I've tried setting up a table that is just:
select * from contrib;
Where contrib has 2 columns; one is contrib_id and the other is contrib_name. Then I alter the SELECT statement for the main table to display image_info.contrib_id, and set up the relationship in the column setup. However, it still only shows the numeric ID rather than the name. (images attached)
Any suggestions? Thanks!
Additional things I've tried:
- I've tried additional columns (institution), with the same result.
- I've also tried having just a query of:
SELECT * FROM image_info
and then setting keys - same result.
Hello Michael
Thank you for your purchase, and for your kind words.
From your screenshot I can see that the name of the column, you're applying the foreign key to, is the same as the value column:
You would need to rename the column in the database (the name of the source table), and then try to establish the foreign key connection.
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
OK - I've renamed the CONTRIB_ID column in the contrib table to ID and tried setting up the foreign key again, with no success.
Image 3 - columns in the contrib table
Image 4 - columns in the image_info table
Image 2 - settings for the contrib_id column in the image_info table
Image 1 - results
Thanks for the help!
Hi again Michael
Is this database that you're using maybe connected as a separate database? If that's the case, it's a known issue, and our developers will work on it. Otherwise, I have no explanation as to why it's not working.
If it's not a separate DB connection, can you please provide me a temporary wp-admin (administrator) user, so I can log in, and check it out. This is a public ticket, so make sure to leave your credentials in a PRIVATE response.
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
It is connected as a separate database. If I delete the other database connections so this is the only one, will that fix it? Thanks!
Hi again Michael
Unfortunately, no. The issue is related to this being a separate DB, instead of WordPress database, regardless of the number of separate databases. The only thing I can recommend is importing the table in WP database, and using the WP Connection for it.
Our developers will work on resolving the issue in the future, but I can't say exactly when it will be resolved.
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
I've tried importing all the tables into the Wordpress database, turned off separate database connections, and still have the same results. I'll add a user and send the info in a message. Thanks!
Thanks for the credentials, Michael.
All your queries had a semi-colon at the end, and the PHP SQL parser we use adds those automatically, so when you add them, they are added twice, actually, and that's what was causing the issue here.
I removed the semi-colons, and the "Contributor" column in table #11 now pulls the name correctly:
Please double-check, and let me know.
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
That was it - thanks!
You're welcome, Michael.
Glad I could be of service.
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