Multiple-Table Query Generator in phpMyAdmin

Databases
1

In phpMyAdmin, I’m trying to use the built in multiple-table query generator described in this article: http://www.packtpub.com/article/multi-table-query-generator-using-phpmyadmin-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

2

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

3

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

4

Thanks for the quick response and encouragement. I need it at this point. :injured:

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. :confused:

5

Unfortunately, I don’t know anything about views.

But this query builder supposedly creates the joins for you.

6

another vote for why phpmyadmin is pants

7

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. :frowning:

I’m trying to do the best I can.

8

“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