Displaying a Table in Record View with Foreign Keys Defined

Richard,

The ABASE plugin provides a very useful resource which I would be most happy to pay for. I cannot find anything that approaches functionality
for accessing databases. However, I cannot do a fundamental task that is outlined below. I hope I can be directed in the right direction to solve this problem.

• Enter search criteria into Search form – Works
• Returns a list of items (Primary key and description) of items meeting the search criteria – Works
• Click the link associated with the primary key of the required item – Works
• Display a Record View of selected fields in the table IF the table that has foreign keys defined – Error is displayed.

An example is provided below.

Table: wp_wnc_food_des
Column: NDB_No
Column: Long_Desc

Table: wp_wnc_abbrev
Column: NDB_No
Column: Shrt_Desc

The tables are using InnoDB engine.

Page 2

Uses the shortcode below to list NDB_No, Long_Desc in table wp_wnc_food_des with a link on NDB_No

[abase table=”wp_wnc_food_des” columns=”NDB_No,Long_Desc” rlink=”NDB_No,https://localhost/test-db-3″%5D

This is as expected. Working well up to this point.

Page 3

Uses the shortcode below with the expectation to display a record view of an row in table wp_wnc_abbrev as specified in the link above.

[abase table=”wp_wnc_abbrev” limit = “1” fields=”NDB_No,Long_Desc” ack=”4, blue” ]

The response from ABASE is below.

=============================================================================================

#1. [abase table=”wp_wnc_abbrev” ack=”4, blue”] ************ NOTE: The fields attribute has been deleted from the shortcode that was defined.
Fatal Error (1376) Column ‘NDB_No’ in where clause is ambiguous

SELECT *
FROM wp_wnc_abbrev
LEFT JOIN wp_wnc_food_des AS wp_wnc_abbrev$NDB_No$wp_wnc_food_des
ON wp_wnc_abbrev.NDB_No=wp_wnc_abbrev$NDB_No$wp_wnc_food_des.NDB_No
LEFT JOIN wp_wnc_fd_group AS wp_wnc_abbrev$NDB_No$wp_wnc_food_des$FdGrp_Cd$wp_wnc_fd_group
ON

wp_wnc_abbrev$NDB_No$wp_wnc_food_des.FdGrp_Cd=`wp_wnc_abbrev$NDB_No$wp_wnc_food_des$FdGrp_Cd$wp_wnc_fd_gr

oup`.FdGrp_CD

WHERE NDB_No=’11031′ table_error = ” cols_in = ” fields_in = ” columns_in = ‘*’ form = ”

================================================================================================

How do you specify a unique NDB_No column name? In this case it should be associated with the table wp_wnc_abbrev.

The response indicates that columns_in = ‘*’. I have tried setting the attributes fields, columns, cols, columns_in to the required fields but columns_in is always shown as ‘*’

The SELECT statement without the WHERE clause works as expected.

Richard Harding

https://www.ads-software.com/plugins/abase/