SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Member
    Join Date
    Aug 2010
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Multiple-Table Query Generator in phpMyAdmin

    In phpMyAdmin, I'm trying to use the built in multiple-table query generator described in this article: http://www.packtpub.com/article/mult...pmyadmin-mysql

    Everything was going great until I got to the point where I wanted to get data from two tables and filter the results by setting criteria to one field. I set it all up, ran the query and it returned a bunch of repeated data (so it obviously didn't work). Then I see the line in the article that says, "phpMyAdmin used its knowledge of the relations defined between the tables to generate a left join on the author_id key field. A shortcoming of the current version is that only the internal relations are examined, not the InnoDB relations."

    Crap! My tables are InnoDB and they need to be that way to support my relationships between the tables (foreign keys).

    I copied my database and tried to switch the table type to myISAM hoping that I could re-establish my relationships and use the built in multi-table query generator. Fail. I cannot establish my relationships using myISAM tables. It seems like with these types of tables you can only link to the primary key in each table, but I need to link to more than that!

    I know I can get my results by manually typing up a SQL query and that works just fine, but I'm needing the query generator to work so that I can have other users who are not as technically savvy get data out too.

    Is there a way to make this work with InnoDB tables?

    Help!

    I'm using phpMyAdmin 2.10.1 and MySQL 5.4045
    Last edited by kmcamara; Sep 22, 2010 at 07:51. Reason: Inserting version #'s

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by kmcamara View Post
    Crap! My tables are InnoDB and they need to be that way to support my relationships between the tables (foreign keys).
    i can't help you with your phpmyadmin problem--sorry--but i just wanted to encourage you not to waver from this

    Quote Originally Posted by kmcamara View Post
    It seems like with these types of tables you can only link to the primary key in each table, but I need to link to more than that!
    actually, a foreign key can be defined to reference any unique key -- a primary key is unique, but the table can also have other unique keys and you are allowed to reference any of them for a foreign key
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    oh, and if you are going to give access to "not so savvy" users, consider giving them (prejoined) views -- don't let them build the joins themselves, that might be asking too much
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Member
    Join Date
    Aug 2010
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i can't help you with your phpmyadmin problem--sorry--but i just wanted to encourage you not to waver from this

    actually, a foreign key can be defined to reference any unique key -- a primary key is unique, but the table can also have other unique keys and you are allowed to reference any of them for a foreign key
    Thanks for the quick response and encouragement. I need it at this point.

    I establish my relationships through the phpmyadmin GUI. When I go to a table where I want to establish my relationship there's a screen with all my fields listed and they each have a drop down menu where you select what you want linked. When I changed the tables over to myISAM, these drop down menus only show the primary keys from other tables. The fields that I have defined as unique keys do not show up on the list.

  5. #5
    SitePoint Member
    Join Date
    Aug 2010
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    oh, and if you are going to give access to "not so savvy" users, consider giving them (prejoined) views -- don't let them build the joins themselves, that might be asking too much
    Unfortunately, I don't know anything about views.

    But this query builder supposedly creates the joins for you.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by kmcamara View Post
    The fields that I have defined as unique keys do not show up on the list.
    another vote for why phpmyadmin is pants
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Aug 2010
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    another vote for why phpmyadmin is pants

    Pants? I'm unfamiliar with that term too. By that do you mean, not optimal?

    I can see how I'm taking the backwards approach to learning database work and mySQL, but unfortunately I was thrown into this with my hands tied.

    I'm trying to do the best I can.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    "of inferior quality, rubbish" -- http://en.wiktionary.org/wiki/pants#Adjective

    fortunately, phpmyadmin offers the SQL tab where you can run whatever SQL you wish

    everything that can be done with columns and tables (including foreign keys) can be done with SQL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •