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.

-- 
Postgresql & php tutorials
http://www.designmagick.com/