Results 1 to 7 of 7

Thread: Move Data from 1 Table to Another

  1. #1
    Join Date
    Oct 2006
    Posts
    94
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Cool Move Data from 1 Table to Another

    I have a PHP script that retrieves information from a table in a database and I have a PHP script that adds data to a table. What I'm after is some way to put the data I retrieve from 1 table into another table. Is there a MYSQL command to move data from one table to another? Or should I be going about this a different way?

    I did think that maybe I could get the data retrieved to be stored in a form maybe. And then if I click submit, it would send the data to the existing 2nd table.

    Any ideas would be helpful.

    Thanks

    Smithster

  2. #2
    Join Date
    Sep 2005
    Location
    India
    Posts
    1,626
    Thanks
    6
    Thanked 107 Times in 107 Posts

    Default

    It can be done using the following scenario

    1. You are retrieving the records from one table.

    2. You starts a loop (while) that goes from the first record retrieved to the last record retrieved.

    3. You can construct an INSERT statement (SQL statement) with the current field value(s) that you had retrieved inside the loop that I had mentioned in step 2. Something
    assuming that $first and $second has the retrieved record's first column and second column value then you can have something like the follwing:
    Code:
     $sqlStmt = "INSERT INTO your_table_name VALUES(".$first.",".$second.")";
    In each iteration of the loop it will construct a new insert statement.

    4. Execute the insert statement agains the correct database.

  3. #3
    Join Date
    Oct 2006
    Posts
    94
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Default

    ok thanks for your reply, I think I understand what you are saying there. But, I didn't explain too well in the first place.

    Lets say I have 10 rows in my table and I only want selected rows to be moved / copied to another table. I would want to do this one at a time with the ability to be able to select a row and then submit that one for insertion into the other table.

    Could I use the INSERT statement like this?.....

    Code:
    $sqlStmt = "INSERT INTO TABLE2 VALUES(".$row[4].")";
    I hope this comes across clear!!

  4. #4
    Join Date
    Oct 2006
    Posts
    94
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Default

    Actually, that won't work. How does it know which field to store it in?? The real question, how do I tell it which field?!?!?

    Smithster

  5. #5
    Join Date
    Sep 2005
    Location
    India
    Posts
    1,626
    Thanks
    6
    Thanked 107 Times in 107 Posts

    Default

    I hope you consider the data type compatibility issues long before the designing a system that does this. Now it is clear that you won't be able to store a numerical data inside a date field.

  6. #6
    Join Date
    Oct 2006
    Posts
    94
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Default

    yes I am sure there wouldn't be any data type compatibility issues as both tables are completely identical. Just that one table is for applications and the other is for approved applications.

  7. #7
    Join Date
    Oct 2006
    Posts
    94
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Default

    ok, after searching around various topics, I manadged to come up with a working script that does exactly what I wanted it to do.

    Here's the code.....

    PHP Code:
    if($_GET['a'] == 'process' && $_POST['approve']){ 

      $idlist = $_POST['approve']; 

      if($_POST['approve_sub']){ 
        $action = 'approve'; 
      }elseif($_POST['reject']){ 
        $action = 'reject'; 
      } 

    ?> 
    <form action="<?php echo $_SERVER['PHP_SELF'?>?a=<?php echo $action?>" method="post"> 
      <center> 
        You are about to <?php echo $action?> the following applications:<br /> 
        <table border="5" cellpadding="5" cellspacing="0" style="border-collapse: collapse" bordercolor="#808080" width="90%" id="AutoNumber2" bgcolor="#C0C0C0"> 
          <tr>  
            <td width=17.7% align=center bgcolor="#FFFFCC"><b>Name</b></td> 
            <td width=14.8% align=center bgcolor="#FFFFCC"><b>Country</b></td> 
            <td width=8.1% align=center bgcolor="#FFFFCC"><b>Location</b></td>    
          </tr> 
    <?php 
    foreach( $idlist as $key => $value){ 
    $row mysql_fetch_array(mysql_query("SELECT * FROM `apps` WHERE `id`='" $value "'")) or die(mysql_error());  
    echo 
    "      <tr>"
    echo 
    "        <td align=center><input type=\"hidden\" value=\"" $row['id'] . "\" name=\"approve[]\" />" $row['alias'] . "</td>";  
    echo 
    "        <td align=center>" $row['Name'] . "</td>"
    echo 
    "        <td align=center>" $row['Country'] . "</td>"
    echo 
    "        <td align=center>" $row['Location'] . "</td>";  
    echo 
    "      </tr>";  

    ?> 
          <tr>  
            <td align=center bgcolor="#FFFFCC" colspan='5'><input type="submit" value="Confirm" name="confirm" />  <input type="submit" value="Cancel" name="cancel" /></td> 
          </tr> 
        </table> 
      </center> 
    </form> 
    <?php 
    }elseif($_GET['a'] == 'approve' && $_POST['approve']){ 

      
    $idlist $_POST['approve']; 

      if(
    $_POST['cancel']){ 
        
    $cancel 'true'
      }elseif(
    $_POST['confirm']){ 
        foreach( 
    $idlist as $key => $value){ 
          
    $copy mysql_fetch_array(mysql_query("SELECT * FROM `apps` WHERE `id`='" $value "'")) or die(mysql_error()); 
          
    // ^^ SELECT THE DATA TO BE COPIED ^^ 
           
          
    @mysql_query("INSERT INTO `approved` (`Name`, `Country`, `Location`) VALUES('" $copy['Name'] . "', '" $copy['Country'] . "', '" $copy['Location'] . "')") or die(mysql_error()); 
          
    // ^^ COPY THE DATA ^^ 
          //CHANGE 'approved' INTO THE NAME OF THE TABLE CONTAINING THE APPROVED APPLICATIONS 
          //ADD ANY OTHER FIELDS THAT ARE NECESSARY TO THE LISTS 
           
          
    @mysql_query("DELETE FROM `apps` WHERE `id`='" $value "'") or die(mysql_error());   
          
    // ^^ DELETE THE OLD DATA ^^ 
        

        echo 
    "Applications successfully approved."
      } 

    }elseif(
    $_GET['a'] == 'reject' && $_POST['approve']){ 

      
    $idlist $_POST['approve']; 

      if(
    $_POST['cancel']){ 
        
    $cancel 'true'
      }elseif(
    $_POST['confirm']){ 
        foreach( 
    $idlist as $key => $value){   
          @
    mysql_query("DELETE FROM `apps` WHERE `id`='" $value "'") or die(mysql_error());   
          
    // ^^ DELETE THE REJECTED APPLICATION DATA ^^ 
        

        echo 
    "Applications successfully rejected."
      } 


    if(!
    $_GET['a'] || $cancel || !$_POST['approve']){ 
    ?> 
    <form action="<?php echo $_SERVER['PHP_SELF'?>?a=process" method="post"> 
      <center> 
        <table border="5" cellpadding="5" cellspacing="0" style="border-collapse: collapse" bordercolor="#808080" width="90%" id="AutoNumber2" bgcolor="#C0C0C0"> 
          <tr>  
            <td align=center bgcolor="#FFFFCC"></td> 
            <td width=17.7% align=center bgcolor="#FFFFCC"><b>Name</b></td> 
            <td width=14.8% align=center bgcolor="#FFFFCC"><b>Country</b></td> 
            <td width=8.1% align=center bgcolor="#FFFFCC"><b>Location</b></td>  
          </tr> 
    <?php 
    $result 
    mysql_query("SELECT * FROM `apps`") or die(mysql_error());  
    while(
    $row mysql_fetch_array($result)){  
    echo 
    "      <tr>";  
    echo 
    "        <td align=center><input type=\"checkbox\" value=\"" $row['id'] . "\" name=\"approve[]\" /></td>";  
    echo 
    "        <td align=center>" $row['Name'] . "</td>"
    echo 
    "        <td align=center>" $row['Country'] . "</td>"
    echo 
    "        <td align=center>" $row['Location'] . "</td>";  
    echo 
    "      </tr>";  

    ?> 
          <tr>  
            <td align=center bgcolor="#FFFFCC" colspan='6'><input type="submit" value="Approve" name="approve_sub" />  <input type="submit" value="Reject" name="reject" /></td> 
          </tr> 
        </table> 
      </center> 
    </form> 
    <?php 
    }

Bookmarks

Posting Permissions

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