Copying Data From One Table To Another Table

--_31f5e03f-0393-480c-b8fb-5418eac00586_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable



Hi,

I am trying to accomplish a task of which I have to copy the data from o=
ne table to another. The problem is that whenever I tried to perform the ac=
tion of copying the data through this method=2C although the data gets copi=
ed=2C it performs reiterative copying that causes my program and my server =
to crash.=20

Here is the code snippet:

#Empty Temporary Array
$tmpHolder =3D array()=3B
$tmpHolder2 =3D array()=3B
=20
#Loop through parts list
foreach ($stringChunk1 As $part) {
=20
#Create a single statement and stuff it in your tmp array
$tmpHolder[]=3D"incidence.name LIKE '%{$part}'"=3B =20
}
foreach ($stringChunk2 As $part2) {
=20
#Create a single statement and stuff it in your tmp array
$tmpHolder2[]=3D"regions.name LIKE '%{$part2}'"=3B =20
}

#Join all the parts together=2C placing an OR between each element
$string3=3D join(' OR '=2C $tmpHolder)=3B
$string2=3D join(' OR '=2C $tmpHolder2)=3B
$total=3D$count_chunk1 * $count_chunk2=3B

//Entry a New Query Entry into scenarios table
$query3=3D"INSERT INTO scenarios VALUES('$scenario_name')"=3B
$result3=3Dmssql_query($query3) or die ("The query has failed")=3B
=20
#Search for the Regions and Incidence ID =20
$query_both=3D"SELECT incidence.ID=2Cregions.ID from incidence=2Cregions=
WHERE ($string3)AND ($string2)";
$result_both=3Dmssql_query($query_both) or die ("The query has failed")=
=3B =20

while($row_both =3D mssql_fetch_array($result_both)) {

$incidence_id=3D $row_both[0]=3B
$region_id=3D $row_both[1]=3B

//Enter entry into scenario elements table
// $query14=3D"INSERT INTO scenario_elements(region_id=2Cincidence_id=2Cma=
rket=2Cnumber_sales=2Cnumber_defer=2Ccustomer_satisfaction)
// SELECT region_id=2Cincidence_id=2Cmarket=2Cnumber_sales=2Cnumber_de fer=
=2Ccustomer_satisfaction FROM scenario_elements_2=2Cregions=2Cincidence WHE=
RE $string2 AND $string3"=3B
// $result14=3Dmssql_query($query14) or die ("The query has failed")=3B
=20

I have to comment out the code snippet above because it creates something l=
ike 30000 entries to the database. Could anyone please tell me if this is t=
he error I have from my PHP=2C or is it from my SQL statement? How would I =
go about fixing this issue?=20

Thanks in advance.=20

Alice


____________________________________________________________ _____
All-in-one security and maintenance for your PC.=A0 Get a free 90-day trial=
!
http://www.windowsonecare.com/purchase/trial.aspx?sc_cid=3Dw l_wlmail=

--_31f5e03f-0393-480c-b8fb-5418eac00586_--

Re: 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,cust omer_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/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: 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,cust omer_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

Re: Copying Data From One Table To Another Table

>>> $result3=mssql_query($query3) or die ("The query has failed");



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

Ah - the OP isn't using mysql, it's ms-sql.


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

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Copying Data From One Table To Another Table

--------------020504000301040008000805
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


>>>> $result3=mssql_query($query3) or die ("The query has failed");
>>>>
>
>
>
>
>> http://dev.mysql.com/doc/refman/5.0/en/insert-select.html
>>
>
> Ah - the OP isn't using mysql, it's ms-sql.
>
>
>

Oups didn't notice that :-) .

--
Thodoris


--------------020504000301040008000805--

RE: Copying Data From One Table To Another Table

--_565f1393-1f4c-4071-8244-723f685df6c2_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable



Hi,
=20
Thanks for the trick. I took the query to copy the data outside of the lo=
op=2C and it is now working!

Alice


> Date: Fri=2C 14 Nov 2008 14:07:27 +0200
> From: tgol@kinetix.gr
> To: dmagick@gmail.com
> CC: php-db@lists.php.net=3B ajwei@alumni.iu.edu
> Subject: Re: [PHP-DB] Copying Data From One Table To Another Table
>=20
>=20
> >>>> $result3=3Dmssql_query($query3) or die ("The query has failed")=3B
> >>>> =20
> >
> >
> >
> > =20
> >> http://dev.mysql.com/doc/refman/5.0/en/insert-select.html
> >> =20
> >
> > Ah - the OP isn't using mysql=2C it's ms-sql.
> >
> >
> > =20
>=20
> Oups didn't notice that :-) .
>=20
> --=20
> Thodoris
>=20

____________________________________________________________ _____
Use Messenger to talk to your IM friends=2C even those on Yahoo!
http://ideas.live.com/programpage.aspx?versionId=3D7adb59de- a857-45ba-81cc-=
685ee3e858fe=

--_565f1393-1f4c-4071-8244-723f685df6c2_--