Hello Lauren

Thank you for your purchase.

Can you, please, tell me which posts are you trying to add, and which option are you using? I'm guessing you're using the Generate a query to WordPress database feature, correct?

If so, can you 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.

Best regards.

Hello again Lauren.

I see you have a lot of Joins in [wpdatatable id=3] and [wpdatatable id=5], and like already described in our documentation, there are some limitations on editing tables with wpDataTables:

• Only one MySQL table can be edited at a time. Queries from multiple tables with joins cannot be used as an editable feature, since SQL UPDATE and INSERT statements are generated automatically, and there currently is no way to update multiple tables – but this problem is being investigated and pursued.
• Only MySQL tables or tables created with “Create a table manually” and “Create a table by importing data from data source” options of “Create a Table” page can be edited.
• Server-side processing MUST be turned on for front-end editing – Note: It will turn on automatically if you forget to enable it.
• MySQL Views cannot be edited properly – only one of the underlying tables can be edited if you pass the proper ID.

So, this can also be an issue if you want to make these tables editable.

Another thing worth mentioning here is noted in the section about the query generator:

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.

My advice is to try using the MetaBox plugin, which can create a new simpler table from posts and display them in a simple table, which you would be able to pull into wpDataTables with a simple query like

SELECT * FROM my_table

and I believe this would be a workaround for most issues.

The only other option would be to work with the query generated by the constructor, and modify it ("play around" with it), until it works.

Please note that using this plugin feature (SQL query) 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. If you do not know how to use MySQL, refer to the “Create a MySQL-query-based table by generating a query to MySQL database” section. It will explain how to create complete tables or MySQL queries without this basic knowledge. Please be advised that preparing MySQL queries for you is not included in the plugin support, but it can be done as a paid customization service.

Best regards.

Hi again Lauren.

Well, our price is $50 per hour, but unfortunately we're very busy at the moment, working on some priority tasks and fixing bugs and issues with our plugins, so we won't be having the time for custom work in near future.

But we can recommend this service for customization: https://wpkraken.io/?tms-plugins (by this link they would know you come from us) - they do develop such custom solutions, could you please send your inquiry to them?

Best regards.

Hi again Lauren.

Yeah, unfortunately this is due to a lot of JOINS you have in the query.

If your MySQL-query based wpDataTable doesn’t work correctly with server-side processing, probably this is happening because wpDataTables server has problems with parsing of the query and building new queries dynamically (rarely happens, but does sometimes). To avoid this you can prepare a MySQL view (a stored query), which will return the data that you need, call it e.g. “view1” and then build a wpDataTables based on a simple query like “SELECT * FROM view1″.

Please note some this when working with the server-side processing feature:

• Please do not use “LIMIT” in the SELECT statement. wpDataTables adds it automatically and it will be overridden.
• Please do not use “ORDER BY” in the SELECT statement. wpDataTables has its own sorting engine so it makes no sense to use MySQL’s sorting, since it will be overridden. Also, server-side processing feature adds this part of statement automatically when users trigger the sorting on the front-end, and having it in initial statement may cause the table to crash.

If, however, you don't need to edit this table and are just using it to display the results, we can "make" the search work by deactivating the server-side processing:

I left it like this, so you can test it out. If it works for you, and you don't plan the table to grow over 2.000 rows - you can keep this OFF and searching through the table will work.

Please let me know.

Best regards.

Hello Lauren.

Sure, I can try helping you out. I'd need access to C-Panel as well as your website's credentials.

When you tell me the details what needs to be inserted where and where from, I'll take a look and tell you if I have the time for custom work or not. We've been swamped in the past couple of weeks, so I cannot guarantee anything.

Our price is $50 per hour, and before you pay anything I will send you a quote if I find it possible to achieve.

Best regards.

Hi again Lauren.

I managed to insert the first two columns, but _mphb_amount doesn't actually show "0" anywhere. I checked the Bookings, and see there are some IDs which should have $0 under that meta value, but it doesn't in the database.

As for the last column, I keep striking out with it, and don't have the time to join that unfortunately. It is fairly simple to pull it in a separate table like this:

SELECT  post_id,
        meta_key,
        post_title
FROM _4KD_postmeta
INNER JOIN _4KD_posts
ON _4KD_postmeta.meta_value = _4KD_posts.ID
WHERE meta_key = '_mphb_room_id'

The thing with it is that it's storing the room ID as ID column from _4KD_posts, which is the same column from where we're pulling booking IDs.

I can take a look at it tomorrow again, but I'm sorry to say I don't have the time now. If you can add it to the new table I created, so it displays only the room IDs like 4687, then you can create a new table using the query I provided above, and link that column via Foreign Key feature.

Best regards.

Disregard that, Lauren, I believe I got it

Ticksy was down for about an hour, so I managed to find a workaround.

The only thing left bugging me is the "Amount paid".

Rooms should be displayed correctly now in the last column.

Please take a look at [wpdatatable id=14] and let me know what you think.

I believe we have to deal with the "Amount Paid" column, don't we?

Best regards.

Hey Lauren.

Sorry for the late response.

I see you have some bookings entered multiple times in the original table.

All new columns are depending on meta values entered in the postmeta table, so if there is something empty or missing - it will be missing in the table as well.

For example - if there is no "_mphb_amount" entry under a specific ID - it will not be listed, and all bookings in that row (under that ID) will also not be displayed.

I believe I have exhausted all my resources here, and simply cannot help you to retrieve all listings. At least not quickly enough. I'm sorry to say that you may want to reach out to a third party company which does custom work for databases, and have them finish what I started.

I've tried to make it work for all entries, but simply cannot do it. I apologize for that. What I already did will not be charged, of course, and you can use it for future reference.

With my sincere apologies, best regards.

Hi again Lauren.

I logged in again, and added this to [wpdatatable id=5]:

GROUP BY mphb_booking_ID

It doesn't have multiple rows for each ID now. I just need you to check if it looks good or not.

Best regards.

Hello again Lauren.

I am sorry for the late response. You may have seen that we're currently out of office, on a conference, but we're trying to catch up to everything.

I understand what you mean. For instance - Kyle Sandler reserved 7 rooms, and you would like to have them all displayed.

Unfortunately, like I mentioned in one of my previous replies - I have exhausted my knowledge here, and cannot get it to display that. I can pull one room # based on the Booking ID, but all the others would require some conditional logic, which is beyond my capabilities.

Sorry again, and good luck with this. I know it's probably going to be a 10 minute work for someone who knows SQL more than I do, but for me it'd take days.

Best regards.

Hi again Lauren.

You're welcome. I'm sorry I couldn't have done more. It is good to export all the information. Maybe the query can be even simpler. If they do a CSV file, you can import that in the database, and have it easily accessible via SQL queries.

If you have any further questions, please feel free to open a new ticket, and we'll gladly help.

Best regards.

A CSS file? 

I don't know how they wish to work with that, though, but an Excel or a CSV file would be much more usable.

You could create a table linked to an existing data source, and it would pull the data from there. Although, you would need to import it every day, since the file is being saved in ../wp-content/uploads/YEAR/MONTH.

It can be changed, of course, but it still has to be on the same server, and the columns should never change their order, get deleted nor could new ones be added to the source file, since that would break the table.

I may have some more time on my hands next week, so when you're done with the table, and if you hit any roadblocks, please let me know and I'll take a look.

Best regards.