How to create a dynamic query from a list of fields?

Question

I am trying to build a dynamic query using PHP and MySQL.

I created a table (i.e. field_relations ) This field has 5 columns

• field_name (field name "ie. account_id, account_name ....")
• display_label (the way the field should be biased to use), i.e. account id, name ")
• table_name (the table in which this field refers to "ie. accounts")
• related_to (the relationship of the field to another table, if any. The default value is NULL )
• related_to_field (field where it indicates “if any.” Default value: NULL )

Here is an example of data field_name display_label table_name related_to related_to_field account_id Account ID accounts NULL NULL account_name Name accounts NULL NULL first_name First Name contacts NULL NULL last_name Last Name contacts NULL NULL contact_id Contact ID contacts NULL NULL account_id Account ID contacts accounts account_id task_id Task ID tasks NULL NULL subject Subject tasks NULL NULL owner_id Assigned To contacts contacts contact_id daily_sales Sales transactions accounts account_id sold_on Sold On transactions NULL NULL

So, if I create an HTML form with 3 seconds

• Select a column to display
• Add formula to columns (optional)
• Condition selection condition (optional)
• Button "Show Results".

The first part of the form displays all the values ​​that are listed in the display_label column.

If the user selects Name, First Name, Last Name

Then the query should look like this:

 SELECT accounts.account_name, contacts.first_name, contacts.last_name FROM accounts INNER JOIN contacts ON contacts.account_id = accounts.account_id 

after the request is completed, it will be executed.

Or, if the user selects "Name, sales." Then the user wants to apply the SUM function in the daily_sales column. And finally, the user selected a filter for Sold On between '2014-01-01 00:00:00' AND '2014-10-01 00:00:00'

Then the query should look like this:

 SELECT accounts.account_name, SUM(daily_sales) AS daily_sales FROM accounts LEFT JOIN sales ON sales.account_id = accounts.account_id WHERE sales.sold_on BETWEEN '2014-01-01 00:00:00' AND '2014-10-01 00:00:00' GROUP BY accounts.account_name 

after the request is completed, it will be executed.

How can I generate such a request? do i need to add more column to field_relations table?

I'm not worried about how to create a PHP form to capture user specifications, but I'm trying to figure out how to correctly generate a MySQL query?

Thank you in advance for your help and time.

Answer 1

First of all, perhaps you should take a look at any of several ORM (Object Relationship Management) management systems available for PHP / MySQL.

But it’s relatively easy to reinvent the wheel, provided that you keep it small (this means that you can only allow very simple requests ).

If in this case, let's say, we only need internal associations, and they are all one-to-many (in fact, this is not a strict requirement, as we will see). We can build a DIY ORM (I suspect a more suitable name would be something like "Diy Orm Cthulhu Fhtagn")

The first step is to store this information somewhere, such as an array. One entry for all possible ASSOCIATIONS. You will also need to describe your table in the system. You can also get a MySQL system query to retrieve tables and field names, perhaps from time to time from a separate utility that will generate PHP code.

 // This just maps what fields are in what tables $tables = array( 'contacts' => array( 'first_name' => true /* or an array of information such as SQL type, etc. */ ); ); // This maps all the JOINs $orm = array( 'contacts' => array( 'accounts' => array( 'on' => array( 'account_id', 'account_id' ), 'type' => 'LEFT JOIN', // ) ) ); 

So you start with the list of $ selectFields. You copy these fields into $unresolvedFields and start exploring them one by one. Your goal is to allow all fields.

