CODEWITHSUNDEEP
phpsqlite
Cripto
Cripto

Reputation: 3751

PHP + Dynamic Sql Queries

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

Upvotes: 1

Views: 319

Answers (2)

Suleman Ahmad
Suleman Ahmad

Reputation: 2113

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;

Upvotes: 0

Amernath
Amernath

Reputation: 358

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';

Upvotes: 1

Related Questions