How to Copy Value of A Column Before Committing the Change?

[mohamed]

Hello,

I am trying to Copy or Save the value of a column before Updatting or Committing the value change IF the Value is "0" Zero, Before Update.
- Table Name: Budget
- Column Name: Estimated_Cost
- Name of Column to Save the data: Original_Estimated_Cost
- condition: entering ZERO () in column Estimated_Cost

so, for instance if value in "Estimated_Cost" is 1000
and the user change the Value of the Column "Estimated_Cost" to ZERO (0)
then click on SAVE

The function Before Update, should copy or update first the Value 1000 to Original_Estimated_Cost
then it updates the column Estimated_Cost to Zero (0)


Thank you in advance...

[pbottcher]

Hi,

assuming the your primary key for the table Budget is id. You can try to add this to the before_update hook:

Code: Select all

if ($data['Estimated_Cost'] == 0) {
    $eo=array();
    $sql="UPDATE Budget set Original_Estimated_Cost = Estimated_Cost where id={$selectedid}";
    sqlvalue($sql,$eo);
}

Depended on you use-case you might also use the

Code: Select all

if(empty($data['Estimated_Cost'])) {

instead.

[mohamed]

Hi pböttcher
& Thank you..

I have tried the code you have shared, and verified that the condition in IF Statment is met, but for some reason the UPDATE is not taking place, i.e., the SET command is not copying the value from Estimated_Cost to Original_Estimate !!

Code: Select all

  if ($data['Estimated_Cost'] == 0) {
	//sleep(5);
	$eo=array();
	$sql="UPDATE Budget SET Original_Estimate  = Estimated_Cost WHERE id={$selectedid}";
	sqlvalue($sql,$eo);
	}

[pbottcher]

Hi,
sorry for the confusion.

Try

Code: Select all

  if ($data['Estimated_Cost'] == 0) {
	//sleep(5);
	$eo=array();
	$sql="UPDATE Budget SET Original_Estimate  = Estimated_Cost WHERE id={$data['selectedID']}";
	sqlvalue($sql,$eo);
	}

[mohamed]

Hi,
Not at all .. and thanks again ..

Unfortunately, still not copying the data for some reason when I enter ZERO in Estimated_Cost

I have created a new field "Estimated" with the new becasue Original_Estimate is a Calculated Field and I don't want to make changes to it

Is there a way to view the Errors in a log file ? to view the errors, if any.

My code as follows:

Code: Select all

if ($data['Estimated_Cost'] == 0) {
  sleep(5);
  $eo = array();
  $sql = "UPDATE Budget SET Estimated  = Estimated_Cost WHERE id={$data['selectedID']}";
  sqlvalue($sql,$eo);			}

Thank you again...

[pbottcher]

Hi,

you can output the variable to a logfile and see what is written there. like:

Code: Select all

 if ($data['Estimated_Cost'] == 0) {
  sleep(5);
  $eo = array();
  $sql = "UPDATE Budget SET Estimated  = Estimated_Cost WHERE id={$data['selectedID']}";
  file_put_contents(__DIR__."/logfile.txt",print_r($sql,true).PHP_EOL,FILE_APPEND);
  sqlvalue($sql,$eo);	
  file_put_contents(__DIR__."/logfile.txt",print_r($eo,true).PHP_EOL,FILE_APPEND);
}

in the hooks dir you should find the logfile.txt. Look as the UPDATE statement and the array output to identify possible error.

[mohamed]

Hi,
Thank you again ..

I am getting this in the Log File

UPDATE Budget SET Estimated = Estimated_Cost WHERE id=47
Table &#039;budget.budget&#039; doesn&#039;t exist<pre class="ltr">Query:
UPDATE Budget SET Estimated = Estimated_Cost WHERE id=47</pre><p><i class="text-right">The above info is displayed because you are currently signed in as the super admin. Other users won't see this.</i></p><p><a href="http://localhost/budget/admin/pageRebui ... s.php">You could try fixing this issue from the Admin Area : Utilities menu : View/Rebuild Fields.</a></p>


From my side no clue yet ! ..

I am attaching the Fields List from AppGini and MySQL

[pbottcher]

Hi,

the error message states:

"Table budget.budget doesn't exist"

so I would guess that your database is named budget, but how is your tablename? You need to provide the correct tablename in the sql statement

best

[mohamed]

Hi,

Sorry;
Database: Budget
Table Name: Network_2024

Thanks

[mohamed]

Hi pböttcher,
Thank you for the hint.

I have corrected and change the table name to be Network_2024

But when I update the record and enter Zero in Estimated_Cost and then hit SAVE two things happening:
1. I get Fatal Error (errors down)
2. The record get updated Successfully !!,

* and I am not getting errors in LogFile.txt, just the normal/successful UPDATE command:
UPDATE Network_2024 SET Estimated = Estimated_Cost WHERE id=47


The Fatal Error:
Fatal error: Uncaught TypeError: mysqli_fetch_row(): Argument #1 ($result) must be of type mysqli_result, true given in C:\inetpub\wwwroot\budget\db.php:65 Stack trace: #0 C:\inetpub\wwwroot\budget\db.php(65): mysqli_fetch_row(true) #1 C:\inetpub\wwwroot\budget\admin\incFunctions.php(536): db_fetch_row(true) #2 C:\inetpub\wwwroot\budget\hooks\Network_2024.php(133): sqlValue('UPDATE Network_...', Array) #3 C:\inetpub\wwwroot\budget\Network_2024_dml.php(245): Network_2024_before_update(Array, Array, Array) #4 C:\inetpub\wwwroot\budget\datalist.php(339): Network_2024_update('47', '') #5 C:\inetpub\wwwroot\budget\Network_2024_view.php(180): DataList->Render() #6 {main} thrown in C:\inetpub\wwwroot\budget\db.php on line 65


-- The code I am using is:

Code: Select all

	if ($data['Estimated_Cost'] == 0) {
		//sleep(5);
		$eo = array();
		$sql = "UPDATE Network_2024 SET Estimated  = Estimated_Cost WHERE id={$data['selectedID']}";
		file_put_contents(__DIR__."/logfile.txt",print_r($sql,true).PHP_EOL,FILE_APPEND);
		sqlvalue($sql,$eo);	
		file_put_contents(__DIR__."/logfile.txt",print_r($eo,true).PHP_EOL,FILE_APPEND);
	  }

==> Not sure why AppGini code does not like the UPDATE command, becasue I am facing the same when I try the UPDATE command in a Calculated Field

[pbottcher]

Hi,

try

sql($sql,$eo);

instead of sqlvalue($sql,$eo);

[mohamed]

Hi pböttcher
Thank you...

when using
sql($sql,$eo);

I am not getting Fatal Error BUT it does not Update the target 'Estimated' field

I am getting sort of empty Array issue message in the LogFile.txt
UPDATE Network_2024 SET Estimated = Estimated_Cost WHERE id=47
Array
(
)


My code now:

Code: Select all

			if ($data['Estimated_Cost'] == 0) {
				//sleep(5);
				$eo = array();
				$sql = "UPDATE Network_2024 SET Estimated  = Estimated_Cost WHERE id={$data['selectedID']}";
				file_put_contents(__DIR__."/logfile.txt",print_r($sql,true).PHP_EOL,FILE_APPEND);
				//sqlvalue($sql,$eo);	
				sql($sql,$eo);
				file_put_contents(__DIR__."/logfile.txt",print_r($eo,true).PHP_EOL,FILE_APPEND);
			  }

[pbottcher]

Hi, actually the code looks good,
how did you set the Estimated field in AppGini? It must be readonly.

Other than that, look into the DB and try the statement within the DB directly.

[mohamed]

Thank you millions.

The problem solved by setting the field Estimated to be Read Only.


Thank you again