sql for beginners...

[maddin]

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]

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]

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