Page 1 of 2

Show next four weeks...

Posted: Tue Jul 28, 2009 6:06 am
by dan90joe
Hi there,

I'm relatively new to PHP coding and have been working on a new site for a music venue. The events page retrieves information from the database about time, date, cost etc etc etc...

With a bit of help from a friend with the intial coding, , i use the getdate function to show the current months dates:

Code: Select all

//default date info//
$month=$today[month];
$year=$today[year];
//User selected date info//
$mselect=$_GET['mselect'];
$yselect=$_GET['yselect'];
 
//operational mode procedure call for the case statements//
$op=$_GET['op'];
if(!$op){$op='thisday';}
//mode switches for default and user data calling//
switch ($op) 
    {                   
    case "thisday":
    $result = mysql_query("SELECT * FROM `guests` WHERE `month`='$month' AND `year`='$year' ORDER BY `day` ASC");   
    $row=mysql_num_rows($result);
    break;
However, speaking to the venue, they would prefer it if the website showed the next four weeks from the current date rather than just dates in July, for example.

I am unsure how to get data from the database in a way to say *get this next four dates* rather than *get this months dates*.

I hope i'm making sense!!!

Thanks for any help.
Dan

Re: Show next four weeks...

Posted: Tue Jul 28, 2009 6:17 am
by jackpf
Right, so you need all venues in the next four weeks?

In that case, you want all venues from now, to now + 4 weeks.

So, if you put

Code: Select all

//assign the vars here
$time = time();
//4 weeks
$four_weeks = 60 * 60 * 24 * 7 * 4;
$time2 = time() + $four_weeks;
 
WHERE `Date` BETWEEN $time AND $time2
Althought this requires that you keep the `Date` column in the UNIX_TIMESTAMP() or php's time() format.

Hope this helps,
Jack.

Re: Show next four weeks...

Posted: Tue Jul 28, 2009 6:23 am
by dan90joe
Thanks for a quick response jack... I think thats what i need :D however, the guy that gave me a hand said the best thing to do would be to have the different components of the date separate, so i have a day, month and year column in my database... will this still work???

Re: Show next four weeks...

Posted: Tue Jul 28, 2009 6:36 am
by jayshields
dan90joe wrote:Thanks for a quick response jack... I think thats what i need :D however, the guy that gave me a hand said the best thing to do would be to have the different components of the date separate, so i have a day, month and year column in my database... will this still work???
You shouldn't need to keep day, month and year separate. You should consider changing to a single MySQL DATE type field.

Then you can do this:

Code: Select all

SELECT
  *
FROM
  `tbl`
WHERE
  `date`
BETWEEN
  NOW()
AND
  NOW() + INTERVAL 4 WEEK
 
If you've already changed to a UNIXTIMESTAMP type field then you should do your timestamp calculations with MySQL, so jackpf's suggestion would look like:

Code: Select all

SELECT
  *
FROM
  `tbl`
WHERE
  `date`
BETWEEN
  UNIX_TIMESTAMP(NOW())
AND
  UNIX_TIMESTAMP(NOW() + INTERVAL 4 WEEK)
Edited to reflect jackpf's correction below. I'm clearly out of practice!

Re: Show next four weeks...

Posted: Tue Jul 28, 2009 6:50 am
by jackpf
Oh wow I never knew about the mysql interval thing :)

But yeah, that's total nonsense keeping the date, month etc... seperate. With mysql date format or unix timestamp format you can keep them all in one column, and perform calculations like this with it.

Jayshields' method looks pretty nice, but either should work. It just depends which format you want to have your date kept in.

Just to let you know, the mysql function for time() is UNIX_TIMESTAMP() (with an underscore), not UNIXTIMESTAMP(). I'm sure that's what jayshields meant :D

Re: Show next four weeks...

Posted: Tue Jul 28, 2009 6:58 am
by dan90joe
Awesome... Thanks both for your help... I'm now in the process of changing the dates to single colum SQL Date columns... However, it doesnt seem to be working for me...

take a look at http://www.twickfolk.co.uk - list on the right has no info in it...

Also, quick question about the date format... how do i display the results as "21st Sept 2009" when calling them back to the site?

Re: Show next four weeks...

Posted: Tue Jul 28, 2009 7:00 am
by jayshields
What exactly is the problem?

For formatting you'll want to look at DATE_FORMAT() http://dev.mysql.com/doc/refman/5.1/en/ ... ate-format

Re: Show next four weeks...

Posted: Tue Jul 28, 2009 7:06 am
by jackpf
What's your code as well?

You are using mysql_error() aren't you...?

Re: Show next four weeks...

Posted: Tue Jul 28, 2009 7:11 am
by dan90joe
Christ, now all my previous coding has gone tits up........ Because i've deleted the Day, Month and Year columns, where i previously used this code:

Code: Select all

