Problem displaying table with foreign keys

  • I’m trying to display a table ‘class’ which has a foreign key linking it to table teachers. The shortcode I use:
    [abase table=”class”
    columns=”id_class,name,id_teacher”
    ]

    I got the following error:

    #1. [abase table=”class” columns=”id_class,name,id_teacher”]
    Fatal Error (1139)
    Unknown column ‘teachers.id_teacher’ in ‘on clause’
    Check to make sure the upper and lower case spelling of your table and column names are all exactly correct. Valid table names in szkola: class, dzieci, klasy, nauczyciele, platnosci, rejestracja, rodzice, teachers.

    SELECT * FROM class LEFT JOIN teachers AS class$id_teacher$teachers ON class.id_teacher=class$id_teacher$teachers.id_teacher LEFT JOIN teachers AS teachers$id_teacher$teachers ON teachers.id_teacher=teachers$id_teacher$teachers.id_teacher table_error = ” cols_in = ” fields_in = ” columns_in = ‘id_class,name,id_teacher’ form = ”

    I didn’t even want to display values from different tables at this point, just display values from a table that has foreign keys.

    Is it possible to add <rlink> to an <sql> attribute? In other words I’d like the functionality of a table, but use my own sql query.

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

Viewing 5 replies - 1 through 5 (of 5 total)
  • Plugin Author Richard Halverson

    (@richhalverson)

    First, can you put the following short codes on a page, view the page and post back what appears?

    [abase sql=”SHOW CREATE TABLE class”]

    [abase sql=”SHOW CREATE TABLE teachers”]

    It looks like you might have set the relations wrong.

    Thread Starter tosa500

    (@tosa500)

    Thank you for the reply. Result from the above shortcodes:
    CREATE TABLE class (
    id_class int(1) NOT NULL AUTO_INCREMENT,
    name int(11) NOT NULL,
    id_teacher int(1) NOT NULL,
    PRIMARY KEY (id_class),
    KEY id_teacher (id_teacher),
    CONSTRAINT class_ibfk_1 FOREIGN KEY (id_teacher) REFERENCES teachers (id_teacher)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1

    CREATE TABLE teachers (
    id_teacher int(1) NOT NULL AUTO_INCREMENT,
    forename varchar(16) NOT NULL,
    surname varchar(16) NOT NULL,
    phone varchar(10) NOT NULL,
    PRIMARY KEY (id_teacher)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1

    Plugin Author Richard Halverson

    (@richhalverson)

    OK I have reconstructed your database. See https://abase.com/abase-help/tosa500/

    The only thing I changed was I made the “name” field in the “class” table a varchar instead of integer.

    Then I inserted a teacher record. Then I inserted a class record. Notice I show the short codes to insert records.

    At the bottom, the short code in purple shows your short code from above. I don’t get the error. I have not been able to replicate your error.

    It is possible you have an older version of ABASE installed. Can you try upgrading and then trying some of the short codes I show on https://abase.com/abase-help/tosa500/

    I have included echo attributes in all the short codes so you can just copy and paste them into your pages.

    Thread Starter tosa500

    (@tosa500)

    Hello Richard. Sorry for a long delay.
    I can confirm installing the latest version fixed my problem.
    I’ve got another question:
    In example 8 there is a table class shown with all the elements of table teachers. But if I don’t want all the fields from table teachers to be shown, I can’t do that. Would it be possible for you to add
    columns=”name,id_teacher|forename,id_teacher|surname” so we could control what fields we want to display, similar to example 7?

    One thing that is also missing in your plugin, or I missed that in your documentation is how to delete a record. Is it possible to do that?

    Thanks a lot!

    Thread Starter tosa500

    (@tosa500)

    One more suggestion:
    Let’s say we have a table view with rlink which takes us to another page where we add/update/delete records. I’d like to have a url in the “form” shortcode which would take me back to updated table. Could you do that?
    Cheers

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘Problem displaying table with foreign keys’ is closed to new replies.