Add log and update another table

This public forum is for user-to-user discussions of PHPMaker. Note that this is not support forum.
Post Reply
eayvl
User
Posts: 315

Add log and update another table

Post by eayvl »

Hi, I have 2 tables (client, historical)

what I need is, when I add a record in the "historical" table the "client" table is updated, in Master/Detail I have attached the ('client'.'pc_id' = 'historical'.'ph_id')

I do not know how to do it.

// Row Rendered event HISTORICAL TABLE
function Row_Rendered() {
$MyResult = ew_Execute("UPDATE MyTable SET Field1=Value1, Field2=Value2, Field3=Value3 WHERE MyField=XXX");
}

client:
pc_id (primary key)
pc_name
pc_address
pc_phone
pc_concep
pc_importe
pc_file
pc date

historical:
ph_id
ph_concep
ph_importe
ph_file
ph date

Thank you very much.


mobhar
User
Posts: 11660

Post by mobhar »

For such case, do not use "Row_Rendered". Use "Row_Inserted" server event that belongs to "historical" table instead.

Please read "Server Events and Client Scripts" topic from PHPMaker Help menu for more info and example.


eayvl
User
Posts: 315

Post by eayvl »

Hi,in the history table the record is successfully added, but the client table is not updated.

Can someone help me to know where is the error, this is the error that shows:

Add succeeded
×Failed to execute SQL. Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE pc_id =' at line 1

CODE:
// Row Rendered event HISTORICAL TABLE
function Row_Inserted($rsold, &$rsnew) {
//echo "Row Inserted"
$MyResult = ew_Execute("UPDATE client SET pc_concep=".$rsnew["pc_concep"]." WHERE pc_id = ".$rsold["ph_id"]."");
}

Thanks.


arbei
User
Posts: 9288

Post by arbei »

In Row_Inserted Server Event, $rsold["<field>"] do not have value, you can use $rsnew["ph_id"] instead.


eayvl
User
Posts: 315

Post by eayvl »

Hi,

The historical table is not updated,continues the same error, the code is correct?

Add succeeded
×Failed to execute SQL. Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE pc_id =' at line 1

CODE:
// Row Rendered event HISTORICAL TABLE
function Row_Inserted($rsold, &$rsnew) {
//echo "Row Inserted"
$MyResult = ew_Execute("UPDATE client SET pc_concep=".$rsnew["pc_concep"]." WHERE pc_id = ".$rsnew["ph_id"]."");
}

Thanks.


kirondedshem
User
Posts: 642

Post by kirondedshem »

am thinking $rsnew["ph_id"] is not available or its empty at that point.
1.check if ph_id is aalowed on the add form of that table If it is then check that its assigned a value appropriately before suing it.
2.try to var_dump or echo the value of ph_id on row_inserting or row_inserted just so you can clearly see what value it is holding before you trust its vailability in te query.
You can also try to formulate the same query and print it out as an error or success message on row_inserting and return false on purpose just to see how the query will really look with all place holders filled in.


sangnandar
User
Posts: 980

Post by sangnandar »

You need to check the full sql:

  • enable debug in Tools->Advanced Settings.
  • move your code to Row_Inserting().
  • set return FALSE, the code will jammed and expose your full sql.
  • there you can check whether the variable empty or not.

eayvl
User
Posts: 315

Post by eayvl »

Very good,

I moved the code to row_inserting and updated the table client, if i enter numeric data it works fine, if i enter text data it marks this error (Failed to execute SQL. Error: Unknown column 'HELLO' in 'field list').

client:
pc_concep (FIELD TYPE - LONGTEXT)

historical:
ph_concep (FIELD TYPE - LONGTEXT)

++++MY CURRENT CODE++++
// Row Inserting event
function Row_Inserting($rsold, &$rsnew) {
$MyResult = ew_Execute("UPDATE plantilla_client SET pc_concep=".$rsnew["ph_concep"]." WHERE pc_id=".$rsnew["ph_id"]."");
return TRUE;
}

Thanks for the help.


sangnandar
User
Posts: 980

Post by sangnandar »

You write an incorrect logic in your code.
Whatever $MyResult is the Row_Inserting() will ALWAYS return true. So we can't verify whether or not your sql is the cause of error.

Follow these steps:

  1. "Save a Copy" your project. Isolate the case. Remove all code (Page_Load, Row_Rendered, all of them), except the Row_Inserting() code (which we want to check).

  2. I forget what $MyResult value for a valid ew_Execute(), but you can easily check this way:
    $MyResult = ew_Execute("select 1"); //select 1 is absolutely a valid sql.
    $this->setFailureMessage($MyResult);
    return FALSE; // this is purposedly to jammed the code and expose $MyResult value of a valid ew_Execute().

  3. Now you have the valid $MyResult value, change your code into this:
    $MyResult = ew_Execute("your actual sql");
    if ($MyResult == valid$MyResult_value) return TRUE; // replace valid$MyResult_value with value from step 2.
    else {
    $this->setFailureMessage($MyResult);
    return FALSE;
    }

Now you can verify whether or not your sql is the cause of error. If you're already enable Debug from Tools->Advanced Settings you can see the full query fired to db. You'll see something like this:
"SELECT ..... " Error(0) // this is the complete, actual sql, fired to db. Error(0) means no error.

To save time while doing this trial and error, edit your ...add.php directly. Re-generate the project will waste the time. Just once your code is verified to be valid, put them into the project and regenerate them all.


I almost cancel this post when I see something wrong in your code, but I think it's worth to read as a basic trick for debugging purpose so let it be posted.

$MyResult = ew_Execute("UPDATE plantilla_client SET pc_concep=".$rsnew["ph_concep"]." WHERE pc_id=".$rsnew["ph_id"]."");