Pseudocode (actually not such a pseudo):

 while (!empty($unresolvedFields)) { $changes = false; // Try to resolve one of them. foreach ($unresolvedFields as $i => $field) { // Try to resolve it. list($tableName, $fieldName) = explode('.', $field); // Did we already select from this table? if (array_key_exists($tableName, $selectedTables)) { // Yes, so this field has been resolved for free. $changes = true; $resolvedFields[] = $field; array_push($selectedTables[$tableName], $fieldName); unset($unresolvedFields[$i]; // On to the next field. continue; } // This is the first time we see this table. // Is this the VERY FIRST table (assume it the "lead" table -- // it not necessary but it simplifies the code)? if (empty($selectedTables)) { // Yes. We need do no more. $selectedTables[$tableName] = array( $fieldName ); $changes = true; //-// $resolvedFields[] = $field; //-// unset($unresolvedFields[$i]; //-// // On to the next field. //--// continue; //--// } // We could also put this check before the last. If we did, the // lines above marked //-// could be left out; those with //--// should. // And we would need $selectedTables[$tableName] = array(/*EMPTY*/); // We did not see this table before, and it not the first. // So we need a way to join THIS table with SOME of those already used. // Again we suppose there're no ambiguities here. This table HAS a // link to some other. So we just need ask, "WHICH other? And do we have it?" $links = $orm[$tableName]; $useful = array_intersect_keys($orm[$tableName], $selectedTables); // $useful contains an entry 'someTable' => ( 'on' => ... ) // for each table that we can use to reference $tableName. // THERE MUST BE ONLY ONE, or there will be an ambiguity. // Of course most of the time we will find none. // And go on with the next field... if (empty($useful)) { continue; } // TODO: check count($useful) is really 1. $changes = true; $selectedTables[$tableName] = array( $fieldName ); list($joinWith, $info) = each($useful[0]); // We write SQL directly in here. We actually shouldn't, but it faster // to do it now instead of saving the necessary info. // $info could actually also contain the JOIN type, additional conditions... $joins[] = "INNER JOIN {$joinWith} ON ( {$tableName}.{$info['on'][0]} = {$joinWith}.{$info['on'][1]} )"; unset($unresolvedFields[$i]; } // If something changed, we need to repeat, because a later field could have // supplied some table that could have made joinable an earlier field which we // had given up on, before. if (!$changes) { // But if nothing has changed there no purpose in continuing. // Either we resolved all the fields or we didn't. break; } } // Now, if there're still unresolved fields after the situation stabilized, // we can't make this query. Not enough information. Actually we COULD, but // it would spew off a Cartesian product of the groups of unjoined tables - // almost surely not what we wanted. So, unresolveds cause an error. if (!empty($unresolvedFields)) { throw new \Exception("SOL"); } // Else we can build the query: the first table leads the SELECT and all the // others are joined. $query = "SELECT " . implode(', ', $selectedFields) . ' FROM ' . array_shift($selectedTables) . "\n"; // Now for each $selectedTables remaining foreach ($selectedTables as $table) { $query .= $joins[$table] . "\n"; // Now we could add any WHEREs, ORDER BY, LIMIT and so on. ... 

If the user selects a first name, first name, last name

You will also need a “translation” between the person you read “Name” and “accounts.account_name”. However, once you have done this, the algorithm above will find these entries:

 Name ... fields = [ accounts.account_name ], tables = [ accounts ], joins = [ ] First Name ... fields = [ a.ac_name, co.first ], tables = [ ac, co ], joins = [ co ] Last Name ... contacts is already in tables, so fields = [ 3 fields ], rest unchanged 

Answer 2

Think about how to build a sql query as a simple exercise for string building. The values ​​placed on the form on the previous page will refer to your field_relations table to determine which columns and tables you want in the query.

The first published value identifies the field in one of the tables, telling you that you need a FROM clause. Then, as soon as you meet the field from the second table, you will be asked to add INNER JOIN contacts (or sales) ON contacts.account_id = accounts.account_id. If you later encounter a field from the third table, you need to add another JOIN clause.

You do not need the related_to and related_to_field columns at all in field_relations, since the column name sent from the form when indicated in the field_relations table tells you which table the field comes from.

Answer 3

Why aren't you using ORM? The doctrine in this case is very useful:

http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/tutorials/getting-started.html

You can add an entity and fields dynamically and combine them: it is easy to learn and implement.