[eluser]maria clara[/eluser]
hi to all,
im currently fixing this update for a table. when i am editing a data in a row it shows an undefined variables for other fields because i have joined other tables. im using this script:
Code: case $crudConfig['update']:
$sql = 'update '.$crudTableName.' set ';
foreach($crudColumns as $key => $value){ $updateArray[$key] = $value.'='.$crudColumnValues[$key]; };
$sql .= implode(',',$updateArray);
$sql .= ' where role_id = '.$crudColumnValues['id'];
mysql_query( $sql );
break;
but this script is just applicable for 1 table only without a joined one. that's why im having an undefined variable error message in my console.
what query do i need for a joined table??
the error pertains to this script:
Code: foreach($crudColumns as $key => $value){ $updateArray[$key] = $value.'='.$crudColumnValues[$key]; };
hope you can help me..
thanks in advance,
maria
[eluser]flaky[/eluser]
a fast example
Code: $this->db->where('column', $data['column_name']);
$this->db->join('table', 'table.column=table1.column1');
$this->db->update('table2', $data);
[eluser]maria clara[/eluser]
this are the script of tables i joined
Code: $this->db->join('maint_customers erp_b','ar_sales.client_id=b.cust_id','left');
$this->db->join('maint_employee erp_c','ar_sales.salesman_id=c.emp_id','left');
$this->db->join('maint_marketsegment erp_d','ar_sales.ms_id=d.ms_id','left');
$this->db->join('sec_dataset erp_e','ar_sales.type_id=e.data_id','left');
//$this->db->from('ar_sales');
$this->db->where('ar_sales.is_deleted','0');
$this->db->join('sec_dataset k','ar_sales.status=k.data_value AND k.data_code = "TRAN_STATUS"','left');
$this->db->select("si_id as pkey,sales_no, DATE_FORMAT(sales_date, '%b %d, %Y'), b.customer_name, e.data_display as type, c.emp_name, d.ms_desc, gross, remarks, k.data_display as status", false);
$query = $this->db->get("ar_sales");
$data['db'] = $query;
return $data;
these are joined query..my script for updating is not working because it is applicable for one table query only.
[eluser]flaky[/eluser]
do you want to update one table or 2 or more tables
[eluser]maria clara[/eluser]
yes i want to update a multiple tables in a jqgrid because in a transaction, there are fields that are from other tables. so i join them as you can see. the fields in the other tables should be updated also so that there will be no error in my console.
how to make a query for a joined tables using set function?
[eluser]flaky[/eluser]
Code: UPDATE a
INNER JOIN b USING (id)
SET a.firstname='Pekka', a.lastname='Kuronen',
b.companyname='Suomi Oy',b.companyaddress='Mannerheimtie 123, Helsinki Suomi'
WHERE a.id=1;
considering the above code
Code: $this->db->set('a.firstname', 'Pekka');
$this->db->set('a.lastname', 'Kuronen');
$this->db->set('b.companyname', 'Suomi Oy');
$this->db->set('b.companyaddress', 'Mannerheimtie 123, Helsinki Suomi');
$this->db->where('a.id', 1);
$this->db->join('table2 as b');
$this->db->update('table as a');
hope it helps
[eluser]maria clara[/eluser]
i used $CrudColumns as an array for the fields. all of the fields are there including the fields in other tables. like what i have shown you.i used this for the tables that are joined
Code: $crudTableName = "'erp_ar_sales','erp_b','erp_c','erp_d','erp_e','k'";
this was the error shown in my console
Code: <p>Severity: Notice</p>
<p>Message: Undefined variable: updateArray</p>
<p>Filename: controllers/ar_salesinvoice.php</p>
<p>Line Number: 247</p>
<p>Severity: Warning</p>
<p>Message: implode() [<a href='function.implode'>function.implode</a>]: Invalid arguments passed</p>
<p>Filename: controllers/ar_salesinvoice.php</p>
<p>Line Number: 247</p>
[eluser]flaky[/eluser]
would you mind putting the complete function code here
[eluser]maria clara[/eluser]
this is the pull code:
Code: function post2()
{
//$this->output->enable_profiler(TRUE);
$crudColumns = array(
'id'=>'si_id',
'sales_no'=>'sales_no',
'sales_date'=>'sales_date',
'type_id'=>'type_id',
'doc_type_id'=>'doc_type_id',
'client_id'=>'client_id',
'customer_code'=>'customer_code',
'customer_name'=>'customer_name',
'salesman_id'=>'salesman_id',
'emp_code'=>'emp_code',
'emp_name'=>'emp_name',
'int_term_id'=>'int_term_id',
'term_id'=>'term_id',
'pay_type_id'=>'pay_type_id',
'ms_id'=>'ms_id',
'ms_code'=>'ms_code',
'ms_desc'=>'ms_desc',
'pd_id'=>'pd_id',
'pd_code'=>'pd_code',
'pd_desc'=>'pd_desc',
'remarks'=>'remarks',
'gross'=>'gross',
'return'=>'return',
'paid'=>'paid',
'debit'=>'debit',
'credit'=>'credit',
'balance'=>'balance',
'invoice_type'=>'invoice_type',
'doc_type'=>'doc_type',
'internal_terms'=>'internal_terms',
'customer_terms'=>'customer_terms',
'payment_type'=>'payment_type',
'status'=>'status',
'status_name'=>'status_name'
);
$crudTableName = "'erp_ar_sales','erp_b','erp_c','erp_d','erp_e','k'";
$postConfig['id'] = 'si_id';
$crudConfig['update'] = 'edit'; /* action UPDATE keyword */
$postConfig['action'] = 'oper'; /* action variable */
switch($postConfig['action']){
case $crudConfig['update']:
/*$this->db->where('si_id', $this->input->post('id'));
$this->db->update($crudTableName);*/
$sql = 'update '.$crudTableName.' set ';
//foreach($crudColumns as $key => $value){ $updateArray[$key] = $value.'='.$crudColumnValues[$key]; };
foreach($crudTableName as $key => $value){ $updateArray[$key] = $value.'='.$crudColumnValues[$key]; };
$sql .= implode(',',$updateArray);
$sql .= ' where role_id = '.$crudColumnValues['id'];
mysql_query( $sql );
break;
}
if (isset($data)) echo json_encode($data);
[eluser]flaky[/eluser]
where are you taking
from?
EDIT
basically you need to declare it before using it
|
