PHP + Dynamic Sql Queries

Question

Image the following data as a result of SELECT * FROM teams;

Country    Games-W    Games-l    ScoreAverage    RedCards    YellowCards
USA        0.18       0.72       0.67            .01         .08
Mecixo     0.28       0.62       0.77            .06         .01
Italy      0.48       0.52       0.87            .07         .00
Spain      0.78       0.22       0.97            .08         .18

I would like to know, how I can have the following.

I know how to implement dynamic sorting for a given column. Using a drop down, the user can select which column to sort on and hit go.

I would like to find a good way to sort on a set of columns without having to write a query for each permutation.

Something like SELECT *, AVERAGE(Columns) as OverAll FROM teams order by OverAll ASCE; where Columns is a set of columns.

The questions is: How do I supply the list of columns? Using html checkbox's? If so, how do I get a list of Columns available (what is the query)?

Are there better approaches to this?

I would prefer if the solution was compatible with SQLite, however, I can change my database if need be.

EDIT:

One of the comments said what do you mean by average?

I mean (1/n)SUM(ai):From i= i:{columns selected} to n = Size|columns selected|

SELECT *, AVERAGE(Game-w, games-l, scoreAverage, redCards, yellowcards) as OverAll FROM teams where Country= 'USA' order by OverAll ASCE; would result in:

Country    Games-W    Games-l    ScoreAverage    RedCards    YellowCards    average
USA        0.18       0.72       0.67            .01         .08            .32

Is you need to show sorted columns on web? Or you just like to sort each columns by just clicking on it from header? — Suleman Ahmad
– Suleman Ahmad, Commented Jul 26, 2013 at 10:51
I can sort a given column on the web. But I need the ability to sort by average of multiple columns — Cripto
– Cripto, Commented Jul 26, 2013 at 11:20
What do you mean with "average of multiple columns"? Show an example! — CL.
– CL., Commented Jul 26, 2013 at 11:36

Fabien TheSolution · Accepted Answer · 2013-07-29 05:41:00Z

1

Using the below query, you can get the table columns.

SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'database_name' AND 
      TABLE_NAME = 'table';

edited Jul 29, 2013 at 5:41

Fabien TheSolution

5,0501 gold badge20 silver badges31 bronze badges

answered Jul 26, 2013 at 10:45

Amernath

3581 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Suleman Ahmad · Accepted Answer · 2013-07-30 06:48:58Z

0

Please try this, I have not tested in your case, but AVERAGE function can be used in ORDER BY clause,

SELECT * FROM teams where Country= 'USA' 
order by AVG(Game-w, games-l, scoreAverage, redCards, yellowcards) ASC;

edited Jul 30, 2013 at 6:48

answered Jul 29, 2013 at 5:28

Suleman Ahmad

2,1235 gold badges28 silver badges43 bronze badges

1 Comment

Suleman Ahmad Over a year ago

@user1048138: please make sure you have removed E from very last.

Collectives™ on Stack Overflow

PHP + Dynamic Sql Queries

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related