Show next four weeks...

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

dan90joe
Forum Newbie
Posts: 7
Joined: Tue Jul 28, 2009 5:53 am
Location: London / Liverpool - UK

Show next four weeks...

Post 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
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Show next four weeks...

Post 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.
dan90joe
Forum Newbie
Posts: 7
Joined: Tue Jul 28, 2009 5:53 am
Location: London / Liverpool - UK

Re: Show next four weeks...

Post 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???
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: Show next four weeks...

Post 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!
Last edited by jayshields on Tue Jul 28, 2009 6:58 am, edited 1 time in total.
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Show next four weeks...

Post 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
dan90joe
Forum Newbie
Posts: 7
Joined: Tue Jul 28, 2009 5:53 am
Location: London / Liverpool - UK

Re: Show next four weeks...

Post 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?
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: Show next four weeks...

Post 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
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Show next four weeks...

Post by jackpf »

What's your code as well?

You are using mysql_error() aren't you...?
dan90joe
Forum Newbie
Posts: 7
Joined: Tue Jul 28, 2009 5:53 am
Location: London / Liverpool - UK

Re: Show next four weeks...

Post 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
dan90joe
Forum Newbie
Posts: 7
Joined: Tue Jul 28, 2009 5:53 am
Location: London / Liverpool - UK

Re: Show next four weeks...

Post 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
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Show next four weeks...

Post 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
 
 */
 
dan90joe
Forum Newbie
Posts: 7
Joined: Tue Jul 28, 2009 5:53 am
Location: London / Liverpool - UK

Re: Show next four weeks...

Post 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
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Show next four weeks...

Post by jackpf »

Ahh it's fixable. Probably.... :P

Let me know ow it works.
dan90joe
Forum Newbie
Posts: 7
Joined: Tue Jul 28, 2009 5:53 am
Location: London / Liverpool - UK

Re: Show next four weeks...

Post 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!!
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Show next four weeks...

Post 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'
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply