how to update the old record?

siriok · Post by **siriok** » Thu May 15, 2014 3:43 pm

Example:. i had 2 table
table 1 (master) -pid ,name ,surname::pid is a primary
table 2 (detail) -no, pid ,update,book,::: no is primary and autoincrement // "update" is a checkbox and default is 1(checked)

then i want to ::after add a new record(detail table) --> field "update" at a past record turn in to "null"value in the same detail table
what code is it:: i tried use a row updating with $rsold["update"] = "null" it don't work
and try to use :

ew_Execute("UPDATE table2 SET table2.update = '" . ."' WHERE table2.pid = ". $rsnew["pid"]);

not work too??
Anyone can help me??? for the true code
regards

scs · Post by **scs** » Thu May 15, 2014 5:04 pm

1) you 'update' field must set to allow null even your default value is '1'

2) $rsnew["update"] = null (not $rsold["update"])

siriok · Post by **siriok** » Fri May 16, 2014 9:43 am

hi.scs -thk for help me
but when i take a code in row_inserting ,row_updating like this
function Row_Inserting($rsold, &$rsnew) {
$rsnew["update"] = null (not $rsold["update"]);

return TRUE;

}

when run the page it can't open and show:
Parse error: syntax error, unexpected T_VARIABLE in /home/lmwcc/domains/lmwcc.com/public_html/ncdlp/example/orderinfo.php on line 702

so.i'll mistake you again. goal of my code are for
after input a new record(record2)- (detail table) -update field is checked(valued = 1) -default valued
and i' want the old record (record1) the field update that used to checked then show to null value(remove checkpoint)
so can you help me again. or anyone pls.....

danielc · Post by **danielc** » Fri May 16, 2014 10:10 am

You need to get the latest record id before insert in Row_Inserting server event:
$lastid = ew_ExecuteScalar("SELECT <id> FROM <yourtable> ORDER BY <id> DESC"); // replace <id>, <yourtable> with your own name

Then execute your update statement to this id.

siriok wrote:
ew_Execute("UPDATE table2 SET table2.update = null WHERE table2.pid = ". $lastid);

siriok · Post by **siriok** » Fri May 16, 2014 10:39 am

thank daniel:
i try this
function Row_Inserting($rsold, &$rsnew) {
$lastid = ew_ExecuteScalar("SELECT no FROM order ORDER BY no DESC WHERE order.pid = ". $rsnew["pid"]);
ew_Execute("UPDATE order SET order.update = null WHERE order.pid = ". $lastid);
return TRUE;
}
but when run page it say:
Failed to execute SQL. Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order ORDER BY no DESC WHERE order.pid = 22222' at line 1

and the update field in last record of same pid don't =null

more any idea?? or my code mistake?? ......pls......

danielc · Post by **danielc** » Fri May 16, 2014 10:51 am

siriok wrote:
$lastid = ew_ExecuteScalar("SELECT no FROM order ORDER BY no DESC WHERE order.pid
= ". $rsnew["pid"]);

There is no need for "WHERE order.pid = ". $rsnew["pid"]". See my code again. ew_ExecuteScalar will get the 1st colomn value (that is your last id).

siriok · Post by **siriok** » Fri May 16, 2014 11:01 am

thk again;
function Row_Inserting($rsold, &$rsnew) {
$lastid = ew_ExecuteScalar("SELECT no FROM order ORDER BY no DESC ");
ew_Execute("UPDATE order SET order.update = null WHERE order.pid = ". $lastid);
return TRUE;
}
but nothing change and say:
Failed to execute SQL. Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order ORDER BY no DESC' at line 1
Failed to execute SQL. Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order SET order.update = null WHERE order.pid =' at line 1

what wrong.??

mobhar · Post by **mobhar** » Fri May 16, 2014 11:18 am

Try this:
$lastid = ew_ExecuteScalar("SELECT no FROM order ORDER BY no DESC");
ew_Execute("UPDATE order SET update = null WHERE pid = ". $lastid);

danielc · Post by **danielc** » Fri May 16, 2014 11:27 am

order is keyword in MySQL. You need to use order instead of order like what Mobhar suggested.

Also, $lastid is based on no. You can not assign it to pid. Check your logic.

siriok · Post by **siriok** » Fri May 16, 2014 12:02 pm

thx. danielc ,mobhar....now it work ...great
but i' had a problem --> can it code for record in specific detail table?? (specific for pid)

because when i add new record in new pid (master table) and add new record in detail table it change the field 'update ' in other pid (it detele the field 'no' des)
can i add code WHERE order.pid = ". $rsnew["pid"]); in ew_executescalar - i try this but get warning again
so how can i do.? pls

scs · Post by **scs** » Fri May 16, 2014 12:41 pm

I think you better check the sequence for Select syntax in MySQL.

Google for 'mysql select'

siriok · Post by **siriok** » Fri May 16, 2014 1:35 pm

or can i' use page load () ->>add/copy page to call the function like this:
ew_Execute("UPDATE order SET update = null WHERE pid = ". $rsnew["pid"]);

but when i click add button ..it said:
Failed to execute SQL. Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

or use startup script in client??? how can i do? pls......

mobhar · Post by **mobhar** » Fri May 16, 2014 1:50 pm

Have you tried to put the code in "Row_Inserted" server event?

siriok · Post by **siriok** » Fri May 16, 2014 2:44 pm

Yeah!!!
i got it that my goal i put this in row_inserting
ew_Execute("UPDATE order SET update = null WHERE pid = ". $rsnew["pid"]);
thank everyone to help me and get me more knowledge
regards