#692 Display more than just the brief description in day view

[Campbell Morrison]

Hi MRBS admins/users.

[Transferred from the mailing list]

This may be a stupid question, but is there a way to have the room bookings table display more than just the brief description for a booking?

I have a setup, but am looking to add information about number of attendees/catering/projector hire to the day view bookings to provide ‘at a glance’ information for people setting up. These I have set up in custom fields and added to the booking form. But I can’t work out if it’s possible to do the next bit!

Any help welcome. I am relatively new to PHP so forgive me if my question is dumb.

[Campbell Morrison]

Do you want to have the extra information there where the brief description is at the moment, or do you just want the extra information to appear when you hover over the booking?

[Anonymous]

Hi,

Thanks for a quick response! I would like it along with the brief description where it is now if possible. Although the hover over option sounds interesting. If it's not loads of work, could you explain both? If it is, just the first option please!

Thanks

Stephen

[Campbell Morrison]

They're both just as easy as each other. The problem with putting the information where the brief description goes is that you'll end up with a lot of text, and so either you won't be able to see it on the page or else you'll need to turn off clipping so that the cell is big enough to show all the text. Anyway, either way the thing to do is to modify the SQL query in functions_table.inc in the function day_table_innerhtml(). The query comes at line 750 in 1.4.11 and looks like this:

  $sql = "SELECT R.id AS room_id, start_time, end_time, name, repeat_id,
                 E.id AS entry_id, type,
                 E.description AS entry_description, status,
                 E.create_by AS entry_create_by
            FROM $tbl_entry E, $tbl_room R
           WHERE E.room_id = R.id
             AND R.area_id = $area
             AND R.disabled = 0
             AND start_time <= $pm7 AND end_time > $am7
        ORDER BY start_time";   // necessary so that multiple bookings appear in the right order

The thing to know is that whatever is in 'name' goes where the brief description is and whatever is in 'entry_description' goes in the title which shows up on hover. So if you had some custom fields you could do something like this:

~~~~
$sql = "SELECT R.id AS room_id, start_time, end_time, name, repeat_id,
E.id AS entry_id, type,
CONCAT(custom1, ' ', custom2, ' ', custom3) AS entry_description, status,
E.create_by AS entry_create_by
FROM $tbl_entry E, $tbl_room R
WHERE E.room_id = R.id
AND R.area_id = $area
AND R.disabled = 0
AND start_time <= $pm7 AND end_time > $am7
ORDER BY start_time"; // necessary so that multiple bookings appear in the right order
~~~

[Stephen Spence]

Great thanks! That works nicely for the hover over option. Most of our rooms are booked for whole days (9-5) so I'm not as worried about the text space as some people might be. I am a bit new to php and sql, so I didn't quite follow how to add the custom fields to the 'name' field. Could you explain again?

Sorry for the dumb questions and thanks again.
S

[Campbell Morrison]

You can do exactly the same with the name field (and could even combine it with the hover option):

  $sql = "SELECT R.id AS room_id, start_time, end_time, CONCAT(name, ' ', custom1, ' ', custom2) AS name, repeat_id,
                 E.id AS entry_id, type,
                 E.description AS entry_description, status,
                 E.create_by AS entry_create_by
            FROM $tbl_entry E, $tbl_room R
           WHERE E.room_id = R.id
             AND R.area_id = $area
             AND R.disabled = 0
             AND start_time <= $pm7 AND end_time > $am7
        ORDER BY start_time";   // necessary so that multiple bookings appear in the right order

~~~~

[Stephen Spence]

That's great, works like a charm. Thanks for all your help!

[Anonymous]

Hi again,

I may be pushing my luck here, but I was wondering if there was a way to insert a line break in the output of my SQL query.

This:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
$sql = "SELECT R.id AS room_id, start_time, end_time, CONCAT_WS(' ',name,number,'Attendees', 'Number of Lunches:', lunch_number,'Projector:', projector, 'Laptop:', laptop,'Conference Phone:', phone,'Flipchart:', flipchart) AS name, repeat_id,
E.id AS entry_id, type,
E.description AS entry_description, status,
E.create_by AS entry_create_by
FROM $tbl_entry E, $tbl_room R
WHERE E.room_id = R.id
AND R.area_id = $area
AND R.disabled = 0
AND start_time <= $pm7 AND end_time > $am7
ORDER BY start_time"; // necessary so that multiple bookings appear in the right order
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

gives me

A Meeting, 10 Attendees, 10 Lunches, Projector: Yes etc

and I would like

A Meeting,
10 Attendees,
10 Lunches,
Projector: Yes
etc

I've tried using CHAR(10) & CHAR (13), which had no effect save to mess up the output and \n doesn't appear to do anything. Again, quite new at this, so I might be coming at this from the wrong angle.

Anyway, if the answer is no, that's fine, its a purely aesthetic thing anyway.

Thanks again.

S

[Campbell Morrison]

What you need is the HTML tag <br >. However that won't work because all output is put through htmlspecialchars() before being displayed as a security measure. (Someone could insert some JavaScript for example in the description which could then do nasty things).

However I think that if you put a "\n" in the CONCAT string and then wrap the htmlspecialchars() call inside a nl2br() call that should work, though I haven't tested it.

The line to change is 448 in functions_table.inc from:

$descr      = isset($cell[0]["data"]) ? htmlspecialchars($cell[0]["data"]) : NULL;

to

$descr      = isset($cell[0]["data"]) ? nl2br(htmlspecialchars($cell[0]["data"]), FALSE) : NULL;

[Stephen Spence]

I thought I needed <br> but I couldn't work out where to put the nl2br so it worked! Thanks for this, I'll let you know if it works.

S

[Campbell Morrison]

It may be that the "\n" doesn't work, in which case try CHAR(10) or CHAR(13).

[Stephen Spence]

Update: Works beautifully with '\n' in the query and nl2br in line 448. Awesome, thank you!