Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 13 of 13
  1. #1
    New Coder
    Join Date
    Mar 2009
    Posts
    13
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Adding data from one table to another

    We use software that stores information on a mySQL database on our computers

    what I would like to do is automatically mirror a copy on the internet so it is always there and active even if the PC is off.

    I would also like to find a way to "merge" the data between the databasses without overwriting it.

    For example (what i am looking to achive):

    I have db1 with table x
    at midnight every friday when i am definitly going to have my pc on this data gets sent to the online db1
    some one else has db2 which also has a table x
    at midnight on monday when he will definitly have his pc on, it will get sent to online db2
    there is also an online db that merges the data from db1, db2 (and any other db) so that all of table x's contents is added together

    Is any of this possible
    and how would one acchive this
    Thank you for your time

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    There's no way to use a MySQL database on a webserver so you are both using the same database? (just thought I'd ask)

  • #3
    New Coder
    Join Date
    Mar 2009
    Posts
    13
    Thanks
    3
    Thanked 0 Times in 0 Posts
    unfortuantly not..
    the software we use is called SAM Broadcaster
    it required instantanious access to the database else you get issues
    we also have different files on our computer that the software utalises
    if i was using the same database as some one else it would be looking for stuff that isn't there

    the reason of the one on the server is to create some results that are a combination of all of our data...

    A charting system if you will.

  • #4
    New Coder
    Join Date
    Mar 2009
    Posts
    13
    Thanks
    3
    Thanked 0 Times in 0 Posts
    ultimatly just a way to automatically copy a database to a database on my server would be great, even if i have to have seperate charts for each dj

  • #5
    Senior Coder timgolding's Avatar
    Join Date
    Aug 2006
    Location
    Southampton
    Posts
    1,519
    Thanks
    114
    Thanked 110 Times in 109 Posts
    Ive got a pretty good shell script for doing this? That synchronizes two identical databases on different servers but they are both linux. I bet there's a DOS equivalent.
    You can not say you know how to do something, until you can teach it to someone else.

  • #6
    New Coder
    Join Date
    Mar 2009
    Posts
    13
    Thanks
    3
    Thanked 0 Times in 0 Posts
    hmmm well the one on my pc is windows and my web server is a linux server.. so i could run a script from that one to pull the data from this one if possible?

  • #7
    Senior Coder timgolding's Avatar
    Join Date
    Aug 2006
    Location
    Southampton
    Posts
    1,519
    Thanks
    114
    Thanked 110 Times in 109 Posts
    Possibly. Look into methods of importing/ exporting data to and from mysql. Might be able to do this with a php script. phpMyAdmin does a pretty good job of it so might be able to hack their code abit. I can' help you do that today as i have band practice but will look into over the next few days.
    You can not say you know how to do something, until you can teach it to someone else.

  • #8
    Senior Coder timgolding's Avatar
    Join Date
    Aug 2006
    Location
    Southampton
    Posts
    1,519
    Thanks
    114
    Thanked 110 Times in 109 Posts
    I've had another read of your post and some time to think about. I just realised that my script at work does work for copying a database from a Windows machine to a linux machine. It just connects to the database regardless of what OS it is stored on and pulls the data, but drops the existing database on the linux box before overwriting it. Which wouldn't work for you anyway.

    In your post you state that you want to merge the two databases. Therefore I assume you can both be inserting different data into your databases then somehow have the two merge together? I guess the best answer would be to both connect remotely to the same database. i.e put the whole database on a remote server somewhere and look into setting it up for remote connections. Or does the program you state you are using give you no choice as to where it connects.

    Another question when your db1 is sent to db2 what does it do then. Does it merge it then and the same when db2 is sent to db1. And if so what would be the point in having another online db? Or does the interaction between db1 and db2 not merge but do something else maybe mirror?

    Finally how many tables are you looking to merge would it be all the tables in the databases? can you be certain that the structure on the two databases is identical? One thing that might be tricky is if the databases have unique keys, because then you will will have to ensure integrity in the merge.

    Well i will need the questions answered here before i attempt to help your further with this.
    You can not say you know how to do something, until you can teach it to someone else.

  • #9
    New Coder
    Join Date
    Mar 2009
    Posts
    13
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Primarily i am looking to achieve this

    Copy my db to the online db AUTOMATICALLY(it can completly replace the data if its just my Db doing this) - Right now i have to do it manually and have not done for about 6months.. oops..

    The reason for this is when we are not "LIVE" we need a database online for people to connect to, and we want it to be up to date, and be able to use it for a charting system.


    The overal issue is this.

    We have broadcasting software called SAM BROADCASTER.
    This works with a mySQL DB
    with some code displays things like "currantly playing"
    and even allows people to request stuff directly from our playlist.

    Each "DJ" has a different DB on his own pc
    Each DJ has his own Collection of music

    SAM looks for the music through the DB
    We couldn't share an online DB for 2 reasons

    1. We all have different files, so SAM would endlessly be looking for files that are not there
    2. SAM needs instantanious access to the DB, if it is not hosted on the PC it can cause some serious issues.. esp for those with a slower connection.

    If you are interested in how this data is displayed: http://www.nexusradio.co.uk/forum/radio.php

    if you scroll down you can see the request section or you can click now playing to see on now/next

    You wont be able to request or see it updating if the DJ is "OFFLINE - SHOW REAIR'S - REQUESTS NOT AVAILABLE"


    Ultimately i would like the online database to be a Merge of ALL the DJ's databases so we had One big online Database of "STUFF" and could create an overall charting system, and overall top 10, from information collated from ALL the DJ's

    However I am happy to settle for this working on just my database for now
    I could even set it to have different online databases for each DJ
    and create a different chart for each dj.

    The bringing all the data together some how is just a preferance...
    ultimatly keeping at least one online database up to date is whats required

    The databases Are Identical. (well the tables are) because they are generated by the same piece of software

    There are 12 tables, and for example in my database there are 32,858 rows
    however with the way it works i think only one or two of the tables are actually needed to be copied, the songlist, and historylist table.. but if all is easier then thats fine too

    um.. i think thats all you asked for

  • #10
    Senior Coder timgolding's Avatar
    Join Date
    Aug 2006
    Location
    Southampton
    Posts
    1,519
    Thanks
    114
    Thanked 110 Times in 109 Posts
    Hi I haven't forgotten you. This is quite some task your talking about here. I have found a PHP class that will be quite some use to you. We can use this to make quick backups of a database and write it to file. This is a link to the class. http://www.phpclasses.org/browse/package/2527.html

    You can download that class then use it in lots of different ways. Here's a few examples of how it could be used.

    Example1 : Backup and allow user to download dump

    PHP Code:
    //config stuff. Obviously fill this in with connection info
    $mySQLserver='localhost';
    $mySQLuser='myuser';
    $mySQLpassword='mypassword';
    $mySQLdefaultdb='mydatabase';

    // Replace IP below with your IP. Provided you use a static IP. If not you need to consider other methods of authentication maybe httacess logins, php logins sessions etc 
    if($_SERVER["REMOTE_ADDR"]=="84.43.33.165"
    {
    mysql_connect($mySQLserver$mySQLuser$mySQLpassword);

    // Must make sure you have put the class file in the same location as script for below to work
    require("class_mysqldump.php");
    $dump = new MySQLDump();
    $stream_string=$dump->dumpDatabase($mySQLdefaultdb);

    header('Content-Description: File Transfer');
    header('Content-Type: application/octet-stream');
    header('Content-Disposition: attachment; filename='.$_SERVER['SERVER_NAME'].'_mysql.sql');
    header('Content-Transfer-Encoding: binary');
    header('Expires: 0');
    header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
    header('Pragma: public');
    // The strlen function blow only works if your database uses iso-8859-1. If it doesn't you need to work out how many bytes a char is for that encoding and multiply it to strlen.
    header('Content-Length: ' strlen($stream_string));

    echo 
    $stream_string;

    }
    else
    echo
    "<h1>You do not have privileges to access this document</h1>"
    Example2: Backup and save on server of database
    PHP Code:
    //config stuff. Obviously fill this in with connection info
    $mySQLserver='localhost';
    $mySQLuser='myuser';
    $mySQLpassword='mypassword';
    $mySQLdefaultdb='mydatabase';
    $filepath='mysql.sql'//make sure file is on server and has chmod 777 permisions

    // Replace IP below with your IP. Provided you use a static IP. If not you need to consider other methods of authentication maybe httacess logins, php logins sessions etc 
    if($_SERVER["REMOTE_ADDR"]=="84.43.33.165"
    {
    mysql_connect($mySQLserver$mySQLuser$mySQLpassword);

    // Must make sure you have put the class file in the same location as script for below to work
    require("class_mysqldump.php");
    $dump = new MySQLDump();
    $dump_string $dump->dumpDatabase($mySQLdefaultdb);

    $fp fopen($filepath'w');
    fwrite($fp$dump_string);
    fclose($fp);

    }
    else
    echo
    "<h1>You do not have privileges to access this document</h1>"
    Example 3: copy file from one server to other. Reuires two scripts one on each server.
    The server with database script:
    PHP Code:
    //config stuff. Obviously fill this in with connection info
    $mySQLserver='localhost';
    $mySQLuser='myuser';
    $mySQLpassword='mypassword';
    $mySQLdefaultdb='mydatabase';

    // Replace IP below with the IP of the other server. 
    if($_SERVER["REMOTE_ADDR"]=="84.43.33.165"
    {
    mysql_connect($mySQLserver$mySQLuser$mySQLpassword);

    // Must make sure you have put the class file in the same location as script for below to work
    require("class_mysqldump.php");
    $dump = new MySQLDump();
    echo 
    $dump->dumpDatabase($mySQLdefaultdb);

    }
    else
    echo
    "<h1>You do not have privileges to access this document</h1>"
    And the code on the other server. The one doing the pulling
    PHP Code:
    //config stuff.
    $location ="http://example.com/script.php"//the full url for other script
    $filepath='mysql.sql'//make sure file is on server and has chmod 777 permisions

    // Replace IP below with your IP. Provided you use a static IP. If not you need to consider other methods of authentication maybe httacess logins, php logins sessions etc 
    if($_SERVER["REMOTE_ADDR"]=="84.43.33.165"
    {
    $dump_string file_get_contents($location);

    $fp fopen($filepath'w');
    fwrite($fp$dump_string);
    fclose($fp);
    }
    else
    echo
    "<h1>You do not have privileges to access this document</h1>"
    This script will just make the dump file into a file at $filepath. I havnt looked into how to run it yet. Something like this maybe

    http://forums.devarticles.com/mysql-...-php-6007.html

    All these script are useful for doing a direct mirror. For merging we might be able to hack the class a bit. But remember if you import any of these dumps it will completely overwrite the data that is there. For merging bare with me. Well this is a start anyway
    You can not say you know how to do something, until you can teach it to someone else.

  • Users who have thanked timgolding for this post:

    Slave (04-12-2009)

  • #11
    New Coder
    Join Date
    Mar 2009
    Posts
    13
    Thanks
    3
    Thanked 0 Times in 0 Posts
    thank you very very very much I will get that tut and play around with what you have supplied!

    your the best

  • #12
    Senior Coder timgolding's Avatar
    Join Date
    Aug 2006
    Location
    Southampton
    Posts
    1,519
    Thanks
    114
    Thanked 110 Times in 109 Posts
    How you getting on?
    You can not say you know how to do something, until you can teach it to someone else.

  • #13
    New Coder
    Join Date
    Mar 2009
    Posts
    13
    Thanks
    3
    Thanked 0 Times in 0 Posts
    In all honesty I am a bit lost as to how to apply this to what i need it to do.
    My kids and missus have also been really ill so I havent had much time to actually sit down and read the class

    I don't have php or anything like that installed on my pc, though from the looks of it its not necessary.

    There is one issue i have..

    the db on my pc has a different name to the one on the server.
    The reason for this is i'm on a shared server type thing so it sticks my username infront of all the dbs i create

    So on my pc i have samdb
    on the server i have slave_samdb

    The only way i can access or update the db on the server manually is with phpmyadmin... they are no where when i connect to the ftp

    Either way I am sure when i have more time to read the class it will make alot more sense to me.

    Thanks for checking up on me


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •