Calendar and retrieving dates from database

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Calendar and retrieving dates from database

Post 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
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Calendar and retrieving dates from database

Post 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.
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Calendar and retrieving dates from database

Post 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;
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Calendar and retrieving dates from database

Post 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
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Calendar and retrieving dates from database

Post 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?
(#10850)
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Calendar and retrieving dates from database

Post 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.
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Calendar and retrieving dates from database

Post 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?
(#10850)
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Calendar and retrieving dates from database

Post 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.
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Calendar and retrieving dates from database

Post by Christopher »

Do the tables have similar schemas?
(#10850)
priyankagound
Forum Commoner
Posts: 27
Joined: Thu Sep 19, 2013 2:53 am

Re: Calendar and retrieving dates from database

Post 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.
Post Reply