CODEWITHSUNDEEP
phpmysql
user2288983
user2288983

Reputation: 63

Query to retrieve data where a variable is stored in the column name

I have a database that looks like this.

width | 10 | 20 | 30 | 40
----------------------------
  10  | $2 | $3 | $4 | $5
  20  | $3 | $4 | $5 | $6
  30  | $4 | $5 | $6 | $7
  40  | $5 | $6 | $7 | $8

The top row are column names. How would I pull a money value out if i were given the two numerical values?

Or what is the easiest way to reorganise this data into new columns?

EDIT:

The numbers in the column name are heights and the numbers in the width column are widths.

If I were to plug in the numbers 10 for height and 20 for width, I'd want $3 to be my result.

Thank you!

Upvotes: 0

Views: 99

Answers (3)

STT LCU
STT LCU

Reputation: 4330

the best way would be to refactor your database, with a normalized design.

table_1 : heights, contains a single column with the heights you have

table_2 : widths, contains a single column with the widths you have

table_3 : h_w_values, contains 3 columns: 1.height, 2.width, 3.value of the pair

This is normalized, easy to extend for N heights and M widths. Optional: consider a table_4 for the possible values (lets say you have fixed tariffs for your packages, or whatever) and change table_3 to (height, width, tariff_id)

Upvotes: 0

Alfwed
Alfwed

Reputation: 3282

Try this :

// assert $height column exists before the query
$sql = "select {$height}
        from table
        where width = :b_width";

Upvotes: 1

fthiella
fthiella

Reputation: 49049

I think you are looking for something like this:

SELECT
  CASE WHEN @height=10 THEN height10
       WHEN @height=20 THEN height20
       WHEN @height=30 THEN height30
       WHEN @height=40 THEN height40
   END value
FROM yourtable
WHERE
  width=@width

Please see fiddle here.

Upvotes: 0

Related Questions