2. PHPRunner General questions
  3. [SOLVED]  SQL Code efficiency question
This topic is locked
[SOLVED]

 SQL Code efficiency question

 		8/7/2019 10:22:33 AM
PHPRunner General questions
D
 david22585 author

I'm just an amateur guy who is winging using PHPRunner to make a site for our condo association. I've found a few different ways to do what I need to do, and I was wondering about the efficiency of a few different ways to update a joined/different table:
Method 1:

DB::Query("update accounts_data set linked='1' where id = " . $values["current_owner"] . "" );

DB::Query("update accounts_data set unit=".$values["unit"]." where id = ".$values["current_owner"]."" );

DB::Query("update accounts_data set building=".$values["building"]." where id = ".$values["current_owner"]."" );

DB::Query("update accounts_data set address=".$values['unit']." where id = ".$values["current_owner"]."" );

DB::Query("update accounts_data set moveoutdate='' where id = ".$values["current_owner"]."" );


Method 2:

DB::Query("update accounts_data set linked='1', unit=".$values["unit"].", building=".$values["building"].", address=".$values['unit'].", moveoutdate='' where id = " . $values["current_owner"] . "" );


Method 3:



global $dal;

$tblDetail = $dal->Table("accounts_data");

$tblDetail->Param["id"] = $values["current_owner"];

$tblDetail->Value["ownername"] = $values["ownername"];

$tblDetail->Value["email"] = $values["email"];

$tblDetail->Update();

unset($values["ownername"], $values["email"]);


I used method 1 for initial testing but use method 2 for the final product. I'm guessing method 2 is less resource intensive, but it's also not as clean to look at when dealing with testing in a way.
For method 3, I use that to update a joined table, but I was wondering if something like that was more efficiency in terms of resource usage than method 2?
Thanks

-David
mbintex 8/7/2019

According to the manual, DAL is deprecated
https://xlinesoft.com/phprunner/docs/about_database_api.htm
So you better use the database API
Sergey Kornilov admin 8/7/2019

Method 4 is the best:

https://xlinesoft.com/phprunner/docs/db_update.htm
D
 		david22585 author 8/7/2019

The reason I used the old DAL is because I was getting errors with the database API and couldn't figure out what the issue was. I'm getting the following error:
<<< Record was NOT edited >>>
Unknown column 'ownername' in 'field list'
The record is updating the joined table though. This is on table a with a joined table to table b. When I update the ownername in table b, I get the error even though the value does update. Here is the code I was using that produces the error:

$data = array();

$where = $values["current_owner"];

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

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

DB::Update("accounts_data", $data, "id=$where" );
Sergey Kornilov admin 8/7/2019

All those methods are identical and produce the same SQL query at the end. It is just the matter of using the recommended method that is easier to use and is supported.
Do not forget to do unset(), it needs to be done at the end of either method.
D
 		david22585 author 8/8/2019



All those methods are identical and produce the same SQL query at the end. It is just the matter of using the recommended method that is easier to use and is supported.
Do not forget to do unset(), it needs to be done at the end of either method.


That was it, just needed to add that to the end to make it work properly. Thanks!
Working code for others that may fall into the same trap as me:



$data = array();

$where = $values["current_owner"];

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

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

DB::Update("accounts_data", $data, "id=$where" );

unset($values["ownername"], $values["email"]);