Best approach for a custom database filling in WP custom post types

I’m a little unsure what “offer WordPress websites for them’ means in terms of implementation. But here are some questions I would ask when approaching this problem:

  • How tightly coupled are the systems? Is this DB and the related WP sites running on the same server?
  • For the DB on a dedicated server, do you have control over the MySQL DB and / or the api that manages it?
  • Do you really need to propagate this to WordPress? Does a JS POST request to the DB (or api) not make sense in this situation?

Same Host

The easiest thing would be if the dedicated MySQL DB is on the same machine since authentication is already taken care of. Events that happen with the MySQL db could then be pushed to WP using some sort of wp-cli command.

Different Hosts

If these sites are on different hosts, and if you are able to make changes to the dedicated server / api, then I would explore a solution where you:

  1. Create an endpoint in WordPress (Rest API) which then updates the WP DB as needed.
  2. Extend the dedicated server so that it issues a POST request to that endpoint

The main challenge here is authentication and security. It’s not impossible but I’ve never really liked working with oAuth. I like this solution best though because changes are propagated out almost immediately, so you don’t have differences between the two dbs.

Different Hosts, access only to WP

If you are not able to make changes to the dedicated server, then I think you are left with some sort of cron solution. WordPress has a pretty good HTTP API so that you don’t have to do the heavy lifting of generating a full request and dealing with weird edge cases. The main drawback is that unless you are checking fairly often, there might be a difference in the number stored in the dedicated DB and the number stored in the WP DB.

Hope that helps clarify your thinking!

Related Posts:

  1. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  2. ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
  3. Can’t connect to local MySQL server through socket ‘/var/mysql/mysql.sock’ (38)
  4. ERROR 1698 (28000): Access denied for user ‘root’@’localhost’
  5. ERROR 1698 (28000): Access denied for user ‘root’@’localhost’
  6. When to use SELECT … FOR UPDATE?
  7. ERROR 1396 (HY000): Operation CREATE USER failed for ‘jack’@’localhost’
  8. Create a new database with MySQL Workbench
  9. Column ‘user_id’ in field list is ambiguous
  10. Getting “Lock wait timeout exceeded; try restarting transaction” even though I’m not using a transaction
  11. Search for all occurrences of a string in a mysql database
  12. #1045 – Access denied for user ‘root’@’localhost’ (using password: YES)
  13. How to convert a string to date in MySQL?
  14. MySQL COUNT DISTINCT
  15. How to update a record using sequelize for node?
  16. Fatal error: Please read “Security” section of the manual to find out how to run mysqld as root
  17. Can’t open the mysql.plugin table. Please run mysql_upgrade to create it
  18. Convert MySQL to SQlite [closed]
  19. Create a temporary table in a SELECT statement without a separate CREATE TABLE
  20. Convert partially non-numeric text into number in MySQL query
  21. MySql: is it possible to ‘SUM IF’ or to ‘COUNT IF’?
  22. MySQL: What’s the difference between float and double?
  23. How can I search (case-insensitive) in a column using LIKE wildcard?
  24. MySQL Select Multiple VALUES
  25. SQL – IF EXISTS UPDATE ELSE INSERT INTO
  26. How to unblock with mysqladmin flush hosts
  27. Node.js MySQL – Error: connect ECONNREFUSED
  28. Solution to “subquery returns more than 1 row” error
  29. PHP date() format when inserting into datetime in MySQL
  30. MySQL Error #1071 – Specified key was too long; max key length is 767 bytes
  31. How To have Dynamic SQL in MySQL Stored Procedure
  32. MySQL – UPDATE query with LIMIT
  33. MySQL said: Documentation #1045 – Access denied for user ‘root’@’localhost’ (using password: NO)
  34. What SQL Query to do a simple find and replace
  35. Scanning Database for malicious Data
  36. how to refresh wordpress permalink settings? (without admin panel)
  37. Ordering posts by day (not time) AND meta value
  38. wpdb->prepare function remove single quote for %s in SQL statment
  39. Problems with DBDelta with FOREIGN key?
  40. Alternative to mysql_real_escape_string
  41. Is it useful to turn on MySQL query cache for performance
  42. Programmatically changing role information; editing role name and deleting
  43. How to remove ‘GROUP BY’ from SQL query produced from get_posts?
  44. Search and replace in post content
  45. MySQL LIKE not working
  46. How to add 2 hours to the wordpress time formed with current_time(“mysql”, false)?
  47. wpdb insert and boolean fields
  48. WordPress installation on valet php 8 (PHP Fatal error: Uncaught mysqli_sql_exception Table ‘wp_options’ doesn’t exist in wp-includes/wp-db.php:2056)
  49. What are the issues associated with upgrading MySQL for WordPress?
  50. How to find meta entries with their posts missing via SQL?
  51. Why doesn’t this code add a table to my database?
  52. SQL database export-import
  53. Mysql SELECT posts
  54. How do I get create a custom table showing the hours remaining for all customers in one page from this transactions page?
  55. Uncaught Error: Call to undefined function mysql_connect() – WordPress installation
  56. #1115 – Unknown character set: ‘utf8mb4’
  57. Visual/Text Editor displays blank pages in WP-Admin- but HTML on client side displays correctly
  58. How to add a private user page populated from sql?
  59. Slow wp-admin due to “SELECT DISTINCT post_mime_type FROM wp_posts WHERE post_type = ‘attachment'”
  60. Code to remove authors with no posts connected to them
  61. Which is better and faster ? WordPress Queries or SQL Query
  62. SQL Query in WordPress – Getting Impossible WHERE error
  63. Using WPDB->Insert()
  64. Active DB queries in WordPress?
  65. Changing MySQL password via WHM – does this affect WordPress?
  66. Run Username SQL Query from WordPress Child Theme Functions File
  67. SQL syntax error. However, it works normally at phpmyadmin
  68. string literals in query
  69. MySQL query for taxonomy-meta
  70. Calling mysql_query() on another database, assumes WordPress using that database
  71. WordPress VIP realpath Alternative?
  72. querying user bookmarks from a large number of bookmarks
  73. Pull post name from value of a specific meta key
  74. PHP variables in mysql query
  75. Php Mysql Terms
  76. Export SQL query based on custom field?
  77. How to fix the amount of comments displayed for each post?
  78. How do I echo all users’ display_name and their meta_value who have a certain meta_key?
  79. Lock wait timeout exceeded; try restarting transaction
  80. How to simulate web traffic to test performance of a website
  81. SQL query to change custom field in WordPress database
  82. SQL. Select category descendants
  83. MySQL Actibity without any active visitors via Google Analytics
  84. How to configure WordPress Database for CentOS 7
  85. Why is local wordpress install calling live site executions?
  86. Display MySQL as part of a Page
  87. Migration of wordpress database from mariadb 10.0.20 to 5.5.50
  88. WP Meta Query – mixed AND / OR query doesn’t work
  89. Geographical proximity query using post_meta
  90. Second completely independent and disposable WordPress installation in subdirectory?
  91. get_results suddenly not working on a single table
  92. Internal error (500) on local & Fatal error on live when trying to access post type edit screen
  93. Query sql for truncate post_content in wp_posts table
  94. Foreach only outputs if id is 0
  95. “Error 1064” when importing msql database after updating wordpress version to 4.2.1
  96. Need SQL query to find&replace image URL
  97. InnoDB: Error: log file ./ib_logfile0 is of different size
  98. Postgres equivalent to MySQL’s \G?
  99. Postgres equivalent to MySQL’s \G?
  100. How to check what port mysql is running on