Update multiple fields using a blank app

Applications Blank
#1

HI There,

I have the following code that updates multiple fields in a form. this works fine.

Now i don’t want to start a form but use a blank app to update the multiple fields from the table and save it in the table based on a where. but i can’t figure out how to do this for multiple fields at the same time.

this is the code:

// SQL statement parameters
$update_table = ‘ILP_rooster’; // Table name
$update_where = “wk_id = ‘{wk_id}’”; // Where clause
$update_fields = array( // Field list, add as many as needed
“wk_ex_opm = ‘CONCEPT #{wk_eduarte_lesnr} | {wk_eduarte_lesnaam}
LOC:$lokaal
D1:$doc1 | D2:$doc2’”,

);

// Update record
$update_sql = ‘UPDATE ’ . $update_table
. ’ SET ’ . implode(’, ', $update_fields)
. ’ WHERE ’ . $update_where;
sc_exec_sql($update_sql);<

the problem is that the field taht i wan’t to update is a merge field containing values from the same record. i could use something like…

sc_exec_sql("UPDATE products SET TotalValue = $vl_discount WHERE ProductID = {ProductID}");<

but how can i get the values form that same record before my update

#2

Not sure to understand your problem

More information will is needed, a real example will help

#3

the script is in a form in onrecord.

in the form a field is merged with values form the same record

wk_ex_opm = ‘CONCEPT #{wk_eduarte_lesnr} | {wk_eduarte_lesnaam}
LOC:$lokaal

every row where that matches the WHERE clausule is updated.

Now i wan’t the same in a blank app. but i can’t figure out how to update a table based on a WHERE clausule and merge the field values from the same record in a row.

with:

sc_exec_sql(“UPDATE ILP_rooster SET wk_ex_opm = $value WHERE wk_id = {wk_id}”);

i can update the record, but not merge the field values like i did in the form

#4

It’s still not 100% clear for me

Is that what you are looking for

$SQL = "UPDATE ILP_rooster SET wk_ex_opm = concat('CONCEPT #',wk_eduarte_lesnr,'|',wk_eduarte_lesnaam) WHERE wk_id = {wk_id};
sc_exec_sql($SQL);

What is LOC:$lokall ?

#5

Hi @jlboutin60,

We’re getting somewhere, thanks for your great help. so for the $lokaal
that’s what’s my problem is. sorry for the long way to get there.

so here is my code

// SQL statement parameters
$update_table = ‘ILP_rooster’; // Table name
$update_where = “ev_id = ‘[ev_id]’”; // Where clause
$update_fields = array( // Field list, add as many as needed
“wk_ex_opm = concat(‘CONCEPT #’,wk_eduarte_lesnr,’|’,wk_eduarte_lesnaam, $lokaal”,

);

// Update record
$update_sql = ‘UPDATE ’ . $update_table
. ’ SET ’ . implode(’, ', $update_fields)
. ’ WHERE ’ . $update_where;
sc_exec_sql($update_sql);

this works for now. except the $lokaal, this is an id (lk_id) that is saved in the table and referred to another table where the actual value is. In the form i do a lookup. in a blank app ???

lookup like this
// Check for record
$check_sql = “SELECT lk_naam_code”
. " FROM event_lokaal"
. " WHERE lk_id = ‘" . {lk_id} . "’";
sc_lookup(rs, $check_sql);

if (isset({rs[0][0]})) // Row found
{
$lokaal = {rs[0][0]};

}
else // No row found
{
$lokaal = ‘-’;

}

So the concat did the job for the fields in the table, and now i have to pull some value form another table to insert in the concat

#6 
// SQL statement parameters
$update_table = 'ILP_rooster'; // Table name
$update_where = "ev_id = '[ev_id]'"; // Where clause
$update_fields = array( // Field list, add as many as needed
"wk_ex_opm = concat('CONCEPT #',wk_eduarte_lesnr,'|',wk_eduarte_lesnaam, 
  IFNULL((SELECT lk_namm_code FROM event_lokaal WHERE lk_id = {lk_id}),'-')",
);
#7

@jlboutin60, i altered you’re code a bit and now it works like a charm. even got a count in it.
the brackets don’t work i changed it to table.field
thanks again. i use this code to alter the subtitle in a calendar with actual values from a grid.

// SQL statement parameters

$update_table = ‘ILP_rooster’; // Table name
$update_where = “ev_id = ‘’”; // Where clause
$update_fields = array( // Field list, add as many as needed
“wk_ex_opm = concat(‘concept #’,wk_eduarte_lesnr,’|’,wk_eduarte_lesnaam,
‘
LOC: ‘,
IFNULL((SELECT lk_naam_code FROM event_lokaal WHERE lk_id = ILP_rooster.lk_id),’-’),
‘
D1: ‘,
IFNULL((SELECT name FROM sec_users WHERE st_id = ILP_rooster.wk_st_id),’-’),
’ D2: ‘,
IFNULL((SELECT name FROM sec_users WHERE st_id = ILP_rooster.wk_st_id_2),’-’),
‘
Aanmeldingen: ‘,
IFNULL((SELECT count(*) AS COUNT_0 FROM ILP_rooster_deelnemer WHERE wk_id = ILP_rooster.wk_id),’-’),
‘/’,
wk_max_d
)”,

);

// Update record
$update_sql = ‘UPDATE ’ . $update_table
. ’ SET ’ . implode(’, ', $update_fields)
. ’ WHERE ’ . $update_where;
sc_exec_sql($update_sql);