homepage Welcome to WebmasterWorld Guest from 54.204.231.253
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
calendar table
helenp




msg:4397498
 6:02 pm on Dec 13, 2011 (gmt 0)

Hi,
I have properties with diferent prices, so
I want a calendar table, and I want to have diferent prices for each propery and day.
I already done a table with the dates of 2 years in rows.
The problem is that if posible I would like to have all properties in the same table, as I do calculate all properties together to get the price on all available properties. I dont know how to do it using several tables and how many tables can be joined.
I did a test with that table, adding the name of a property as a column, and inserted the daily price for that property, and it works, I only have to sum the prices for my choosen dates, however I think this is not the correct way to do it.
Anybody have expericene in this?
Thanks in advance.

 

helenp




msg:4397532
 7:38 pm on Dec 13, 2011 (gmt 0)

And the problem with easter, seen script that insert holidays etc. The only dates I need are the dates of the year (including february 28 or 29) and if posible easter, not to change the price of the properties every year for those dates.

jamie




msg:4397558
 9:16 pm on Dec 13, 2011 (gmt 0)

hi helen,

it's difficult to be exact without knowing your exact requirements, but would something like this work:


table_properties
----------------------
id | property_name | description | (any other fields you need)

table_dates
----------------------
property_id | price | date_field

(make sure the date_field is in mysql 'date' format to make easy selections)

getting prices for a property for today would be:

SELECT price FROM table_dates td
LEFT JOIN table_properties tp ON tp.id = td.property_id
WHERE property_id = 123 /* replace with your id */
AND date_field = '2011-12-13'

getting prices for all properties on a particular day would be:

SELECT SUM(price) as total FROM table_dates td
LEFT JOIN table_properties tp ON tp.id = td.property_id
WHERE date_field = '2011-12-13'

getting price for a property for a date range would be:

SELECT SUM(price) FROM table_dates td
LEFT JOIN table_properties tp ON tp.id = td.property_id
WHERE property_id = 123 /* replace with your id */
AND date_field BETWEEN '2011-12-13' AND '2011-12-20'

it sounds like a booking calendar you are making, so you'd need to save the availability as well, which would need a separate table.

