Hi, Pam.

It seems to me that the issue is the Database Table prefix that our plugin is expecting,

or in other words if this site/database has a different DB prefix stored in our "wp_wpdatatables" table compared to the "old site",  then our plugin won't pull them correctly,

but there is a workaround to solve this.

-

If you changed the domain or Database Table Prefix,

 the path to the files for the source data of tables most likely changed.

In order to check what is the current/new path of the tables,

first you can create a new table linked to an Excel or CSV file, and it should generate just fine. 

( Or make a new Manual Table)

Once you do that, access your database, and open the 'wp_wpdatatables' database table.

( wp_ is the default table prefix, on your site it might be different)

In the "content" column, you will see the path related to the new table. 

So, if the new file is stored in (for example):

/var/www/html/your-NEW-site/wp-content/uploads/2021/02/file-one.xls

( Or for Manual Tables, check in the Content column what is the SQL Query showing there)

And if your old files are in:

/var/www/html/your-OLD-site/wp-content/uploads/2021/02/file-two.xls

You need to modify the content column for each row, and replace the path.

To do that, you can run this SQL command in your Database ( via PHPMyAdmin):

UPDATE `wp_wpdatatables` SET content=REPLACE(content,'this_part','that_part')

Please bear in mind that wp_ is the default table prefix of WordPress,

 but on your new site it could be anything, so check what is your DB table prefix,

then you should run `your_prefix_wpdatatables` and actually replace the part of the path in "this_part" and "that_part" sections.

-

That replaces the prefix in the "Content" column. And this:

UPDATE `xyz_wpdatatables` SET mysql_table_name=REPLACE(mysql_table_name,'wp_','xyz_');

That replaces the prefix in the "mysql_table_name" column. 

Once you do this, you should be able to simply load all your manual tables without issues.

-

If you're unsure how to do this, I can do it for you if you can provide me with a temporary WP-Admin user (administrator), 

send us URL with Admin credentials for WP-Admin;

and database (phpMyAdmin) access credentials.

If you send us access, please make sure to check PRIVATE reply - for safety reasons, so that only we can see it.

Hi, Pam.

Thank you for the screenshots, that helps.

Basically, in the error message screenshot i notice that our plugin says it can not find a table in the Database with this prefix from the error.  I can't really type all of these numbers and letters perfectly from the screenshot,

but as you see, it says "MySQL said :  table name 'wordpress-313931a99e.obit_index' does not exist in this Database.

While in your new screenshots, i can see that our plugin is "expecting" to see the database table prefix as f3_tableName.

So it seems to me that the "old prefix" was 'f3_'   and this new Database Table prefix in this WordPress Database is 'wordpress-313931a99e_' .

So the table name is not the issue, as "obit_index", but rather the table prefix was previously "f3_obit"

while now it seems to be "wordpress-313931a99e.obit_index".

Actually, when i take a closer look, it seems that the table name is also slightly different.

Before it was just "obit" and now it seems to be "obit_index" so i am honestly not 100% sure.

-

In any case, if you wish to get the old/existing DataTables to show in our plugin after you "migrated"/moved all tables in this database,

then i can suggest to change the Database Prefix in the "content" column of the "wp_wpdatatables" table.

-

First, i would make a new Manual Table, then check in this new Table ID,  inside the "wp_wpdatatables" table,

what does it say in the "Content" section.

Check the table prefix "NEWprefix_TableName" , i guess it will probably not be f3_TableName.

Then, you can run this Query :

UPDATE `wp_wpdatatables` SET content=REPLACE(content,'f3_','NEWprefix_')

Where you would replace the 'NEWprefix_' with the actual prefix from the current database.

Let me know if this helps;

or if you want me to do that for you, you can add a user for my Email :

[email protected].

Thank you.

Hi, Pam.

Firstly, my apology for the waiting time on this reply.
We had our Orthodox Easter holiday break, which increased the delay.
Thank you very much for your patience.

-

In the meantime, our 2nd level Team has advised me.

They confirmed that our plugin's SQL feature is not meant to be used to call an Index like that,

but similar to how the PHPMyAdmin is giving you that error;   

when you run a standard SQL Query, it is expected to pass the table name ( at the end of the SQL SELECT statement, like FROM "tableName", rather than FROM "tableindex").

So, unfortunately, we do not have a valid solution or a workaround to achieve pulling table's Index as you are trying.

If you wish to see a solution for calling data from table's Index in the future,

Please feel free to search on our suggestions page, 

 to see if someone may be already suggested this feature. If you can't see it, feel free to add your suggestion there,  and as more people vote, the feature will move higher on the priority list.

You can certainly follow our changeLog page if you'd like ( it is also available in the plugin dashboard), where we state any changes/new features/bug fixes during updates;

and our newsletter, so you're informed about new features, bug fixes, freebies, etc.

-

As we mentioned earlier, to achieve returning the data you need, you will first have to run a successful SQL Query in the Database itself,

then later on you can try the same Query in our SQL Table.

Please note: Using our plugin's SQL Query feature requires at least a basic knowledge of SQL. It is assumed that you can create the table in some MySQL data manager (e.g., PHPMyAdmin, MySQL Workbench), and prepare a SQL query that will return the data you need. 

A good way to achieve this can be experimenting using our WPDB SQL Query Constructor tool.

- Please note: this tool is not an ultimate query generator. It simply constructs a suggestion of a query. We are constantly working to improve it, but SQL is such a complicated and flexible language that full automation for constructing queries is next to impossible. 

