Forum Moderators: open
right, i built a database with a table for a calender which also needed other info, but a calendar was need for different properties so customers could see if a property was already rented or not at the time they would want it.
however i dont think i thought this through very clearly. doh!
so.. in the table there is a field for each day of the year plus a couple of otheres such as property name..
thing is now im having trouble writting an sql statement. and im not sure if what i want to do is actually possible. im hoping it is as would have to start again! what im trying to do is this..
say theres to variables set to say jan15 and feb5 i want it to search all fields from jan15 to feb5 to see if it equals true or false.
is there a way to get an sql statement to just check from one field to the next? so it can say check all fields from var1 to var2 that == true?
or would i have to write a script to work out all the dates between those dates, to dynamicly write the sql for all those date fields to check each individually to == true? the latter i can imagine being abit long winded! so am hoping some one knows how to do the first.
thanks for reading this and any ideas/advice/help would be really appreciated..
thanks,
adrian
jan29 varchar(1),
jan30 varchar(1),
feb1 varchar(1),
feb2 varchar(1)
in english or psuedo, sql would be something like..
select all from table where jan29 to feb2 equals 'y'
but i know if that was in sql that wouldnt work..
would have to do each field individually.. e.g where jan29 == y and where jan30 == 'y' etc.
but im wondering if there is away for it to go from one field then the next and then the next searching for same criteria until it reaches the next stated field, which would be equals 'y'.
hope this makes sense.
adrian
The normal way of doig it is like the following:
SELECT * FROM table WHERE jan29 ='y' OR jan30='y' OR feb1='y' OR . . .
query = "SELECT * from table";
$result = mysql_query($query) or die( "Error");
$row = mysql_fetch_field ($result);
$row will the have the array of your columns.
implode this array of your database fields into a variable to shape your query as follows:
SELECT * FROM table WHERE ". $imploded;
That is roughly how you can do it, if you have specific question on the steps please post them here.
Habtom
so ill need to create somekind of loop to create the sql statement from the two variable dates.. coz the dates from and to will be selected by the web user.
why did they have to create months with different numbers of days in them lol..
thanks for you help by the way :D
so ill need to create somekind of loop to create the sql statement
My initial idea was a loop. But that doesn't seem good to me. If you are having the Month{date} structure, you might need to remember every month has got different number of days.
I think the solution I posted above, is not only shorter, but also is going to ensure that the fieldname you put in your query indeed exist at the backend.
Anyway you proceed, good luck.
[edited by: Habtom at 12:57 pm (utc) on Aug. 15, 2007]
i will only want fields between the "from date" selected and the "to date" selected by the viewer. which could be jan30 to feb2 or even jan28 to feb1.
if i was to do what you suggested and the did where $implode == 'y' i will still get results from dates such as dec. which i wouldnt want.
