How to get a field-variable in a SQL-statement?

Scriptcase 8 Discussion
#1

I?m started to work with SC!
I?d like to have a question-summary, and a question-sheet for all Clients.
First: I manage the questions in the summary (in a multi row form) . if I create a new one, it should appear to all question-sheets per client! This functionality can be called via events or via a button and AJAX
e.g. Table cpm_risc_que
Column Typ
queid int(11)
cusart int(11)
question text NULL
rating int(11)
mutdate timestamp

e.g. Table cpm_risc_client
Column Typ
queid int(11)
nkunde double
queid int(11)
question text NULL
rating int(11) NULL
indrating int(11) NULL
yes_no tinyint(4) NULL
mutdate timestamp NULL
cusart int(11)
question text NULL
rating int(11)
mutdate timestamp
e.g. SQL in Event onAfterInsert:
/**

  • Insert a record on another table
    */
    // Insert record
    $insert_sql = ‘INSERT INTO cpm_risc_client ( NKUNDE, queid, question, rating, mutdate )
    SELECT cpm_cus.NKUNDE, cpm_risc_que.queid, cpm_risc_que.question, cpm_risc_que.rating, cpm_risc_que.mutdate
    FROM cpm_risc_que INNER JOIN cpm_cus ON cpm_risc_que.cusart = cpm_cus.cusart’;
    sc_exec_sql($insert_sql);

Result: all questions per client are inserted, (table empty)!
It works! But only with all rows from cpm_risc_que and table cpm_risc_client has to be empty!!
How can I get the current queid into the SQL??
$insert_sql = 'INSERT INTO cpm_risc_client ( NKUNDE, queid, question, rating, mutdate )
SELECT cpm_cus.NKUNDE, cpm_risc_que.queid, cpm_risc_que.question, cpm_risc_que.rating, cpm_risc_que.mutdate
FROM cpm_risc_que INNER JOIN cpm_cus ON cpm_risc_que.cusart = cpm_cus.cusart
Where cpm_risc_que.queid = {queid}?;

It is not working!!!

#2

In general the use of {var} is ok. But it might be that you need to quote queid, or that there is some other error. What you can do is to go to the application sections and set the debug options to on so that you can see the generated sql statements and that the errors are all reported.
Also it might be that in a multirow the {} is not valid as there’s no current selected record. Since you do an insert from a select you might need to use a global variable or create a grid with a run button.

#3

Hello Albert Drent
Thanks for your quick response on my Question.
I found the solution to get the Field-variable into the SQL-Statement.
The SQL-Statement is a String-Variable for $insert_sql and needs to be attached with the field variable outside the string!
The where clause has to look like:
'INSERT INTO cpm_risc_client ( NKUNDE, queid, question, rating, mutdate )
SELECT cpm_cus.NKUNDE, cpm_risc_que.queid, cpm_risc_que.question, cpm_risc_que.rating, cpm_risc_que.mutdate
FROM cpm_risc_que INNER JOIN cpm_cus ON cpm_risc_que.cusart = cpm_cus.cusart
Where cpm_risc_que.queid =[SIZE=5]’ . {queid};[/SIZE]