SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Member
    Join Date
    Jun 2012
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Foreign key in phpmyadmin

    Hi I am creating a website and linking it to a database using php and mysql. I need to create the databases in phpmyadmin and have decided on the structure of the tables in the database. However, as expected, there are foreign keys in some of the tables - does anyone know how to link the foreign key of one table into another table within phpmyadmin so the database works properly and can be queried using sql? I would really appreciate help with this. Thanks

  2. #2
    SitePoint Member dennis.g's Avatar
    Join Date
    Jun 2012
    Location
    Athens, Greece
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It is very easy if you have the following:
    - Latest phpMyAdmin installed.
    - The storage engine of both tables are InnoDB. Not MyISAM.
    - You have primary keys on both tables.
    - You have an index on both fields that are going to connect. If the field in the first table is the primary key, you don't need to index it again.

    If you have all that, you go to structure view in the second table. Under the table with all the field information there is a link "Relation view". Click that and make the connection.

  3. #3
    SitePoint Member
    Join Date
    Jun 2012
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you Dennis. I did what you have told but still I am not able to get foreign key. Do I need to make ny change in php script?

  4. #4
    SitePoint Member dennis.g's Avatar
    Join Date
    Jun 2012
    Location
    Athens, Greece
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, this thing is internal to the database.

    Do you see the "Relation view" link under the structure table?

  5. #5
    SitePoint Member
    Join Date
    Jun 2012
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes I used the relation view under the structure table. However I dont get it why it is not working.

  6. #6
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    To save you time in phpmyadmin you can ALWAYS use the tab marked SQL and create any table creation statement, inserts, updates etc. from there. So if you are having trouble using the GUI you can set your foreign keys in the SQL tab.

  7. #7
    SitePoint Member dennis.g's Avatar
    Join Date
    Jun 2012
    Location
    Athens, Greece
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SQL is always an alternative. But if the requirements are not met, the SQL call will also fail.
    Back to the problem, if you can get into the relation view, that means that your table is indeed in InnoDB format.
    Do you see the field that needs to reference to the primary table? If not, you are probably at the wrong table.
    Does the row with the field have form controls? If not, then you probably haven't created the appropriate indexes (primary and secondary).
    If you see them enabled, then you just use them to make the connection.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by dennis.g View Post
    But if the requirements are not met, the SQL call will also fail.
    you can say this about anything

    if you don't click the right icons in phpmyadmin, you will fail

    if you don't submit the right foreign key SQL, you will fail

    if you don't know how foreign keys are supposed to work, you will probably fail no matter how you go about it

    the nice thing about SQL is that it will always work, and you'll never have to learn some unfriendly gui which actually just generates SQL under the covers
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My initial thought was to get the problem solved. The second, in reference to r937, is that what if you only use point/click with a GUI and then somehow move to a set-up where there is no GUI or perhaps one you are not familiar with. now what do you do if you don't know how to ALSO code by hand.

  10. #10
    SitePoint Member dennis.g's Avatar
    Join Date
    Jun 2012
    Location
    Athens, Greece
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The average MySQL user does not have that deep knowledge guys. And MySQL is a success because of those people. So lets not be too strict. It is good to know MySQL in depth, but if you have a simple project and you can do your job in simpler means, I don't see why not use them instead of raw SQL.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by dennis.g View Post
    ...if you have a simple project and you can do your job in simpler means, I don't see why not use them instead of raw SQL.
    i agree completely

    so these simpler means, by which you mean the icons and buttons in phpmyadmin, why is it so hard to get them to work?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Member dennis.g's Avatar
    Join Date
    Jun 2012
    Location
    Athens, Greece
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well.. this is completely off topic. So the OP can ignore these posts.

    <Philosophical discussion>

    Because there is the business logic that is common for a user of a GUI and an SQL coder. From that point on, the SQL coder must know the syntax which adds a lot to the learning curve.

    For example I know the computer language TCL. It is unique because it has about 5 commands. Is it reasonable to demand everyone to learn it? And even if you spend 1-2 hours to learn the basic commands, would you be willing to spend endless hours in exceptions? Like for example in adding or removing items in associative arrays.

    The people who have a deep understanding of a system usually forget how it looks from the outside. The last years there is this new area in computing called UX, who's mission is to fight the kind of thinking of people like us (coders)

    </Philosophical discussion>

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by dennis.g View Post
    The people who have a deep understanding of a system usually forget how it looks from the outside.
    not if you're a frequent poster, like i am

    believe me, i have seen and heard it all when it comes to people learning SQL

    i completely understand how tough it is

    and yet i still believe that their understanding is not improved by using a gui -- often, it's the opposite
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •