rvwill48

Yes, I can confirm that it is fixed because I had the same problem.
Thanks Peter.

Please accept my apologies for raising an issue when there was none. I’ve since had an Eureka! moment remembering that when I first installed the WPDA plugin I had checked ‘Show only selected tables’ in the ‘Table access’ options (please see the pic in the link below). Changing it to ‘Show all tables’ displayed the ‘missing’ tables.
Data Access Settings: Select tables

I’m still finding my way around the plugin which is taking me a while because there are so many clever facets to it.
Best wishes,
Richard

Hi Peter,
I’ve resolved this problem. All I had to do was to edit the connection (I just changed the connection name), clicked ‘save’, then ‘test’ and all 29 tables were displayed.

Were the tables create with the same user account? If not, you might need to give the user access rights.

All the tables were created using the database administrator account. However, the plugin connects to the DB with an user account that only has CRUD access. I’ll change the connection string to see what happens.
Thanks for the head’s up on this.

Did you create the table you designed in the Data Designer?

No I didn’t.
I’m using MySQL Workbench to manage my database. When I started using your Data Access plugin I created a connection to the remote database and upon doing so the tables and views already present in the DB appeared in the Data Designer. I was then able to display the content of a specific view which is what I wanted. All great so far.
A couple of weeks later I went ahead and created three new tables via Workbench thinking that it would be possible to have these tables show up in the Data Access page by ‘refreshing’ the connection but no, hence my original question.
Can I achieve what I want by removing the original remote connection and creating a new one?

@websgt
I’m sorry to hear that nothing appears to have worked for you. I hope Peter can help resolve the sort issue for you.

I’m assuming that your datatable is located within your pre-installed WordPress database which gives you relatively limited access to it.

Your website is hosted by DomainsByProxy which is a reseller for GoDaddy. The basic GoDaddy hosting account includes up 10 MySQL databases, so there is always the possibility for you to create your own database outside of WordPress. This would enable you to have much more control over the data and Peter’s Plugin would still work as it caters for ‘remote’ databases which reside on the same server as would be the case in this instance. This is the way I prefer to work.
Just something for you to consider for the future.
Regards,
Richard

Looking at your create table statement I notice that:
1. Your database engine is specified as ISAM, why is this?
2. You specify a Primary Key on the TERM column and
3. Then go on to specify a unique index on the same column.

A primary key column is by definition an unique index, so it appears you are creating a second unique index on a primary key column. I’ve no idea what effect this will have on the behaviour of the database table when sorting.

To reorder your table rows you’ll need administrative access to the database. I would suggest using the ALTER TABLE sql command to reorder the rows as follows:

ALTER TABLE ‘MCL_Officers’ ORDER BY ‘TERM’ DESC;

This will physically rearrange the records into the order you want and they’ll stay that way until you add a new record or delete an existing one, then you’ll need to rerun the above ALTER TABLE command again if by then you haven’t fixed the sorting issue.

Best wishes,
Richard

Ok Peter, thanks for clarifying.
Could Steve’s issue have anything to do with webpage caching then, since he’s changing the sort order in the publication but sees no effect?
How is the ‘order by’ setting applied in your plugin?
Richard

try
ORDER BY LEFT(TERM,4) DESC

Can you just sort using just the first four characters of the TERM field? This will definitely work.
My suspicion is that the presence of the minus sign in the term of office is somehow causing MySQL to convert the field into a numeric calculation whereupon the result of each row is -1. This would explain no change when sorting ASC or DESC. Just a thought.

FIXED: For anyone else who comes across this issue, the solution in my case was to ensure that all exported data fieldnames begin with the word ‘event’. Personally, I think this is a bug in the event calendar’s import routine.