Consequently, the more complicated the query, the higher is the probability that it will not return exactly what you need. So, you will often need to play around with the resulting query.

Please be advised that writing custom SQL Queries or debugging Queries does not fall under what our support covers.

You can check out this official page of WP codex,  for help with writing custom Queries.

Hi, Pam.

Firstly, I would like to sincerely apologize for the delayed response as we have been experiencing an unusually high number of tickets. I am sorry that it has taken longer than usual to respond to your concern and your patience is highly appreciated.

-

I am sincerely sorry for this inconvenience, but our support is not able to debug custom SQL Queries for our users.

The syntax in the PHPMyAdmin should be the same between MariaDB and MySQL as far as i am aware.

I just researched about this for some time,

and there are some articles that explain this in more details.

For example, on this article,  it says :

"Because MariaDB is a fork from MySQL, the syntax is similar, but MariaDB has several other features. 

Basic SQL syntax is the same, but the way MariaDB stores data or handles functions is different . 

Each new version of MariaDB also has added features. The latest version of MariaDB has several new features and extensions. "

And our support is only able to help users with some basic SQL Query modifications,

so for basic syntax they are the same.

-

Generally speaking, i would say the main issue in this ticket/ your use-case is that you tried to pull data from a table Index, rather than a table itself;

our plugin is not able to pull data from an Index like that,

in the SQL SELECT statement you need to provide our SQL Parser with an actual Table Name to pull the results without errors.

But you are free to research and try custom workarounds, to somehow get the data as you need it,

our support is just not able to cover custom solutions.

-

As we pointed out in our SQL based tables documentation;

Please note: Using this plugin feature requires at least a basic knowledge of SQL. It is assumed that you can create the table in some MySQL data manager (e.g., PHPMyAdmin, MySQL Workbench), and prepare a SQL query that will return the data you need. 

A good way to achieve this can be experimenting using our WPDB SQL Query Constructor tool.

- Please note: this tool is not an ultimate query generator. It simply constructs a suggestion of a query. We are constantly working to improve it, but SQL is such a complicated and flexible language that full automation for constructing queries is next to impossible. 

Consequently, the more complicated the query, the higher is the probability that it will not return exactly what you need. So, you will often need to play around with the resulting query.

Please be advised that writing custom SQL Queries or debugging Queries does not fall under what our support covers.

You can check out this official page of WP codex,  for help with writing custom Queries.

We hope that might help.

Hi, Pam.

Can you send me an SQL dump of that file that you are importing to your database?

We can inspect this and see what we can do.

Of course, if the file contains any sensitive data, make sure to mark it as PRIVATE reply, for safety.

Thank you.

Hi, Pam.

Firstly, I would like to sincerely apologize for the delayed response as we have been experiencing an unusually high number of tickets. I am sorry that it has taken longer than usual to respond to your concern and your patience is highly appreciated.

-

I have imported the SQL dump you sent, and in my PhPMyAdmin there are a lot of NULL dates in some columns.

Our plugin is going to show any values that are coming as NULL from the Database SQL Table as that "default date"  01/01/1970.

Could we take another look from your database, can you show me some screenshots/ or record a Video how it looks on your end when you view this Obit SQL Table in the Database,

can you see some of these "NULL" values in the Date columns?

If you can point out some rows/cells that are not "NULL" in the Database Table, but are showing as 01/01/1970 in our plugin's table, and we can investigate the issue?

-

If i try to log in again on your website,  it says it has a critical issue.

https://keystoneconnect.co/wp-admin/

So if you could show us some screenshots/ or even better a Video how all this looks,

and we can also take a remote closer look at the database, if possible?

-

Please provide me a temporary WP-admin (administrator) user 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 parties. 

You can write credentials here just check PRIVATE Reply so nobody can see them except us.

And if you can send us access to the Database/PhPMyAdmin URL link with login credentials.

And point us to this SQL Table ID from our plugin.

Thank you.

Hi, Pam.

Apologies for all the waiting time.

I am glad to see that you resolved the Dates to show correctly.

-

In regards to the number of rows i got when importing the "Obit" table SQL dump you sent previously,

i have just tried that again, first deleted it and re-imported it again,

when i check the table in the Database,  the PhPMyAdmin says it has  216 783 total rows.

Hi, Pam

Firstly, I would like to sincerely apologize for the delayed response as we have been experiencing an unusually high number of tickets. I am sorry that it has taken longer than usual to respond to your concern and your patience is highly appreciated.

-

I am sorry, but our support only covers troubleshooting/debugging issues that come from our plugin, wpDataTables.

We are not able to provide tutorials/troubleshoot/debugging for issues that are with your source data in your SQL Database, such as converting from one Date Format to another,

but we will try to point you in the right direction.

I researched online and found some articles that seem useful.

1. You can check out this from TutorialsPoint, that says : STR_TO_DATE() method from MySQL to convert the date format.

2. And this one, that says : MySQL query to convert YYYY-MM-DD to DD Month, YYYY date format.

( It is not the same format you need, but i thought it can be useful as an example, then you can search a bit more to see what is the syntax to get your format)

3. Here a user on Stack Overflow is getting advice how to change a Date format.

As mentioned earlier, our support is very limited to what we can advise when it comes to issues outside of our plugin.

You should be able to find the right tutorial online, or on Stack Overflow from other users.

I hope this helps.

Hi, Pam.

You're welcome, i am happy to help. 

Please don't hesitate to create new tickets for anything else we could assist with.

Have a great day!