order by, replacing null values with next column

[ahs10]

i have four columns, each of which may have a null value.  i've displayed the data of these columns in a html table so that if the row does not contain in all four columns, it will display the first occurrence of data as the first column.  for example, my database might look like this...

 

+----------+----------+----------+----------+

|    col1    |    col2    |    col3    |    col4  |

+----------+----------+----------+----------+

|    null    |    pink    |    null    |    null    |

+----------+----------+----------+----------+

|    blue    |    null    |    grey  |    null    |

+----------+----------+----------+----------+

 

since the maximum number of columns with data in this set is 2 (second row has blue and grey), my html table would display two columns like this....

 

+----------+----------+

|    col1    |    col2    |

+----------+----------+

|    pink    |            |

+----------+----------+

|    blue    |    grey  |

+----------+----------+

 

now what i would like to do is sort the data from the html table, with up and down arrows as links that just run the mysql query over again but using ORDER BY.  however, i can't sort on col1 because what's displayed in the first row of my html table is actually data in col2 of the db.  how can i do this?

 

basically, i want to sort on col1, but if it comes to a row with a null value in col1, i want it to look at col2, col3, or col4 for data instead.  any help would be greatly appreciated.  thanks!

 

 

[Ken2k7]

Why not structure your table better rather than have all those nulls? Generally when you run into these sticky and hard situations and you have no idea what to do, think about how you can structure your table better so you can easily accomplish your task.

[ahs10]

i agree with you, somehow it is considered a "feature" by others to be able to put the data in any column rather than using some sort of numerical order.  none the yahoo and without a big long explanation that would never work via bbs, that's not an option for me.

 

i did find something that may work.  i could put each column into a php array, then use array_multisort() instead.  it'd be using php to sort instead of mysql.  opinions?