Move Data from 1 Table to Another

[smithster]

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

[codeexploiter]

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:

 $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.

[smithster]

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?.....

$sqlStmt = "INSERT INTO TABLE2 VALUES(".$row[4].")";

I hope this comes across clear!!

[smithster]

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

[codeexploiter]

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.

[smithster]

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.

[smithster]

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.....

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 
}