Question about Join Table Column

Discuss MySQL Ajax Table Editor Paid Version

Question about Join Table Column

Postby almo » Fri Jun 19, 2015 10:11 am

Hi, I am trying to edit join table column, but is unsuccessful with error Unknown column 'phone' in 'field list'.

Table A (a_id, name, email, role)
Table B (b_id, a_id, phone)

Table A is used as main table and Table B is joined to Table A.
Below is the codes:

$tableColumns['phone'] = array(
'display_text' => 'Phone',
'perms' => 'EVTQSF',
'display_mask' => 'b.phone',
'input_info' => 'size="20"',
'col_header_info' => 'width="15%"',
);

$tableName = 'table_a';
$primaryCol = 'a_id';
$errorFun = array(&$this,'logError');
$permissions = 'EUFIM';


....

$this->Editor->setConfig('editInPlace',true);
$this->Editor->setConfig('extraOrderByInfo','name asc');
$this->Editor->setConfig('customJoin',"left join b as b on a.a_id = b.b_id");
$this->Editor->setConfig('sqlFilters',"role = '5' OR role = '6'");


The table column view is fine but when update the row. There is error : Unknown column 'phone' in 'field list'
Is there any way to edit the value of the columns of the joined table? I want the phone column to be editable.
almo
 
Posts: 3
Joined: Sat Mar 02, 2013 8:48 am

Re: Question about Join Table Column

Postby KarelB » Tue Jul 28, 2015 11:28 am

You cannot update fields in a joined column directly.
Either make your phone table the main table or use after_edit_fun callback to update a secondary table.
KarelB
 
Posts: 458
Joined: Mon Dec 01, 2008 11:49 pm
Location: The Netherlands

Re: Question about Join Table Column

Postby Mesqueeb » Thu Oct 15, 2015 12:44 am

KarelB wrote:You cannot update fields in a joined column directly.
Either make your phone table the main table or use after_edit_fun callback to update a secondary table.


Does anyone know how to do this? I tried but I have no idea how to use the "after_edit_fun".
I'm trying to be able to edit fields from a table which I joined into a different table.
Mesqueeb
 
Posts: 10
Joined: Wed Oct 14, 2015 2:31 am

Re: Question about Join Table Column

Postby admin » Thu Oct 15, 2015 4:15 am

If the 2 tables are a 1 to 1 relationship, then you may be able to do this with a view.

To test, try creating a view of the 2 joined tables (make sure there is a unique primary key in your view for MATE to reference). If you can update the view correctly with a regular update query then you can replace the table name with the view name in the MATE config file and it should work.
admin
Site Admin
 
Posts: 1502
Joined: Fri Jul 11, 2008 1:34 am

Re: Question about Join Table Column

Postby Mesqueeb » Fri Oct 16, 2015 3:04 am

admin wrote:If the 2 tables are a 1 to 1 relationship, then you may be able to do this with a view.

To test, try creating a view of the 2 joined tables (make sure there is a unique primary key in your view for MATE to reference). If you can update the view correctly with a regular update query then you can replace the table name with the view name in the MATE config file and it should work.


Dear Admin,
I searched for 'view' in the documentation, but I could only find
"Working With Views - Overriding insert, update and delete functions." which had but a very small explanation.
Could you point me out where I could find how to create a 'view'?
When googling I found this: http://www.w3schools.com/sql/sql_view.asp but I don't think I need to write raw SQL code or do I?

Cheers!
-Luca Ban
Mesqueeb
 
Posts: 10
Joined: Wed Oct 14, 2015 2:31 am

Re: Question about Join Table Column

Postby admin » Fri Oct 16, 2015 3:12 am

You will have to execute an sql statement to create the view. A view is like a virtual table that can be queried. So you would create a view that is already joining the 2 tables together and then use that with MATE.

For more info see the following links.

http://stackoverflow.com/questions/2878 ... views-work
http://stackoverflow.com/questions/5185 ... w-in-mysql
admin
Site Admin
 
Posts: 1502
Joined: Fri Jul 11, 2008 1:34 am

Re: Question about Join Table Column

Postby Mesqueeb » Tue Oct 20, 2015 1:27 am

admin wrote:If the 2 tables are a 1 to 1 relationship, then you may be able to do this with a view.

To test, try creating a view of the 2 joined tables (make sure there is a unique primary key in your view for MATE to reference). If you can update the view correctly with a regular update query then you can replace the table name with the view name in the MATE config file and it should work.


Dear Admin,
I was able to make a view from the mate example db:
Code: Select all
CREATE VIEW emp_login_view AS
SELECT
   employees.id,
   employees.first_name,
   employees.last_name,   
   login_info.login
FROM
employees
LEFT JOIN login_info
   ON login_info.employee_id = employees.id

As you can see I use the employees.id, so this is my unique key.
I could show this view correctly:
http://system-labo.jp/luca/grid/mategridtest.php
But when trying to edit something I get the following error:
Invalid json sent back from server. Error: SyntaxError: Unexpected token <
Server sent: Fatal error: Uncaught exception 'Exception' with message 'An error occurred in script /export/sd212/www/jp/r/e/gmoserver/9/1/sd0801691/system-labo.jp/luca/mate-free-4.2/php/AjaxTableEditor.php on line 1304: <br /><br />There was an error executing the following query: <br />update emp_login_view set `first_name` = :first_name, `last_name` = :last_name, `login` = :login where id = :old_primary_key_value<br /><br />PDO query params: first_name => 'Alexius', last_name => 'Christensen', login => 'alexcwac', old_primary_key_value => '15'<br /><br /> PDO error:<br /> "SQLSTATE[HY000]: General error: 1393 Can not modify more than one base table through a join view 'sddb0040189228.emp_login_view'" on line: 328' in /export/sd212/www/jp/r/e/gmoserver/9/1/sd0801691/system-labo.jp/luca/grid/Common.php:26 Stack trace: #0 [internal function]: Common->logError('<br /><br />Th...', '/export/sd212/w...', 1304, 'mate1_') #1 /export/sd212/www/jp/r/e/gmoserver/9/1/sd0801691/system-labo.jp/luca/mate-free-4.2/php/AjaxTableEditor.php(348): ca in /export/sd212/www/jp/r/e/gmoserver/9/1/sd0801691/system-labo.jp/luca/grid/Common.php on line 26


Any thoughts?
Mesqueeb
 
Posts: 10
Joined: Wed Oct 14, 2015 2:31 am

Re: Question about Join Table Column

Postby admin » Thu Oct 29, 2015 2:27 pm

It doesn't look like your view is updatable. Only some views can be updated directly (see https://dev.mysql.com/doc/refman/5.0/en ... ility.html for more info).

If you can't modify the view structure to make it updatable, then you will need to override the insert, update, delete functions of MATE and write your own queries to update the view. See the following link in the documentation for more info.

https://mysqlajaxtableeditor.com/Docume ... with_views
admin
Site Admin
 
Posts: 1502
Joined: Fri Jul 11, 2008 1:34 am


Return to Paid Version

Who is online

Users browsing this forum: No registered users and 3 guests

cron