one of the problems with programming this sort of application is how to insert data in a user-friendly way. so i would start with creating a form to insert/edit properties and prices. as you start creating this, i am sure other things will occur to you which you need in the database (at least that's the sort of haphazard way i do things ;)

helenp




msg:4397572
 9:53 pm on Dec 13, 2011 (gmt 0)

Dear Jamie,
Thanks,
Without testing it, it looks perfect,
I will try tomorrow, its late here,
and yes it is a booking calendar, but not bookings, only to calculate the prices, owner and client.

I already have an intranet where I insert the bookings etc, however the calculor I have is not flexible and the query is to complicated to change every year.
I have a post with my complicated query 4 or 5 rows below.
However the problem is with easter.....
using this without testing it, I would have to update the prices for easter dates last year and next year on those properties that want special prices.....

helenp




msg:4397600
 11:28 pm on Dec 13, 2011 (gmt 0)

Having a closer look, not sure its ok
This is your proposal:
table_properties
----------------------
id | property_name | description | (any other fields you need)

table_dates
----------------------
property_id | price | date_field

Table properties no problem, however the table dates I think will need values like this:
property_1 50 | property 2 70 | 2012-12-13
property 1 60 | property 2 80 | 2012-12-14
etc.

Or am I just to tired to think cleary?

jamie




msg:4397708
 8:29 am on Dec 14, 2011 (gmt 0)

i would insert the data differently into the dates table, otherwise you are constantly adding columns (property_1, property_2... property_70) each time you add a property. the way below means you only add the property once to the properties table, then each property gets its own row for each date.

you might think that would mean a lot of rows (365 x 70 in the case of 70 properties, but mysql handles that without blinking - make sure you put an index on the property_id column in the dates table)

property_id | price | date_field
--------------------------------
1 | 60.00 | 2012-12-13
2 | 70.00 | 2012-12-13
1 | 60.00 | 2012-12-14
2 | 75.00 | 2012-12-14
1 | 65.00 | 2012-12-15
1 | 65.00 | 2012-12-16
1 | 70.00 | 2012-12-17

(the order of insertion doesn't matter).

it sounds like you'll need a custom form for inserting special dates. something like:

Property: dropdown menu: <option value="1">Property 1</option> etc
From: datepicker
To: datepicker
Price: price field

processing the form, you need to capture the 'from' and 'to' fields, work out all the days between and put them in an array, then update the price for that property on those days.

a quick search gave me this function for getting the days between two dates: [edrackham.com...]

once you have the array of dates, implode(',', $array_dates) to give you the IN() clause for the SQL

UPDATE table_dates SET price = '$_POST['price']' WHERE date_field IN ($imploded_array_dates) AND property_id = $_POST['property_id']

the IN () clause won't work in the code above as dates need to be escaped. it should look like:
IN ('2012-12-13', 2012-12-14'). remember to escape all the POST'ED input as well for security.

helenp




msg:4397709
 8:55 am on Dec 14, 2011 (gmt 0)

Thanks for your help,

Dont understand the implode stuff, isnt it posible to use something like this to change prices, ( a form where I choose the dates )
UPDATE calendar_table_owner
SET price = 514.285 where date_field between '2012-07-01' and '2012-08-31' and property_id = $property?

And how do I insert a new property as I must set the datefield when I insert, and not only one row, I have to insert several rows, this I dont understand?

Edited, ups, see the implode thing is how to get the dates between the selected dates, but as it said update in the query, I suppose I have to insert the id of property, price and date at the same time....

[edited by: helenp at 9:47 am (utc) on Dec 14, 2011]

helenp




msg:4397716
 9:17 am on Dec 14, 2011 (gmt 0)

Think I will add a firth column, "description of the price" such as easter, christmas, low etc, that will make it easier to select to view the prices that are in the database, to get 352 rows for each property is not a good idea :)

jamie




msg:4397763
 11:44 am on Dec 14, 2011 (gmt 0)

hi helen,

where date_field between '2012-07-01' and '2012-08-31'

the between will work fine.

And how do I insert a new property as I must set the datefield when I insert, and not only one row, I have to insert several rows, this I dont understand?

you'll need a separate form to insert a property with the fields you need.

regarding inserting the first dates and prices. you could one form for inserting dates and another for updating.

if you really don't want a separate field for each day and price. then you need a separate table to contain the price periods:

table_prices
--------------
id | name | start_date | end_date

then your table_dates looks like this:

table_dates
--------------
property_id | price_id | price

this does mean every time you select data you are JOINing on another table and off the top of my head i can't figure out how to create the join to select a total price for a range of dates. the row for each date seems the simplest solution to me.

if you are not yet confident submitting/inserting form data to mysql, a quick search found this: [php.about.com...]

all content management in php is based on submitting forms, processing the submitted data and inserting into mysql.

once you are confident inserting data (try it with your real life examples), different solutions will occur as to how you can do it. (there's always more than one way to do it!)

cheers

helenp




msg:4397775
 12:28 pm on Dec 14, 2011 (gmt 0)

to calculate using the end date and start date is what I have at this moment, you can see in below thread, I have a very complex intranet, done by myself, but the calculator I dont like as it uses the same start and end date for everybody, and there are some that want easter special price, and I dont feel like changing this complicated query every year as easter changes dates:
[webmasterworld.com...]

I am better in doing mysql queries than php I think, and dont know if I manage to insert the dates, however will give a try, I do have a script that inserts rows until 2040 however cant use that I think as I need to insert the dates with the rest of information.

Thanks for your help

jamie




msg:4397832
 3:46 pm on Dec 14, 2011 (gmt 0)

even though it may take a bit of doing, i always find programming a user-friendly way of inputting data really helps understand the logic of an application. there's always phpmyadmin, but that's a real pita for lots of rows.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved