We know, we’ve been lazy this month, as this is our first article and it’s now the 31st – but, we were working on exciting projects so rest assured that we’ll make it up to you in the next month.
One of the exciting projects that we worked on was the sharing of a database table across 3 different Joomla websites. We tried several methods to reach this goal, but only one of them proved to be stable and secure, and it was the MySQL view.
For those who don’t know, MySQL, like almost any other RDBMS, offers an interesting functionality called a view. A view is more or less like a table, except that its content comes from other tables. Also, if there is a one to one relationship between a view and a table, then updating the view can also update the table. Let us explain…
Say you have a table called abc_emails in the database firstdatabase which serves one of your Joomla websites. Say you have another Joomla website which is served by the database secondatabase. If you want to use the table abc_emails on your second Joomla website (that has def_ as its alias), then all you need to do is to run the following query while using the second database:
CREATE ALGORITHM = UNDEFINED DEFINER=`firstdatabase_user`@`localhost` VIEW `def_emails` AS SELECT * FROM `firstdatabase`.`abc_emails`;
Once you do that above, any row that is added to the abc_emails table will automatically appear in the def_emails view, and any row that is added to the def_emails view will automatically be added to the abc_emails table. Nice, isn’t it?
You can do the above with as many Joomla websites that you want, but make sure that you only point a view to a table – do not point a view to a view, for example, do not point the view ghi_emails to the view def_emails, but rather point it to the abc_emails table.
Of course, there are other methods of sharing tables across multiple databases, but they aren’t as simple and as secure. For example, you can create a cron job that copies rows from tables to each other, but we almost guarantee you that you will regret it, and that you’ll run into many obstacles (trust us, we tried it). You can also update a table from another website by modifying the connection parameters to those of the first website, but this is also a non-solid and a non-secure option.
We hope that you found our post fun and exciting. If you need help with the implementation, then please contact us. Our fees are affordable, our work is clean, and we always love working on Joomla challenges!