Set field value with SQL query

Scriptcase 9 General
#1

Hello,

I have a calendar with several fields but lets focus on two of them:

category (HTML type is SELECT; SQL type is TEXT)
letter (HTML type is TEXT; SQL type is TEXT)

category field has its values feed by automatic Lookup code listed below:

SELECT Title
FROM Projects
ORDER BY Title

Based on the category selected by the user at the time of event creation, I want to make a SQL query to autofill another field of my calendar, the field is called letter

In order to achieve that I went to my calendar events area, onValidadeSuccess and tried the following code:

{letter} = a;

Everything worked well and for that event being created, the letter value was a at the calendar table.

Next, I replaced the above code with something that would check for the category got by user and pick a letter value on Projects table. Find the code below (also in OnValidateSuccess of calendar):

$test = “SELECT letter FROM Projects WHERE Title = ‘{category}’”;
sc_exec_sql($test);
$test = {letter};

Both letter and Title fields in Projects table are HTML type TEXT and SQL type TEXT.

However, this code does not add anything to the letter field on my calendar.

I don’t have much experience on SQL and can’t see my mistake.

Thanks in advance for helping.

Daniel.

#2

Hello,

Could someone help me going in the right direction? I can’t see where are the errors into my code.

Thanks.

#3

If you expect the sql select to return the value shouldn’t the last line be {letter} =$test ?

Anyway you’re using the wrong SC macro.

For select queries you should use either the sc_lookup or the sc_select macro. The former is simpler to use especially if you expect only one record as result.

See the SC manual or some videos available in the SC official site to learn how to use them.

#4

TRY THIS:

$sql = “SELECT letter FROM Projects WHERE Title = ‘{category}’”;
sc_lookup(rs, $sql);
if (isset({rs[0][0]})) // Row found
{
$letter = {rs[0][0]};
}
{letter}=$letter;

#5

It’s better to add the else part, so that you don’t assign an undefined value to {letter} if Row in not found

$sql = “SELECT letter FROM Projects WHERE Title = ‘{category}’”;
sc_lookup(rs, $sql);
if (isset({rs[0][0]})) // Row found
{
$letter = {rs[0][0]};
} else {
$letter = '';
}
{letter}=$letter;
#6

True.
Usually I just put $letter=""; at the top to start things off.

#7

This is good too. :slight_smile:

#8

Lately I’m trying to use the ternary operator as much as possible and i write one liners like this:

{letter} = isset({rs[0][0]}) ? {rs[0][0]} : ‘’;

I never tried it, but I read some php 7 info stating that one could write a shorter line like this (Null Coalescing Operator):

{letter} = {rs[0][0]} ?? ‘’;

I wonder if that will work since SC will pre parse the code when compiling it to generate standard PHP code by removing the non standard notation (the {} variables).

#9

robydago, nwdbs, jlboutin60

I followed your advices and everything is working great into my application. Thanks a lot for your time and patience giving me the right directions.

Regards,

Daniel.