Page 1 of 1
Calendar and retrieving dates from database
Posted: Tue Oct 15, 2013 4:16 am
by social_experiment
I'm creating code that will search multiple tables (at least 10) for dates matching the days on a calendar.
My two main concerns are 1) how to search multiple tables in a single sitting and 2) where in my calendar creating code to do the searching. I think i have point 2 covered, i will do the searching inside the code that creates the days ( below the dotted line in the following code )
Code: Select all
<?php
if ($j <= $this->_numberOfDays) {
// here ? search here ?
// --------------------------------------
// 1. the days of the month are printed here <------
// each day is checked inside this loop.
// 2. i have the value of $j {value per day} which i can
// use to search against the database. If i hit a match, i
// need to make the day value a link.
// 3. what will my search function look like?
if ($j != $this->_currentDay) {
echo '<td class="days">'. $j .'</td>';
// or in here ? why in here ?
}
else {
// or in here ? why in here ?
echo '<td class="active">'. $j .'</td>';
}
}
?>
Any ideas on how to go about the search would be welcome
thanks
Re: Calendar and retrieving dates from database
Posted: Tue Oct 15, 2013 3:26 pm
by requinix
...At least ten tables? What's the difference between them?
And what's the rest of your code? It'd be much more efficient to do all the searching at once before you start generating the calendar, rather than do ~30 searches for each day.
Re: Calendar and retrieving dates from database
Posted: Wed Oct 16, 2013 12:39 am
by social_experiment
requinix wrote:...At least ten tables? What's the difference between them?
from the explanation i received each table will be for a specific type of event i.e concerts, meetings, festivals, etc.
there is no rest of my code yet

i need to figure out where to do the searching before i start creating the code;
Re: Calendar and retrieving dates from database
Posted: Thu Oct 17, 2013 2:53 am
by social_experiment
I've come up with an idea of how to do the database searching prior to creating the calendar:
I'll have an array of days of the month and loop through it; each iteration will search against the database and if a match is found for the particular day another array is populated, something like 1 => 1 if there is an event on the first and 2 => 0 if there isn't.
Still not sure about the details but seems like an option imo
Re: Calendar and retrieving dates from database
Posted: Thu Oct 17, 2013 3:03 pm
by Christopher
Wouldn't it make more sense to search the tables for all records with dates in the requested month -- then build the calendar with all that data?
Re: Calendar and retrieving dates from database
Posted: Thu Oct 17, 2013 4:14 pm
by social_experiment
Christopher wrote:Wouldn't it make more sense to search the tables for all records with dates in the requested month -- then build the calendar with all that data?
indeed, my problem is how to use the results that i find to construct the calendar; if there is an event on a specific day, that day will be click-able. So my thoughts with the previous comment was that i would search the tables for events on day X and if any matches are found i'd create an array, giving it a value of 1 and 0 for days with no events. From the i'd know which days on the calendar would need to be links to a page containing all the events.
Re: Calendar and retrieving dates from database
Posted: Thu Oct 17, 2013 11:39 pm
by Christopher
I think I would initialize an array with all the days in the month, then read in all records from the ten tables and assign them to the array as they are read (maybe adding their type). Then you have all the data and can just loop through the days array to generate the calendar.
Is there a way you can JOIN the tables and read them in one query to reduce the overhead of multiple queries?
Re: Calendar and retrieving dates from database
Posted: Sat Oct 19, 2013 1:06 am
by social_experiment
Christopher wrote:Is there a way you can JOIN the tables and read them in one query to reduce the overhead of multiple queries?
that's an option, I haven't worked with JOIN as much as i would have liked to but for this case it seems to be a good option.
Re: Calendar and retrieving dates from database
Posted: Sun Oct 20, 2013 11:12 pm
by Christopher
Do the tables have similar schemas?
Re: Calendar and retrieving dates from database
Posted: Mon Oct 21, 2013 5:56 am
by priyankagound
Try out with the below code, it has worked fine for me:
Code: Select all
$table=date,field name=date1,date2 , datatype=datetime
$sql="select date1 from date;
$result=mysql_query($sql) or die(mysql_error());
if($row=mysql_fetch_array($result))
{
$cr_date=date_create($row['date1']);
$for_date=date_format($cr_date,'d-m-Y H:i:s');
}
print $for_date;
Hope this helps.