Tony wrote:
I have set up four websites for local charities but they want the same events to appear on all websites. The sites have all been set up using Joomla and are on the same server but different domains. A common connection to MySQL is not a problem.
What I am trying to do is collect data from the MySQL database of the main site and send it to the other sites using PHP (will use a cron job when I get the script working). There is only one table that needs to be transfered.
So you have a single table that needs to be read by multiple clients? Who needs to write to it?
I would prefer to drop the old table and create a new one otherwise deleted events would stay on the client sites.
Can anyone help or suggest an easier way of doing it?
Regarding the script, briefly:
$table2 = "jos_extcal_events"; /[other stuff snipped, but $table2 hasn't been mentioned until:]/ mysql_query("INSERT INTO $table2");
Am I missing something? That INSERT doesn't look like it actually carries the data you want put in.
With respect to other possible solutions: Have you got good reason not to give the different sites access to the master copy of the table? That would be a lot simpler. If you've got enough authority over the MySQL server, you could make a separate database and user for that alone.
If you have a single user who has the right permissions on all the databases (read the master, write the slaves) you could do the copy within MySQL, which would keep the PHP simpler. If you have sufficient access to the machine, you could Cron the MySQL statements through the command-line client and do away with PHP completely, which makes the project much simpler and should reduce the overhead of running the update task considerably.
If you are copying, dropping the table to lose the old data is only necessary if you change the schema of the master table, otherwise TRUNCATE is simpler (and probably quicker). Bear in mind if you decide to DROP that you will need to CREATE it again, so you either need to hard-code the table schema into your update script or make the script smart enough to construct a CREATE statement based on the schema of the master.
The MySQL you want to pass (either through PHP or into the MySQL client) probably looks a little like this:
TRUNCATE `db2`.`table_name` ;
or if you're dropping:
DROP TABLE IF EXISTS `db2`.`table_name` ; CREATE TABLE `db2`.`table_name` ( /[insert column and key declarations]/ ) TYPE = MYISAM ;
then (assuming you can get access to both from the same user)
INSERT INTO `db2`.`table_name` SELECT * FROM `db1`.`table_name` ;
Hope some of that helps.
Matthew Holland