Re: [PHP-DB] Copying Data From One Table To Another Table

> Alice Wei wrote:
>>
>> Hi,
>>    I am trying to accomplish a task of which I have to copy the data 
>> from one table to another. The problem is that whenever I tried to 
>> perform the action of copying the data through this method, although 
>> the data gets copied, it performs reiterative copying that causes my 
>> program and my server to crash.
>>   Here is the code snippet:
>>
>>   #Empty Temporary Array
>>   $tmpHolder = array();
>>   $tmpHolder2 = array();
>>  
>>   #Loop through parts list
>>   foreach ($stringChunk1 As $part) {
>>         #Create a single statement and stuff it in your tmp array
>>     $tmpHolder[]="incidence.name LIKE '%{$part}'";    }
>>   foreach ($stringChunk2 As $part2) {
>>         #Create a single statement and stuff it in your tmp array
>>     $tmpHolder2[]="regions.name LIKE '%{$part2}'";    }
>>
>>   #Join all the parts together, placing an OR between each element
>>   $string3= join(' OR ', $tmpHolder);
>>   $string2= join(' OR ', $tmpHolder2);
>>   $total=$count_chunk1 * $count_chunk2;
>>
>>  //Entry a New Query Entry into scenarios table
>>    $query3="INSERT INTO scenarios VALUES('$scenario_name')";
>>    $result3=mssql_query($query3) or die ("The query has failed");
>>      #Search for the Regions and Incidence ID     $query_both="SELECT 
>> incidence.ID,regions.ID from incidence,regions WHERE ($string3)AND 
>> ($string2)";    $result_both=mssql_query($query_both) or die ("The 
>> query has failed"); 
>>   while($row_both = mssql_fetch_array($result_both)) {
>>
>>    $incidence_id= $row_both[0];
>>    $region_id= $row_both[1];
>>
>>  //Enter entry into scenario elements table
>> //  $query14="INSERT INTO 
>> scenario_elements(region_id,incidence_id,market,number_sales,number_defer,customer_satisfaction) 
>>
>> //  SELECT 
>> region_id,incidence_id,market,number_sales,number_defer,customer_satisfaction 
>> FROM scenario_elements_2,regions,incidence WHERE $string2 AND $string3";
>> //  $result14=mssql_query($query14) or die ("The query has failed");
>>  
>> I have to comment out the code snippet above because it creates 
>> something like 30000 entries to the database. Could anyone please 
>> tell me if this is the error I have from my PHP, or is it from my SQL 
>> statement? How would I go about fixing this issue? 
>
> An insert into select doesn't need to use a loop, you can use just one 
> query:
>
> insert into scenarios(field1, field2) select field1, field2 from 
> other_table where ....
>
> the db will (internally) loop over the results and do the work.
>

http://dev.mysql.com/doc/refman/5.0/en/insert-select.html


Not to mention that you don't even need PHP for that. You just need to 
open a client and make the query.

-- 
Thodoris