title: How to build a SQL query from a list of selected fields?
body: <p>I have an application that is programmed using PHP and MySQL for back end. Most of my tables are standard MySQL Innodb tables. Also, I have a feature where an admin can add custom attributes using <a href="http://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model" rel="nofollow">EAV model</a></p>
  
  <p>I need to build a feature in the application where a user can create a custom view. Basically, I want to give the user a list of all available fields. The user can pick what fields he/she want to see on his/her screen and the system will create the query with the proper JOINS and display the results.</p>
  
  <p>For example, if a user is looking to display field 1, 2, 3, 4, 5, 6, 7, 8, 9. I will need to do the following steps</p>
  
  <ol>
  <li>I will check what table each field belongs to.</li>
  <li>Figure out which table name goes in the <code>FROM</code> part of the query</li>
  <li>Figure out which table name goes in the <code>JOIN</code> part of the query if needed. and determine when fields go in the <code>ON</code> clause of of the <code>JOIN</code> statement.</li>
  </ol>
  
  <p>The first step is fairly east to do, as it this info in stored in <code>entity_definitions</code> table "defined below"</p>
  
  <p>The second step is also fairly easy, since I know which table is the base table so I can fill it out (ie. <code>accounts</code>). </p>
  
  <p>"If needed" the third step is quite complex and I am not sure how to accomplish this. How would I know what table table to use for the Join and what column names do I use for the <code>ON</code> clause?</p>
  
  <p>I tried to solve the problem by creating a table that has 2 columns one column for a the field name and another for foreign field name. However, I am still unable to build a query by just by looking at the selected field names..</p>
  
  <p>To keep track of table/attributes definition, I created a table (ie. <code>entity_definitions</code>) that has a list of all the columns. </p>
  
  <pre><code>CREATE TABLE entity_definitions (
    entity_id int(11) unsigned NOT NULL AUTO_INCREMENT,
    display_name varchar(255) NOT NULL,
    entity_key varchar(60) NOT NULL,
    entity_type enum('lookup','Integer','text','datetime','date') CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
    object_id int(11) unsigned NOT NULL,
    PRIMARY KEY (entity_id),
    KEY object_id (object_id)
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p>The <code>object_id</code> reference the table name where it belongs in the <code>entity_objects</code> table.</p>
  
  <pre><code>CREATE TABLE entity_objects (
    object_id int(11) unsigned NOT NULL AUTO_INCREMENT,
    object_name varchar(60) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
    object_description varchar(255) DEFAULT NULL,
    PRIMARY KEY (object_id)
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p>Here is a <a href="http://sqlfiddle.com/#!9/c2a6e/2" rel="nofollow">jFiddle link</a> that has the tables and some data/queries that I have done so far.</p>
  
  <p>Question: How can I accomplish step #3? better ideas are welcome as well.</p>