Hi Everyone
Well i was just trying to work out how to copy data from one mysql table to another, in different or same databases. I cannot do it manually, as it will be run every minute or so by a cron. I need it to copy certain columns from the first database to the other.

Thanks for your help.

    $resource1 = database connection get select data from
$resource2 = database connection to insert data to
$sql = your select query to get all the data
$query = mysql_query($sql, $resource1);
for ($i=0;$i<($result = mysql_fetch_assoc($query));$i++) {
    mysql_query(insert statement goes here, $resource2);
}

mysql_close($resource1);
mysql_close($resource2);

    Basically, what you're doing here is going through each entry in your select query, and entering it into the other database, looping with the for loop. Since you may have two different databases, you need to add in the resource link, to make sure that you do it for the correct connection.

      Hi
      thanks for the reply
      As im quite new to php, can you please insert some varibles so i have a better idea as to what is going on
      Thanks

        Well, I would recommend finding a tutorial (google for php mysql tutorial), which can take you through the basics of using the two together. In addition, the PHP manual at php.net is very useful for looking up functions.

        I don't mean to sound rude, if I do, but it would be much better to take the time to learn it now than it would to spend much more time trying to debug something later.

          <?php 
$resource1 = mysql_connect(localhost, admin_plus, -----);
$db = mysql_select_db(admin_plus, $resource1)
               or die ("Couldn't select database.");
$resource2 = mysql_connect(localhost,admin_image,-----);
$db = mysql_select_db(admin_image, $resource2)
               or die ("Couldn't select database.");
$sql = "SELECT id FROM -members";
$query = mysql_query($sql, $resource1);
for ($i=0;$i<($result = mysql_fetch_assoc($query));$i++) {
    mysql_query("INSERT INTO users ('uid') VALUES('id') ($resource2");
}

mysql_close($resource1);
mysql_close($resource2);   
          
?>

          Wel i have been trying and searching, but best i could come up with is this, which does not work. Can you please help by just correcting it, thanks for your time. As i said ive been searching. I basically want to copy,the id column from the members table, into the uid column in the users table.

          Thanks

            Take a look for the INSERT... SELECT and CREATE (TEMPORARY)... SELECT syntax while you're at it, great for copying within a local system

              Hi
              I dont really understand, can you please explain, and if you can please help correct my script.

                Sawan wrote:
                <?php 
$resource1 = mysql_connect(localhost, admin_plus, -----);
$db = mysql_select_db(admin_plus, $resource1)
               or die ("Couldn't select database.");
$resource2 = mysql_connect(localhost,admin_image,-----);
$db = mysql_select_db(admin_image, $resource2)
               or die ("Couldn't select database.");
$sql = "SELECT id FROM -members";
$query = mysql_query($sql, $resource1);
for ($i=0;$i<($result = mysql_fetch_assoc($query));$i++) {
    mysql_query("INSERT INTO users ('uid') VALUES('id') ($resource2");
}

mysql_close($resource1);
mysql_close($resource2);   
                
?>

                Wel i have been trying and searching, but best i could come up with is this, which does not work. Can you please help by just correcting it, thanks for your time. As i said ive been searching. I basically want to copy,the id column from the members table, into the uid column in the users table.

                Thanks

                select id from -members should be select id from members

                your insert query has parentheses problems

                  Thanks for your reply.

                  <?php 
$resource1 = mysql_connect(localhost, admin_plus, -----);
$db = mysql_select_db(admin_plus, $resource1)
               or die ("Couldn't select database.");
$resource2 = mysql_connect(localhost,admin_image,-----);
$db = mysql_select_db(admin_image, $resource2)
               or die ("Couldn't select database.");
$sql = "SELECT id FROM members";
$query = mysql_query($sql, $resource1);
for ($i=0;$i<($result = mysql_fetch_assoc($query));$i++) {
    mysql_query("INSERT INTO users ('uid') VALUES('id') ($resource2");
}

mysql_close($resource1);
mysql_close($resource2);   
                  
?>

                  Can you please tell me of the parantheses problems on the insert query.

                    You need to make sure that, for every parentheses you open, you have a corresponding closing one. i.e. ((((()) is illegal, but (()(())()) is legal.

                      <?php 
$resource1 = mysql_connect(localhost, admin_plus, ------);
$db = mysql_select_db(admin_plus, $resource1)
               or die ("Couldn't select database.");
$resource2 = mysql_connect(localhost,admin_image, -----);
$db = mysql_select_db(admin_image, $resource2)
               or die ("Couldn't select database.");
$sql = "SELECT id FROM members";
$query = mysql_query($sql, $resource1);
for ($i=0;$i<($result = mysql_fetch_assoc($query));$i++) {
    mysql_query("INSERT INTO users ('uid') VALUES('id') ($resource2)");
}

mysql_close($resource1);
mysql_close($resource2);   
                      
?>

                      Ok right, i think i have fixed the parantheses problem, but now this error comes ups
                      Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in copy.php on line 10.

                      Can you please assist in fixing this.

                      Thanks for your time.

                        Have you tried that $sql query alone, not in a PHP script? Try it by logging into MySQL, or use something like phpMyAdmin to test it. Does it give you an error when you do that?

                          Shall i just you the sentance below for the mysql query
                          SELECT id FROM members
                          Thanks.

                            Ok, well the above sql query works.

                              I basically want to copy one column from the other table, to another in a diffrent database.

                                I dont exactly get it, can you try and do a sample code for me.
                                thanks.

                                  I dont exactly get it, can you try and do a sample code for me.
                                  thanks.

                                    MySQL Server doesn't yet support the Oracle SQL extension: SELECT ... INTO TABLE .... Instead, MySQL Server supports the SQL-99 syntax INSERT INTO ... SELECT ..., which is basically the same thing. See section 6.4.3.1 INSERT ... SELECT Syntax.

                                    INSERT INTO tblTemp2 (fldID) SELECT tblTemp1.fldOrder_ID
                                    FROM tblTemp1 WHERE tblTemp1.fldOrder_ID > 100;

                                    Alternatively, you can use SELECT INTO OUTFILE... or CREATE TABLE ... SELECT.

                                      Ahh ok, but what is this fldorder_id, would that be a varible in the table.

                                      Thanks.