1

I'm working on a saas app in php/mysql, i have create db according to below fig.

enter image description here

Now with this structure i need a report with IGA and MDS customers in a particular date between.

Here comes the tricky part. IGA and MDS are codes from a customer table and its will be add into the query in dynamic way and the condition will be applied to all the tables.

ie. "select * from IGA_customers where created between $date1 and $date2" and that query to be made with multiple tables.

Any help would be appreciated. Thanks in advance

Ram Kannan Raj
  • 88
  • 6
  • What do you mean by `different schema name` – Imran Dec 22 '14 at 05:16
  • Please clarify the "dynamic way", do you mean you know the prefixes beforehand or they also have to be added to the query? If you know them beforehand, all you need is UNION to combine data from several tables. Also in the future it might be easier to use a single table and add the prefix as a column and not use separate tables if you need to get data from them combined. – Sami Kuhmonen Dec 22 '14 at 05:17
  • 1
    are you talking about `JOIN`ing tables? http://stackoverflow.com/a/16598900/689579 – Sean Dec 22 '14 at 05:21
  • @imran Different schema name is like IGA_customers, XYZ_customers, ABC_customers. ref: Case 3. http://www.ibm.com/developerworks/data/library/techarticle/dm-1201dbdesigncloud/ – Ram Kannan Raj Dec 22 '14 at 05:36
  • @SamiKuhmonen yes i know the prefix of the tables beforehand. i get the customer names from a table and use it as prefix. – Ram Kannan Raj Dec 22 '14 at 05:43
  • Then just do a select * from IGA_customers union select * from XYZ_customers union...? – Sami Kuhmonen Dec 22 '14 at 05:55
  • @SamiKuhmonen that was great. will its be ok with table join as well. now i got stuck with left join each "customer" with its appropriate "orders" table with union. – Ram Kannan Raj Dec 22 '14 at 06:07
  • Yes, just write the queries separate and then combine with union. The queries have to have the same columns selected to work, but here that is the case. – Sami Kuhmonen Dec 22 '14 at 06:08

1 Answers1

1

Why you don't write a php function like

function ($db_name, $table_name, $date_start, $date_end){

            //your query with argument
            return $query_result

}

Your query should be

  SELECT * From $db_name.$table where created between $date_start and $date_end

If you need all customer information on your all customer table , you can just add union

Imran
  • 3,031
  • 4
  • 25
  • 41