Announcement

Collapse
No announcement yet.

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

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

    Im started to work with SC!
    Id 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.
    Albert Drent
    aducom software netherlands
    scriptcase partner, reseller, support and (turn-key) development
    www.scriptcase.eu / www.scriptcase.nl

    Comment


    • #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 =’ . {queid};

      Comment

      Working...
      X