Build a calendar like view

[samtwilliams]

Hi All,

 

Just about to embark on taking some data from a mysql DB and pivoting it into a calendar like view. I'm not php or mysql expert so rather than struggle and take days to complete i'd thought i'd explain what i'm trying to do and see if anyone has done similar or has any code snip its.

 

I have data like this;

 

TaskID|Assigned|DueDate

1|User1|01/01/01

2|User2|02/01/01

 

I want to create a dynamic table that pivots the data so that the next thirty days are put into rows, the users are along the top and the tasks a put in the middle;

 

         User1           User2

Date Task1           Task3

         Task2

Date

Date

Date

 

Has anyone achieved anything similar or recommend the best way to achieve it?

 

 

[Jacques1]

This has nothing to do with MySQL. It's a data rendering job.

 

All you have to do is put the data into a more convenient format like a two-dimensional associative array:

[
    'date1' => [
        'user1' => [
            'task1',
            'task2',
            // ...
        ],
        'user2' => [
            // ...
        ],
    ],
    'date2' => [
        // ...
    ],
    // ...
]

Then you can simply iterate over the dates and user to generate your table.

 

As pseudo code

// collect all users separately for easier rendering 
users = []

// load data into new structure
table_data = []
for each row in database_table:
  users[] = row['user']
  table_data[row['date']][row['user']] = row['task']

render_table_headers(users)
for each date, user_tasks in table_data:
  render_date_cell(date)
  for each user in users:
    if user_tasks has key user:
      render_tasks(user_tasks[user])
    else:
      render_empty_cell() 

A quick forum search also helps. This has been discussed to death.