can i send "conditional" data to databse?

Applications Forms
#1

hi guys, i have multiple recrods form for table “A” - just updating some records… no insertion…
on after update event i send the data as copy to table “B” - same structure, only one extra field for Autoincrement in the table “B”
this is to create a history update records…

my code is

$insert_table  = 'TABLE_B';
$insert_fields = array( 
      'idintablea' => "'{id}'",
       'field1' => "'{field1}'",
       'field2' => "'{field2}'",
        'field3' => "'{field3}'",
       'field4' => "'{field4}'",
        'field5' => "'{field5}'",
       'field6' => "'{field6}'",
    );
 
$insert_sql = 'INSERT INTO ' . $insert_table
    . ' ('   . implode(', ', array_keys($insert_fields))   . ')'
    . ' VALUES ('    . implode(', ', array_values($insert_fields)) . ')';
 
sc_exec_sql($insert_sql);

i want to to add a new field in table B and call it {fieldx} — and fill that field ONLY if {field1} has value “1” AND {field6} has the value “1”

moreover, the value in fieldx should have unique number if field is filled, or null is field is not field…

why? as this form is used for update only, it will repeatedly update the records and sends a copy to table_b… this will keep a track of field1 when be “1” and i can count it as one record for statistics … even if the other fields are changed during many-updates for each… hope it makes sense and i could explain the “why”

do you think this is doable easily? as if and else? and how to make the fieldx add unique id each time field1=1 and field6=1… i saw something like calculated by database and calculated… but didn’t understand how it works

#2

onAfterUpdate


$fieldx = null;
if({field1} == 1 && {field6} == 1)
{
    sc_lookup(fx,"SELECT MAX(fieldx)+1 FROM table_b");
    if(isset({fx[0][0]})
    {
        $fieldx = {fx[0][0]};
    }
}

Add fieldx to the insert_fields array

‘fieldx’ => $fieldx

jsb

#3

perfect boss, seems will do exactly the required task… however, i seem to be having phrase error, here is what i did


$fieldx = 'null'; // this single quote I added because it was giving me SQL error in line 1 near ')' bla bla, so this solved it after allowing in db that fieldx checkbox Null is ticked...

if({field1} == 1 && {field6} == 0) //this is the required criteria
{
    sc_lookup(fx,"SELECT MAX(fieldx)+1 FROM table_b"); // i checked this fieldx in db i have 0, and 1 values currently.. so it should give 2...
    if(isset({fx[0][0]})
    {
        $fieldx = {fx[0][0]};
    }
}

also added the fieldx to the array, and tested as static variable $fieldx =‘1’; it works, sends the value to db

Parse error: syntax error, unexpected ‘{’ in… line 5919 // which says: $fieldx = $this->fx[0][0];

tried removing the {} from the scary [] things but didn’t work… hmmmm i read for hours about these [0][1] but never could understand them yet :smiley:
what could be wrong

#4

update:

it works without the “isset” what is the consequence?!

this way:
$fieldx = ‘null’;
if({field1} == 1 && {field6} == 0)
{
sc_lookup(fx,“SELECT MAX(fieldx)+1 FROM table_b”);
{
$fieldx = {fx[0][0]};
}
}

perfect, remains small thing that this is a multiple records form… should update the +1 counter for reach record… will try some tests

#5

last update… all ok, just because this this is a multiple records form, the maxid is adding sequentially to all records, even if the update is for another record…

can we modify this code to add the record id and +1 to that record? i have field2 is the record id in table_a that i am sending the history log to table_b {recordid} as field2

can we modify this:

sc_lookup(fx,"SELECT MAX(fieldx)+1 FROM table_b");

so the fieldx will have +1 added to previous MAX(fieldx) OF the recordid

i tried this

sc_lookup(fx,"SELECT field2, MAX(fieldx)+1 FROM table_b whre field2 IN");

didn’t work…

#6

Sorry, overlooked the Multiple Records. :slight_smile:
Now we move things around a bit.

onBeforeUpdateAll



sc_lookup(fx,"SELECT MAX(fieldx)+1 FROM table_b");
if(isset({fx[0][0]})) // see the second ')' at the end, that's the error from before
{
    [newfx] = {fx[0][0]}; //use a global variable, set to Out
}
else
{
    We got an error, do something about here.
}

onAfterUpdate


$fieldx = null;
if({field1} == 1 && {field6} == 1)
{
    $fieldx = [newfx];
    [newfx] ++;
}

Add fieldx to the insert_fields array

‘fieldx’ => $fieldx

hmmmm i read for hours about these [0][1] but never could understand them yet

You’ll get sometime (may be). :slight_smile: :slight_smile:

jsb

#7

Alright, this seems doing that job exactly as imagined… when started this scenario i couldn’t believe it may be possible :slight_smile: for that i’m much grateful :slight_smile:

one luxury code/workaround currently thinking of, as you can see, now we are sending to the log table “table_b” a “null” value and keep sending all updates to table_b as “null” unless fields1 and fields6 mach the criteria hence we add +1 to fieldx

again, based on the recordid {field2} which is always with the same record… can we fill that null with the fieldx of that record instead of the null?

i tried this onAfterUpdate, modified the code a bit to be:



sc_lookup(dx,"SELECT recordid, MAX(fieldx) FROM table_b"); //tried also FROM table_b WHERE recordid IN, and changed recordid/ MAX(fieldx) orders...
if(isset({dx[0][0]}))
{
    $repeated_fieldx = {dx[0][0]};
}

$fieldx =$repeated_fieldx;
if({field1} == 1 && {field6} == 1)
{
    $fieldx = [newfx];
    [newfx] ++;
}

if this is worked i was about to send it to guinnes sworld records, but didn’t… returns in db strange fieldx value (something like recordid- 4)

update

tried this:

sc_lookup(dx,“SELECT MAX(fieldx) FROM table_b where {recordid}=recordid”)

seems working… still testing all aspects, hope title is waiting…

till now works only if there is already recordid in db for this recordid, but it is not a big issue i assume even if i created one record in db for each record!!..

logogwr.jpg

#8

update: tried this:

sc_lookup(dx,“SELECT MAX(fieldx) FROM table_b where {recordid}=recordid”)

seems working… still testing all aspects, this title is waiting :slight_smile:

logogwr.jpg