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?
I'm using phpMyAdmin 2.10.1 and MySQL 5.4045