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
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
