Start Building Professional
Web Apps Today

Updating Complex Joins using Connector

Hi Guys,

I have a complex query used to populate a grid and I want to update these tables based on what the user chooses (the grid has a number of coro fields used for data enty/updates as well ed fields. However, the connector update produces some strange results, the ed fields which are pulled from the main table update ok, however the foreign keys into other table are not, plus some of the fields in the foreign tables are incorrectly updated wth key indexes rather than key values (these tables should not be updated at all). Heres the code, but perhaps you can give me some ideas on the best way to update complicated queries..


            $GetCalloutsSQL = "select t1.CalloutID, t1.ClientID, t1.JobID, t3.CalloutName as JobCalloutName, t2.TaskDesc, t1.NumberCrew, t1.CalloutDate, t1.ChargeRate, t1.StartTime, t1.NumberHours, t4.lookuptext as COStatus, t1.Location, t5.ContactName from callouts t1, tasks t2, callouttypes t3, lookups t4, contacts t5 where t1.TaskID=t2.TaskID and t1.CallOutTypeID = t3.CalloutTypeID and t1.Status=t4.lookupvalue and t4.lookupkey='calloutstatus' and t1.JobID=$_GET[JobID] and t1.ContactID=t5.ContactID order by CalloutDate"; //, StartTime, TaskDesc asc";
            $resconn->enable_log("log.txt");
        
            $COoptions = new OptionsConnector($res);
            $COoptions->render_table("callouttypes","CalloutTypeID","CalloutTypeID(value),CalloutName(label)");
            $resconn->set_options("t3.CalloutName",$COoptions);
            
            $TaskOptions = new OptionsConnector($res);
            $TaskOptions->render_table("tasks","TaskID","TaskID(value),TaskDesc(label)");
            $resconn->set_options("TaskDesc",$TaskOptions);
            
            //$resconn->sql->attach("Update", "update callouts set `NumberCrew` = '{NumberCrew}' where calloutid='{
CalloutID}'");

            $ContactOptions = new OptionsConnector($res);
            $ContactOptions->render_table("tasks","TaskID","TaskID(value),TaskDesc(label)");
            $ContactOptions->render_sql("select contacts.ContactID, contacts.ContactName from contacts, jobs where jobs.ClientID=contacts.ClientID and Jobs.JobID=$_GET[JobID]","ContactID","ContactID(value),ContactName(label)");
            $resconn->set_options("ContactName",$ContactOptions);

            $resconn->render_sql($GetCalloutsSQL,"CalloutID", 'NumberCrew, TaskDesc, t3.CalloutName(JobCalloutName), CalloutDate, StartTime, NumberHours, ChargeRate, Location, ContactName, t4.lookuptext(COStatus)');        

You are using render_sql with multiple tables linked , and connector not good for updating linked tables. It generates the correct auto-update logic if a single table involved but not in such case.

You can add the custom updating logic 

function custom_update($action){
    $id = $action->get_id();
    $data = $action->get_value("NumberCrew");
    mysql_query("update callouts SET NumberCrew=$data WHERE CalloutID=$id");
    ... other necessary updates here ...
   $action->success();
}

$resconn->event->attach("beforeUpdate","custom_update");