Ordering data results in columns instead of rows

Question

Hello,

I have a MySQL table ordered like this :

| user_id |   day   | time | job |    
|--------------------------------|    
|     1   | 2455251 |  08  | T23 |    
|     1   | 2455252 |  14  | T14 |    
|     1   | 2455253 |  22  | T27 |
|     2   | 2455251 |  06  | T16 |
|     2   | 2455252 |  12  | T64 |
|     2   | 2455253 |  20  | T38 |
  etc...

and i want to get the result of my request like this schema :

user_id, day1, time1, job1, day2, time2, job2, day3, time3, job3

the values could be :

1, 2455251, 08, T23, 2455252, 14, T14, 2455253, 22, T27
2, 2455251, 06, T16, 2455252, 12, T64, 2455253, 20, T38
   etc...

I feel unable to make the request... (i'm poor on databases). Do you have an idea ? Did someone of you ever experienced it before ?

Thanks a lot.

Michel L'HUILLIER

Answer 1

Your wish is possible to do, but not with SQL.

You're talking about two distinct ideas: querying data and displaying it. If you have no problem querying, all you have to do now is work on the display.

What software is displaying the database? If you're hoping that the MySQL client will manage this for you, I'm afraid you'll be disappointed. You'll have better luck if it's code that you write.

Where do you want the data to end up? In a file? On a display?

UPDATE: If you're using Ajax to query the database, it's the page's job to format it in the manner you like.

You might still find this to be a challenging chore. (It won't look very good, either.)

It's easy to see how this would look for a handful of rows. But what if the query returns tens? Hundreds? How are you limiting the number of rows that come back?

If I want to get this format from the server, it's because I don't want my javascript to take too much time to render the result on the client-side.

Sounds like you're guilty of premature optimization to me.

If you've got that much data coming back from the server I'd say you've got a bigger problem. Make it work on the client where it belongs and then see if your performance is acceptable.

Here's another idea: I believe this way of rendering operator data will look bad and be of little use to operators. What problem are you really trying to solve here? Ajax and libraries like jQuery UI, YUI, etc. offer some really nice ways to render data that go well beyond what HTML can do. Why not investigate some alternative approaches?

If you can use CSS in PHP, I'd recommend using CSS to position your data. Just load the rows into the DOM and let CSS lay it out.