I have a local website in which user adds items to Mysql DB everyday. Now I want to make a live version of the site.
But instead of adding items to both databases, I want to only add to the local database. And sync the remote database.
Local site uses XAMPP. Also I don't think replication is the way I want to do it.
I'm looking more of a PHP way of doing this task.
Currently I have no idea on how to achieve this.
Any idea on how to do this?
The quickest way to do this would be with MySQL, but if you want to use strictly PHP, there're two ways to go about this if you want the live data to only reflect the local data (i.e. you're fine with deleting all data and re-pulling data). Because this solution is in PHP, you will have to loop through each individual table. You can run a PHP script that uses either PDO or MySQLi, but you will need one of two deletion strategies listed below:
1) TRUNCATE and SELECT FROM (Fast, but has potential security risks) This is a bigger risk because the MySQL permissions to TRUNCATE are DROP and ALTER. Not commands you want a regular database user to have access to. Here's the SQL to pull it off:
TRUNCATE live_database_name.table_name
INSERT INTO live_database_name.table_name SELECT * FROM local_database_name.table_name
2) DELETE FROM and SELECT FROM (Slower the more data you have, but safer). This solution is slower because you have to walk through each entry in a table rather than dropping and re-creating the table. However, DELETE is seen as a safer permission to give a database user as they can't DROP entire tables. Here's what you'll need to pull it off:
DELETE FROM live_database_name.table_name
INSERT INTO live_database_name.table_name SELECT * FROM local_database_name.table_name