5

I have a requirement for a PHP function that takes table or tables and the required columns from those db tables and returns a html table containing the data. I know how to do this for one table but am struggling with how to make this more dynamic

My thinking for one table would be to have a function that takes the table name and then an array of columns and then just selects the data from the table and then loops through it constructing the data as html and then return that from the function.

As an example my database has two tables; users and orders

users
|----------------------------|
|user_id|first_name|last_name|
|-------|----------|---------|

orders
|----------------------|
|order_id|user_id|total|
|--------|-------|-----|

Now with the function discussed above it would be easy to generate a table for all the users or orders but what I would like to do is have a function where I could dynamically join tables and for example list all users and the number of orders they've made or list all orders from user x. I know that this would be possible with many different functions but I'm really interested in developing a way of doing this dynamically and basically building all the relationships somehow in the program and then be able to call one function and request columns x,y and z

My thinking so far would be (again for this example) somehow define that number of orders for user i = count(order_id) where user_id = i

Hope this makes sense and thank you in advance

  • 1
    Many frameworks have their own (I like http://cakephp.org/) or http://stackoverflow.com/questions/108699/good-php-orm-library – AbraCadaver Sep 20 '16 at 20:59
  • These don't exactly solve the problem of dynamically creating a table of data. I see the benefits of them and they do work but I don't think they do for my example- unless you know otherwise? –  Sep 20 '16 at 21:27
  • So suppose you could call this: `Users::all("first_name","total");` giving you all users' first names with their totals, is that dynamic enough? – Glubus Sep 26 '16 at 14:22
  • Yeah I guess that is kind of what I'm looking for but totals would have to be calculated for each user as that data is in a different table –  Sep 26 '16 at 14:57
  • Sounds like `group_concat()` does what you want? http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat – Robbie Sep 27 '16 at 00:42

3 Answers3

2

The INFORMATION_SCHEMA.KEY_COLUMN_USAGE table can be used to find all foreign key relationships from a particular table to other tables, e.g:

SELECT `TABLE_NAME`,
       `COLUMN_NAME`,
       `REFERENCED_TABLE_NAME`,
       `REFERENCED_COLUMN_NAME`
FROM `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE`
WHERE `TABLE_SCHEMA` = SCHEMA() -- current schema
  AND `REFERENCED_TABLE_NAME` IS NOT NULL
  AND `TABLE_NAME` = 'orders'; -- name of table to get relationships to other tables

This should return something like the following:

+--------------+-------------+-----------------------+------------------------+
| TABLE_NAME   | COLUMN_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+--------------+-------------+-----------------------+------------------------+
| orders       | user_id     | users                 | user_id                |
+--------------+-------------+-----------------------+------------------------+

The above can be done in PHP and the results can then be iterated over to dyamically construct a query that selects from those tables, joining on the listed columns. Will leave that part as an exercise ;-)

Steve Chambers
  • 37,270
  • 24
  • 156
  • 208
1

You wouldn't need to make a function to grab data from first table then loop around them and get data from the second table.
SQL can do this for you with 1 hit on the database.
All what you need to do is join the two tables, and grab the data you want.. If I understood what you need right, you want to grab all users id from the first table, and get their order count from the second table.
A simple join or selecting from both table could do that, and I suggest something like:

Select a.user_id, b.count(order_id) 
FROM table1 as a, table2 as b 
WHERE a.user_id = b.user_id 
Group By a.user_id

Or you could join the tables and do a similar task.
I am assuming you're gonna access database from PHP code, so try that, and give me back your feedback.

Paul Karam
  • 4,052
  • 8
  • 30
  • 53
  • This isn't exactly what I'm looking to do. What I am wanting to is be able to call a function to generate a html table and specify the columns I want on that table as an array passed to the function and the function handle this and return the html. The function needs to be able to _know_ how to generate things like total number of orders, process this and then return the table –  Sep 20 '16 at 21:10
  • 1
    Sorry, sheldor73; I side with Paul. It is a nightmare to design a specification language to go beyond a simple table. Instead of requiring a tablename, require a `SELECT` statement, such as Paul coded. Inside the function, any `SELECT` (or any `SHOW`) will produce a 2D array, which can easily be turned into html. – Rick James Sep 27 '16 at 00:06
  • Agreed. Building a table builder function is much easier than building a query builder function and a table builder function. If you really want the query builder, you have to either code it to know that word X means get column Y or you have to make it look at the database and make an educated guess as to what it should be pulling. – CptMisery Sep 28 '16 at 14:04
1

This is easy to implement but we have to fix few things. Our requirement: 1. Identify Tables according to column name. 2. How we can Join those tables. 3. How to resolve ambiguity of columns.

Solution:

Unique column name for each field or no table has duplicate column name. To achieve it we should have fix table prefix for each table. for example: your column name could be odr_orderid and usr_orderid.

Now by identifying unique prefixes, we can identify tables.

Now issue arises how to join these tables

To resolve it: Create an another table strong JOIN keys and JOin type Left, right,inner or full.

Thats all Now you can make the query as you want.

Narendra Yadav
  • 88
  • 9