MRBS Add On - Need SQL code

[mossj]

Some guy a while ago posted his code for a small MRBS mod, It wasn't working how I like (and not at all which is to be expected as I should think it was taylored to his MRBS install).

 

Anyway I have modified the code to be more general and provide a few more features but I'm stuck at the SQL side of things.

 

I've got it running a show all upcoming bookings from that user query that comes down to..

 

select * from $tbl_entry WHERE create_by='$username' ORDER BY start_time

 

I already know how to run diffrent SQL/php depending on what the user wants to see, However I need code that will create the SQL syntax for the following:

- Show Todays bookings, made by user

- Show Tomorrows, made by user

- Show This Weeks, made by user

- Show Next Weeks, made by user

 

MRBS uses unix times for start, and thats what I can't get my head around.... I know I could pull it from the mrbs code somewhere, but if someone helps me out it will make things alot quicker (IE I cba to look through the code right now)

 

P.S

When its done it will have the following features...

 

- Show bookings made by user for Today, Tomorrow, This Week, Next Week, All

 

I will also be releasing it for download, as its well commented and not to tricky to edit

might add a few other little useful things I'd (or anyone else *hint*) would like it to do....

Edited May 10, 2009 by mossj

[mossj]

Sorted my show today one out using a if statment in the whilst clause and I think I should be able to do this with a few of them.

 

But i would still prefer to filter them at the SQL level as its a faster and more robust way of doing it.

[dgsmith]

Just wondering, is that "some guy" me, who released a pretty much raw file of code to do what you are describing? If so, i'd be interested to hear why you had troubles with it (not that I guaranteed it of course, if it was my code you're describing)

 

What are these extra features too? I'm sure others like myself would be interested if you can implement what you suggest!

 

A complete wild guess, but I were trying to get next day/week etc, i'd query for entries which fall within the current timestamp (now) and how many seconds difference until the day after (maybe with some trial and error), and use that range (ie: time greater than now, but less than X, where X is how many seconds until 24hrs after now) in the sql query to filter it down. Likewise for the next week too.

 

I can't remember the database setup off the top of my head though, though in theory, it should be quite simple i'd have thought.

[mossj]

Just wondering, is that "some guy" me, who released a pretty much raw file of code to do what you are describing? If so, i'd be interested to hear why you had troubles with it (not that I guaranteed it of course, if it was my code you're describing)

...

 

Yes it was, I can't remember exactly what it was.... but I just reread the whole thing and changed it to my naming conventions and the way I qoute things and it kicked in...

 

From what you already had, all I did (and you probley already had this) was add drop down box for selecting different view types, added the code to go with them...

 

I'm sure I will think of the other things to do with it before I'm finished.

 

A complete wild guess, but I were trying to get next day/week etc, i'd query for entries which fall within the current timestamp (now) and how many seconds difference until the day after (maybe with some trial and error), and use that range (ie: time greater than now, but less than X, where X is how many seconds until 24hrs after now) in the sql query to filter it down. Likewise for the next week too.

 

That was the way I though about doing it, however you need to work out the time, and how many seconds/hours/days you can add on, as if someones viewing it at 3pm, they will see bookings from 3pm tomorrow in the table...

 

Anyway I got today/tommorow to work (adding 86400 seconds works on these, as long as you use the date(D M Y) when checking against the DB. Also very close with the week (when I get that working it will be a simple case of adding 86400*7 to the unix date to get into next week and then running the same while loop)

 

P.S Yes i'm leaving your "Booking overview addon by David Smith \ 2009-04-23" at the top

 

P.P.S I suspect modulation of the unix time is the key

Edited May 10, 2009 by mossj

[dgsmith]

That was the way I though about doing it, however you need to work out the time, and how many seconds/hours/days you can add on, as if someones viewing it at 3pm, they will see bookings from 3pm tomorrow in the table...

 

You're quite right - time was getting on a bit then so my brain wasn't in the right gear

 

So, you're converting it to a DMY date and using that to compare with "today"? I'd be interested what you come up with, as I am sure others would be too, though most of our staff only have at most 2 bookings in 1 day! I did encourage anyone to improve what I had, so it's great you are giving that a go and willing to release your changes!

 

What I really, really wanted to do was email reminders (through exchange) - frustrating when they "forget" and I tell another teacher they can't have it because it's taken!