A text value should be quoted, so it should be like this: ... pc_concep='".$rsnew["ph_concep"]."' WHERE ... // look carefully on the quotes
Assume $rsnew["ph_id"] is integer, it should be like this: ... WHERE pc_id=".$rsnew["ph_id"])
If $rsnew["ph_id"] is text: ... WHERE pc_id='".$rsnew["ph_id"]."'") // look carefully on the quotes


eayvl
User
Posts: 315

Post by eayvl »

Hi, check the box DEBUG and all displays Error (0)

I did some tests with different types of fields:

client:
pc_concep FIELD TYPE LONGTEXT - mark error
pc_importe FIELD TYPE DOUBLE - fields are updated, ok
pc_address FIELD TYPE VARCHAR - mark error

historical:
pc_concep FIELD TYPE LONGTEXT - mark error
pc_importe FIELD TYPE DOUBLE - fields are updated, ok
pc_address FIELD TYPE VARCHAR - mark error

sangnandar you say that:

I almost cancel this post when I see something wrong in your code,

I can not find the error:

++++MY CURRENT CODE++++
// Row Inserting event
function Row_Inserting($rsold, &$rsnew) {
$MyResult = ew_Execute("UPDATE plantilla_client SET pc_concep=".$rsnew["ph_concep"]." WHERE pc_id=".$rsnew["ph_id"]."");
return TRUE;
}

Thansk.


mobhar
User
Posts: 11660

Post by mobhar »

Assume the target updated table name is "client" (not "plantilla_client" as described in your first post), then simply put the following code in "Row_Inserted" of your "historical" table:

ew_Execute("UPDATE client SET pc_concep='".$rsnew["ph_concep"]."' WHERE pc_id=".$rsnew["ph_id"]);

Note, double check your table name and the related field name, and make sure they are already correct in syntax.


sangnandar
User
Posts: 980

Post by sangnandar »

eayvl wrote:
client:
pc_concep FIELD TYPE LONGTEXT - mark error
pc_importe FIELD TYPE DOUBLE - fields are updated, ok
pc_address FIELD TYPE VARCHAR - mark error

historical:
pc_concep FIELD TYPE LONGTEXT - mark error
pc_importe FIELD TYPE DOUBLE - fields are updated, ok
pc_address FIELD TYPE VARCHAR - mark error

It is obvious that you have problem with text type field because you didn't enclose them properly with single-quote.
... SET pc_concep=<put single-quote here>".$rsnew["ph_concep"]."<put single-quote here> WHERE ...
Have a look on this carefully, it's a basic sql stuff.


eayvl
User
Posts: 315

Post by eayvl »

This code does not work, but keep searching.

// Row Inserting event
function Row_Inserting($rsold, &$rsnew) {
$MyResult = ew_Execute("UPDATE plantilla_client SET pc_concep=,".$rsnew["ph_concep"].", WHERE pc_id=".$rsnew["ph_id"]."");
return TRUE;
}

Thansk.


mobhar
User
Posts: 11660

Post by mobhar »

Of course it does not work, because you have incorrect syntax in that SQL, there are two comma characters that unnecessary, and this should raise an SQL error. I suggest you to learn about SQL that related to your Database. Just Google for it if you are not sure about the correct SQL syntax.

In addition, people in this forum could not help you straighforward, since you did not include your complete tables schema from the beginnig of your first post. You just posted the field name without including the field type.

Field type information is very useful to know for us, so we can give you the closest approach. In other words, we do not know exactly until now, whether the "ph_id" in your "historical" table is an auto-increment field or not.


eayvl
User
Posts: 315

Post by eayvl »

I thank you all for help me, if i enter numeric data it works very well, if i enter text data it marks this Error (Failed to execute SQL. Error: Unknown column 'HELLO' in 'field list').

pc_id - ph_id = are related fields

++++MY CURRENT CODE++++
// Row Inserting event
function Row_Inserting($rsold, &$rsnew) {
$MyResult = ew_Execute("UPDATE plantilla_client SET pc_concep=".$rsnew["ph_concep"]." WHERE pc_id=".$rsnew["ph_id"]."");
return TRUE;
}

client:
pc_id int (primary key)
pc_name varchar
pc_address varchar
pc_phone varchar
pc_concep longtext
pc_importe double
pc_file varchar
pc date datetime

historical:
ph_id varchar
ph_concep longtext
ph_importe double
ph_file varchar
ph date datetime

Thanks for the help.


sangnandar
User
Posts: 980

Post by sangnandar »

You can't run this sql part:

pc_id=".$rsnew["ph_id"]."");

Because:
pc_id int (primary key)
But:
ph_id varchar


eayvl
User
Posts: 315

Post by eayvl »

I can not find the solution, still sending error when entering text data.

Change ".pc_id."=".$rsnew["ph_id"]."");
Change the field ph_id varchar to "INT"


mobhar
User
Posts: 11660

Post by mobhar »

Please generate your complete tables schema in .sql file format, so others could help you straightforward.

The tables schema should be started like this:

CREATE TABLE ...

In addition, generate/post some records example in those tables above into .sql format, so others also could reproduce your issue, and copy/paste or post into this forum the content of the .sql file. Those records should be started like this:

INSERT INTO ...
INSERT INTO ...
... and so forth ...


sangnandar
User
Posts: 980

Post by sangnandar »

Copy-paste this code to your Row_Inserting() or Row_Inserted():

ew_Execute("UPDATE client SET pc_concep='".$rsnew["ph_concep"]."' WHERE pc_id=".$rsnew["ph_id"]);


eayvl
User
Posts: 315

Post by eayvl »

Thanks, thanks.


Post Reply