2. PHPRunner General questions
  3. Update main table from another table
This topic is locked

Update main table from another table

 		8/4/2008 2:12:10 AM
PHPRunner General questions
N
 nix386 author

Hi, just wondering if someone can point me in the right direction;
I have two tables tb_main and tb_locations
In tb_main I have various fields some being location information like suburb, postcode, region and state and currently I have over 6500 records which have only suburb data. The tb_locations table however contains all the complete postcode, suburb and regional info (over 15000 records).
What I am trying to do is update all the data in tb_main using the complete data from tb_locations....sounds easy yeah.
Using PHPRunner 4.2 MYSQL 5.xx
In PHPR I have added the following code to tb_main List Page:Before Process but for some eason it just takes forever and then the browser times out..

I am sure I have used something simillar in the past with no issues...Can anyone see what I might be doing wrong?

global $conn;

$str = "select * from tb_locations";

$rs = db_query($str,$conn);

while ($data = db_fetch_array($rs))

{

$strUpdate = "update tb_main set region='".$data["region"]."',postcode=".$data["postcode"]." where suburb='".$data["suburb"]."'";

db_exec($strUpdate,$conn);

}
J
 		Jane 8/4/2008

Hi,
It's difficult to tell you what's happening without seeing actual files.
Please publish your project on Demo Account and send to support@xlinesoft.com a URL to your pages along with instructions on reproducing this error.

I'll find what's wrong with your project inspecting it at Demo account site.
T
 		thesofa 8/4/2008

Hi, just wondering if someone can point me in the right direction;

I have two tables tb_main and tb_locations
In tb_main I have various fields some being location information like suburb, postcode, region and state and currently I have over 6500 records which have only suburb data. The tb_locations table however contains all the complete postcode, suburb and regional info (over 15000 records).
What I am trying to do is update all the data in tb_main using the complete data from tb_locations....sounds easy yeah.
Using PHPRunner 4.2 MYSQL 5.xx
In PHPR I have added the following code to tb_main List Page:Before Process but for some eason it just takes forever and then the browser times out..

I am sure I have used something simillar in the past with no issues...Can anyone see what I might be doing wrong?

global $conn;

$str = "select * from tb_locations";

$rs = db_query($str,$conn);

while ($data = db_fetch_array($rs))

{

$strUpdate = "update tb_main set region='".$data["region"]."',postcode=".$data["postcode"]." where suburb='".$data["suburb"]."'";

db_exec($strUpdate,$conn);

}



Well one possibility that might be scuppering things is that you are trying to update with using the suburb as a link.

I would think that there will be more than one postcode within a suburb, so you are tring to update without a vlaid join.

Try adding both tables in the query designer editor page.

Drag the suburb field from thew data table and drop it on the suburb field in the other table, this joins the tables.

now put a tick in the suburb from the main table and the postcode field from the data table and run the query (Results tab)

this will show if you habve a many to many link, if you have you cannot do what you ask of SQL as there is more than one answer to the question the query is asking.

I hope I have explained this better than it seems.
N
 		nix386 author 8/4/2008

Yes and thank you for your explanation the sofa! It's possible that there is more than one postcode in my suburbs list, although the naming for each should be somewhat unique... I did groom the location data a little but something may of have slipped past. I'll look into this and post back...thanks as well Jane for the support on such a trivial sql issue...
Cheers, Nick



Well one possibility that might be scuppering things is that you are trying to update with using the suburb as a link.

I would think that there will be more than one postcode within a suburb, so you are tring to update without a vlaid join.

Try adding both tables in the query designer editor page.

Drag the suburb field from thew data table and drop it on the suburb field in the other table, this joins the tables.

now put a tick in the suburb from the main table and the postcode field from the data table and run the query (Results tab)

this will show if you habve a many to many link, if you have you cannot do what you ask of SQL as there is more than one answer to the question the query is asking.

I hope I have explained this better than it seems.