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 3 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: [PHP-DB] Copying Data From One Table To Another Table
$result3=mssql_query($query3) or die (The query has failed); snip 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: [PHP-DB] Copying Data From One Table To Another Table
$result3=mssql_query($query3) or die (The query has failed); snip 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
RE: [PHP-DB] Copying Data From One Table To Another Table
Hi, Thanks for the trick. I took the query to copy the data outside of the loop, and it is now working! Alice Date: Fri, 14 Nov 2008 14:07:27 +0200 From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: php-db@lists.php.net; [EMAIL PROTECTED] Subject: Re: [PHP-DB] Copying Data From One Table To Another Table $result3=mssql_query($query3) or die (The query has failed); snip 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 _ Use Messenger to talk to your IM friends, even those on Yahoo! http://ideas.live.com/programpage.aspx?versionId=7adb59de-a857-45ba-81cc-685ee3e858fe
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 3 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: [PHP-DB] copying data
yes, if your server is windows based. Create a odbc system dsn to the access file on your server, then connect to that dsn. You can then pull data from it, connect to mysql and insert the data. If I remember correctly, you had wanted your users to upload the mdb file for you to extract the data. This may be tricky as I am not sure if you can bait and switch the file from under the odbc connections nose. Cheers Jason -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 12, 2002 10:33 AM To: PHP-DB Subject: [PHP-DB] copying data I asked a question sort of alone these lines the other day so please forgive me if it sounds familiar... If I have a microsoft access file .mdb on my server, can I use a php page to move the data from the file to a mysql db? Thanks, Eddie -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
