sql for beginners…

Archived from the Xataface Users forum.

maddin — Sun Aug 13, 2006 9:40 am

me again…sorry

a new day and a new problem..
i tried my best to write a trigger to update a related record

like in the facultyOfWidgetry example i have a table called catalogue and a table workflow
the relationship is stored in a table programcourses

my relationships.ini in catalogue folder looks like this:
//———————————————-snip——————-
[workflow]
__sql__ = “SELECT * FROM programcourses pc INNER JOIN workflow c ON pc.local_ID = c.local_ID WHERE pc.artikel_ID = ‘$artikel_ID’”
//———————————————-snap———————

my plan was to update the table catalogue when i save a record in the workflow table
unfortunately my sql knowledge is quite poor

hopefully somebody of you can help?

strval('completed') != 0){  
//-----------------------outline----------------------------------------------------------------------	  
	 // $record->strval('completed')is a date time field  
	  how to sql now?... i neither know the correct sytax nor do i know how to figure out the related "artikel_ID"  
	   //  $sql = 'UPDATE catalogue'  
       // . ' SET $record->strval('lang_ID') = '$record->strval('completed')''  
       // . ' WHERE artikel_ID = "is like the related artikel_id(don«t know how to get it)"';  
  
//------------------------------------------------------------------------------------------------------  
            $response['--msg'] .= "\nchanged status in catalogue to: ".$record->strval('completed');  
        } else {  
            return PEAR::raiseError(  
                        "Errors occurred while saving Record.  Record could not be inserted",  
                        DATAFACE_E_NOTICE);  
        }  
	  
	  
          
    }  
       
}  
  
?>

cheers
martin

---

shannah — Sun Aug 13, 2006 9:19 pm

Hi Martin,

Okay, I’m not sure what your table schema looks like, but i think your SQL will be something along these lines:

Code: Select all

$sql = "UPDATE `catalogue` SET `".str_replace('`','',$record->strval('lang_ID'))."` = '".addslashes($record->strval('completed'))."' WHERE `artikel_ID` = '".addslashes($artikel_ID)."'";

The $artikel_ID variable that I use above would have to be obtained by doing the inverse sql query as you use for the workflows relationship
e.g.

Code: Select all

$sql2 = "select `artikel_ID` from `programcourses` where `local_ID`='".addslashes($record->strval('local_ID'))."'";

Hope this helps a little.

-Steve

---

maddin — Tue Aug 15, 2006 8:03 am

thank you steve for the competent help
your code helped and , more important , was more or less self explaining how this can be done.
cheers
martin