SELECT * FROM `guests` WHERE `month`='$month' AND `year`='$year' ORDER BY `day` ASC"
i now need to find another way of doing what the Guests page is supposed to do. I want it to show the next four weeks, but also for a user to be able to select a custom month and show just those dates... This is getting rather complicated now!!

Thanks for your help,
Dan

Re: Show next four weeks...

Posted: Tue Jul 28, 2009 7:20 am
by dan90joe
Right... Now that i've shut the website down, i can relax and explain myself a bit better...

Okay, so previously, i had two pages that accessed the same database... index.php (the sidebar bit) and guests.php which shows a more in depth version with a description of the artist...

On the guests page, i wanted to have a selector where the user can change the month and view september (for example)

We used this code...

Code: Select all

//call of apache/unix date information//
$today = getdate();
 
//default date info//
$month=$today[month];
$year=$today[year];
//User selected date info//
$mselect=$_POST['mselect'];
$yselect=$_POST['yselect'];
 
//operational mode procedure call for the case statements//
$op=$_POST['op'];
if(!$op){$op='thisday';}
//mode switches for default and user data calling//
switch ($op) 
    {                   
    case "thisday":
    $result = mysql_query("SELECT * FROM `guests` WHERE `month`='$month' AND `year`='$year' ORDER BY `day` ASC");   
    $row=mysql_num_rows($result);
    break;
    
    case "userday":
    $result = mysql_query("SELECT * FROM `guests` WHERE `month`='$mselect' AND `year`='$yselect' ORDER BY `day` ASC");  
    $row=mysql_num_rows($result);
    break;
    }
 
?>
This worked fine, but had its limitations and the club wanted to be able to see 4 weeks in advance rather than just this month. They way you've shown me seems to be the solution, but now i need a way to use the drop-down menu on guests.php to extract the "month" from the SQLDate function so that the userday case still works...

The thing on the front page doesnt seem to like the code however it isnt coming up with any errors which is a good sign i suppose, perhaps its just the way that i'm trying to call it that isnt working...

Code: Select all

switch ($op) 
    {                   
    case "thisday":
    for ($z==1;$z<$row;$z++) {
    $item=mysql_fetch_row($result);
    echo"
                        <li class='first'>
                        <h3>$item[1]</h3>
                        <p>$item[0]</p>
                        </li>
    ";  
    };
Hope this explains a bit better now... Had a bit of a freak out a few mins ago...

EDIT: p.s. the index.php file can now be found at http://www.twickfolk.co.uk/eindex.php

Re: Show next four weeks...

Posted: Tue Jul 28, 2009 7:31 am
by jackpf
Right well...with a bit of maths you should still be able to achieve that.

Say someone chooses february. You could use strtotime() to get the timestamp for that month.

Here's a little example I wrote - hope it helps. You should be able to do the same sort of thing for days and years as well.

Code: Select all

 
//the month to get records for
$month = 'January';
 
//the unix stamp for the start of the month
$time = strtotime('1 '.$month.' 2009');
 
//array consisting of months and the number of days
$months = array(
'January'   => 31,
'February'  => ((bool) date('L', $time)) ? 29 : 28),
'March'     => 31,
'April'     => 30,
'May'       => 31,
'June'      => 30,
'July'      => 31,
'August'    => 31,
'September' => 30,
'October'   => 31,
'November'  => 30,
'December'  => 31
);
 
//the unix stamp for the end of the month (using the array to get the last day of the month)
$time2 = strtotime($months[$month].' '.$month.' 2009');
 
 
/*
 Then you should be able to do the same thing with your SQL
 
 WHERE `Date` BETWEEN $time AND $time2
 
 */
 

Re: Show next four weeks...

Posted: Tue Jul 28, 2009 7:39 am
by dan90joe
Jack, thanks for the response... I have to pop out for a bit but will have a play when i get back... Think i may have severely cocked it up for the moment and its going to need some thought to fix! LOL.

Thanks
Dan

Re: Show next four weeks...

Posted: Tue Jul 28, 2009 7:54 am
by jackpf
Ahh it's fixable. Probably.... :P

Let me know ow it works.

Re: Show next four weeks...

Posted: Tue Jul 28, 2009 9:52 am
by dan90joe
Right, it took me a while, but i've managed to put the website back to the state it was in before i started messing around. I figured it wouldnt be that destructive so didnt make a backup of the database...

I'm going away tomorrow so the website has to be sorted and working at least... I've made a second database for me to mess around with while i'm away, which shouldnt effect the website in any way. I realise now this is what i should have done in the first place really!!!

I'll let you guys know how I get on!!

Re: Show next four weeks...

Posted: Tue Jul 28, 2009 10:08 am
by pickle
If the user selects a month (1-12) and a year, you can use the MySQL functions MONTH and YEAR:

Code: Select all

SELECT
  *
FROM
  events
WHERE
  YEAR(event_time) = '$year' AND
  MONTH(event_time) = '$month'