Announcement

Collapse
No announcement yet.

Update field in one table using data from another based on a key value

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

  • Update field in one table using data from another based on a key value

    I need to update the Remark field in my table 'student' by concatenating the text data with the Remark field in my secondary table Problem_Student by matching the Student_reg in both tables.
    so i add a event onValidateSuccess

    // SQL statement parameters
    $update_table = 'student'; // Table name
    $update_where = "student_reg = 'student_reg'"; // Where clause
    $update_fields = array( Remark );

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

    but got error


    Error
    Use of undefined constant Remark - assumed 'Remark'
    Error while accessing the database:
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE student_reg = 'student_reg'' at line 1

    please i'm new to this

  • #2
    Remark is kind of strange here... $Remark for a normal variable or {Remark} if you want to use the field data would make more sense.

    Comment


    • #3
      Originally posted by rr View Post
      Remark is kind of strange here... $Remark for a normal variable or {Remark} if you want to use the field data would make more sense.
      Actually the main thing is i created a table for students registration(tblStudent) in this table(page) there is a field for Remark. i also created a table(page) for adding remark(tblprboble_student) since it cant be added manually from the student registration page. i want the data from remark field in Problem_student to save both in Problem_student table under remark field and also in student registration table under remark field.. like i said earlier im new to this

      Comment


      • #4
        Ah you want to same the date in two tables. Well then you'd use the OnAfterUpdate event on the remark field and simply perform the update as you already have figured out.
        But if you want to access the contents of a field in scriptcase you have to put { } around it.

        Comment


        • #5
          Thanks bro, but can you help in giving a detail explanation like re-writing the code.. still learning

          Comment


          • #6
            Thanks bro. but after adding the {} around remark like this
            // SQL statement parameters
            $update_table = 'student'; // Table name
            $update_where = "student_reg = 'student_reg'"; // Where clause
            $update_fields = array( {Remark} );

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

            i got this error

            Error
            Error while accessing the database:
            You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Malpratice WHERE student_reg = 'student_reg'' at line 1

            Comment


            • #7
              Hi,
              I assume you are working in a single record form.
              The error is triggered by the WHERE-CLAUSE.
              If I understood your problem, the basic statement should read like this:

              UPDATE student s, problem_student p
              SET s.remark = CONCAT(s.remark, p.remark)
              WHERE s.student_reg = p.student_reg AND s.student_reg = {student_reg}

              You might need to adjust it to your specific needs.

              jsb
              Last edited by jsbinca; 07-17-2014, 07:02 PM.

              Comment

              Working...
              X