Search code examples
mysqlphpmyadminforeign-keysrelationinternal

Can phpMyAdmin "internal relations" be configured to do lookup on one column and insert values from another?

I have a MySQL database with two InnoDB tables that I administer with phpMyAdmin v4.5.2. The table have a foreign key constraint on an "id" column.

When I define an "internal relation" and then goto the "insert" page for the table I now see a new icon next to my column with the internal relation defined. This is nice but when I click the icon I can only lookup ID values...

What would be really nice is if I could do the lookup on the internal relation by a "name" column (i.e. the SYMBOL varchar field), but upon choosing an item, it would populate the ID from that row (the SYMBOL_LIST_ID column) into the insert field. I've been searching but haven't been able to find an example of how to do this -- has anyone setup this type of internal relation functionality? Thanks!

Here's an example of my table structure: http://sqlfiddle.com/#!9/4e921/1

Solution

  • I'm unable to view your SQL Fiddle, but it sounds like you just want the lookup value to show in the table instead of the key ID.

    By the way, since you are using the InnoDB table type, I suggest using the actual MySQL relations instead of phpMyAdmin's internal relations. This will allow your other applications to take advantage of the relations you configure.

    There are two ways to configure relations, Relation view (available under the Structure tab of a table), or Designer (on the main list of tabs when viewing a database). It sounds like you're already using Relation View and have correctly defined your foreign keys there, so on the table where you're doing the lookup go to the Relation View and scroll down to "Choose column to display", then pick the column you want to look up.

    In my case, I built a table "customer":

    id int primary key
name varchar(255)
city_ref int index

    and "customer_city":

    id int primary key
city varchar(255)

    Then I configured my foreign key from the Relation view in "customer":

    InnoDB relation view

    Finally from the Relation view for "customer_city", picking "city" as the column to display:

    column to display

    Now when I go to insert a row to "customer", I get the lookup value presented in the "city" field:

    Lookup

    Note that it appears twice, once sorted by the ID and once sorted by city; that way I can insert quickly if I know the ID and by referenced value if I don't.