Sorting dynamic data to display on specific web table rows.

[Guest]

I am building a simple appt booking application. On one page I have a table, with the first column being appt time blocks of every 15 minutes.

8:00

8:15

8:30

          and so on. I have each time as a link to book an appt passing the time as a url parameter to the booking page( that works fine).

I'm strugging with sorting appointments that exist in the database with the matching time slot on the web page table, making it visually appealing to the end user.

8:00

8:15    some existing appointment

8:30

I would be nice if a repeat region would work, matching to a hidden field with the static time value to the record returned by the SELECT,  but i'm not sure how to coorelate the two. Leaving the open appointments slots blank, making it a single view of their day.

Thanks in advance for any help!

[bregent]

I don't understand. Why are you using a static time value? Why not use the time value stored in the database?

[Guest]

The static value is only on the html table, so that it has the look and feel of an appt book. What I need is a way to coorelate the database time value on the record with the matching static value in the html table, when it displays. So if there is no appointment for that time slot it remains empty.  What I'm trying to avoid is for a 9am appt and a 2 pm appt to show next to each other in the list, without blank rows in between. I hope this is more clear.

Thanks

[bregent]

Right. Don't use static times. Create a database table for all appointment times. Then use an outer join from that table to the appointment table. That will return a recordset with all available times and will include appointment details for those times that have them.

[Guest]

Makes sense, I'll give it a try!

[Guest]

The concept seems right but I think my grasp of implementation is off.

The appts table has the follow columns

appt_hr | clientname | type | appt_day | appt_month | appt_year

I created a table appt_tm where each column is a different 15 min time increment

tm800 | tm815 | tm830  .....and so on up till tm2100

I put in the first row for each column its time,  col (0800) , value 0800, so there would be a data pattern to match on the join. Wrote the Select statement using a LEFT JOIN, joining all time columns 0800 and so on to the one appt_hr in the appts table. The sql is fine, and the web page returns some time data only. I'm not sure how to pull over the clientnames, or if my usage of the join is off track.

Part of my join

SELECT *
FROM appt_tm LEFT JOIN (appts) ON appt_tm.tm800=appts.appt_hr AND appt_tm.tm815=appts.appt_hr AND appt_tm.tm830=appts.appt_hr AND appt_tm.tm845=appts.appt_hr AND appt_tm.tm900=appts.appt_hr AND appt_tm.tm915=appts.appt_hr AND appt_tm.tm930=appts.appt_hr AND ......

Thanks again!

[bregent · Accepted solution]

>I created a table appt_tm where each

>column is a different 15 min time increment

No. Your appt_tm table should have a single column - appt_time. You add a row for each time slot. Then you simply outer join the one appt_time column in the appt_tm table to the appts table.

>appt_hr | clientname | type | appt_day | appt_month | appt_year

Why do you have appt day, month and year columns? Why not just use a date field?

[Guest]

Found time to rebuild the tables and it works great!  The date thing I have gone back and forth on, using mysql date field or storing as int fields, without the need for ISO conversion. I have read good arguments both ways and am on the fence.

Thanks again!