WordPress.org

Ready to get started?Download WordPress

Forums

ABASE
Problem displaying table with foreign keys (6 posts)

  1. tosa500
    Member
    Posted 10 months ago #

    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.

    http://wordpress.org/plugins/abase/

  2. Richard Halverson
    Member
    Plugin Author

    Posted 10 months ago #

    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.

  3. tosa500
    Member
    Posted 10 months ago #

    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

  4. Richard Halverson
    Member
    Plugin Author

    Posted 10 months ago #

    OK I have reconstructed your database. See http://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 http://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.

  5. tosa500
    Member
    Posted 10 months ago #

    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!

  6. tosa500
    Member
    Posted 10 months ago #

    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

Reply

You must log in to post.

About this Plugin

About this Topic

Tags

No tags yet.