2. PHPRunner General questions
  3. Assign one field to the same as another field
This topic is locked

Assign one field to the same as another field

 		5/19/2010 8:53:49 AM
PHPRunner General questions
S
 swanside author

In my tables as in thge below SQL, I have sites.LocationId AS LocationId1, In my job.LocationId this is empty, now apart from going into every record to edit the job.LocationId, to be the same as sites.LocationId AS LocationId1, is there a script I could use to do this?
Cheers

Paul.

SELECT

job.Order_Date,

job.Job_No,

job.CustomerId,

job.Order_Site_Address,

job.Job_Finished,

job.Customer_Name,

job.LocationId,

job.Payment_Received,

job.Invoice_Printed,

sites.LocationId AS LocationId1

FROM job

INNER JOIN sites ON job.Order_Site_Address = sites.site_address
A
 		ann 5/19/2010

Hi,
you can edit SQL query in the following way:

job.Customer_Name,

if (jobs.LocationId=0, sites.LocationId, jobs.LocationId) as LocationId,

job.Payment_Received,
S
 		swanside author 5/19/2010



Hi,
you can edit SQL query in the following way:



Thanks Ann, But, DO I put this query in my Edit SQL Query Page? I did that and get an error?

Thanks

Paul.
S
 		swanside author 5/19/2010

Sorry It was the s on jobs.
Thanks Very Much

Paul
S
 		swanside author 5/19/2010

Hi Ann.

I entered this

SELECT

job.Order_Date,

job.Job_No,

job.CustomerId,

job.Order_Site_Address,

job.Job_Finished,

job.Customer_Name,

job.Payment_Received,

if(job.LocationId="", sites.LocationId, job.LocationId) AS LocationId,

job.Invoice_Printed

FROM job

JOIN sites ON job.Order_Site_Address = sites.site_address
But it does not add anything, so I did it in PHPMyAdmin and it just shows the results, but does not change the LocationId field where they are NULL to the correct ID?

Cheers

Paul
A
 		ann 5/20/2010

Paul,
to save job.LocationId into the database use BeforeAdd/BeforeEdit events on the Events tab of the job table. Here is a sample code:

global $conn,$strTableName;

if (!$values["LocationId"])

{

$sql = "SELECT LocationId FROM sities WHERE site_address='".$values["Order_Site_Address"]."'";

$rs=CustomQuery($sql);

$data=db_fetch_array($rs);

$values["LocationId"]=$data["LocationId"];

}
S
 		swanside author 5/20/2010



Paul,
to save job.LocationId into the database use BeforeAdd/BeforeEdit events on the Events tab of the job table. Here is a sample code:

global $conn,$strTableName;

if (!$values["LocationId"])

{

$sql = "SELECT LocationId FROM sities WHERE site_address='".$values["Order_Site_Address"]."'";

$rs=CustomQuery($sql);

$data=db_fetch_array($rs);

$values["LocationId"]=$data["LocationId"];

}



Thanks Ann, WIll try that tomorrow, Headache time now.

Cheers

Paul.