Date help !!!!

[billynastie]

Hi back once again.

Problem is I have a field in my mysql database which is a datetime field I am inserting data into the table and viewing it via Datagrid what i am trying to do is to show all forthcoming events upto 2 weeks away but not past events nor any further than 2 weeks away basically it is a football club website and they want to show forthcoming fixtures I am generally having problems error checking the datetime field for comparison and then displaying the relevant data I cannot personally write functions but can understand them and generally im just stuck in a rut with it I am going to give the database structure and also the code I have written even though I know it is a mess if someone could point me in a direction I could possible work from I would be most grateful.

 

Mysql Database table.

CREATE TABLE IF NOT EXISTS `fixtures` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `team` varchar(50) NOT NULL DEFAULT '0',
  `opponent` varchar(50) NOT NULL,
  `location` tinyint(10) NOT NULL DEFAULT '0',
  `match_date` datetime DEFAULT '0000-00-00 00:00:00',
  `description` varchar(255) NOT NULL,
  `postcode` varchar(10) NOT NULL,
  `map` varchar(50) NOT NULL,
  `now` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

 

Php code which ive written.

<?

echo "<br /> <br />";
$w = mktime(0, 0, 0, date("m"), date("d")+15, date("y"));
$now = date("Y-m-d", $w);
$wk = mktime(0, 0, 0, date("m"), date("d"), date("y"));
$today = date("Y-m-d", $wk);

mysql_connect('localhost', 'root', '') or die(mysql_error());
mysql_select_db('season_2008to2009') or die(mysql_error());
//$update = "UPDATE fixtures SET now = NOW()";
//mysql_query($update);
$date = "SELECT *, DATE_FORMAT(match_date, '%Y') as year, DATE_FORMAT(match_date, '%m') as month, DATE_FORMAT(match_date, '%d') as day, DATE_ADD(match_date, INTERVAL 15 DAY) as date FROM fixtures";
$res = mysql_query($date) or die(mysql_error());
$rowdate = mysql_fetch_array($res) or die(mysql_error());
$day = $rowdate['year']."-".$rowdate['month']."-".$rowdate['day'];
$date = $rowdate['date'];
if ( $date <= $now ) {
$query = "SELECT *, DATE_FORMAT(match_date, '%e %M, %Y') as date, DATE_FORMAT(match_date, '%l:%i%p') as time FROM fixtures WHERE now  <= '$today'"; 	 
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result) or die(mysql_error());
echo $row['opponent']. " - ". $row['date'] . " - ". $row['time'] ."<br />";
} else {
echo "The if statement evaluated to false";
}

?>

Cheers for reading.

[billynastie]

Would a IF / IFELSE statement inside a WHILE loop work.a

[Catfish]

I don't know exactly what you're pulling form your MYSQL table with regards to the dates as I haven't done much of that stuff personally, but what is:

 

if ( $date <= $now )

 

? Isn't $date the future date of a fixture? And $now [obviously] is today. Shouldn't $date want to be >= $now if it is a future or current event?

 

 

PS: Can't you build a complete SELECT statement that will only pull relevant data from the fixtures table (fixtures two weeks from today at most) and then simply output that data via PHP?

[billynastie]

Sort of worked it but it still needs working ive added my complete database structure and entries and also the code ive written

--
-- Table structure for table `fixtures`
--

CREATE TABLE IF NOT EXISTS `fixtures` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `team` varchar(50) NOT NULL DEFAULT '0',
  `opponent` varchar(50) NOT NULL,
  `location` tinyint(10) NOT NULL DEFAULT '0',
  `match_date` datetime DEFAULT '0000-00-00 00:00:00',
  `description` varchar(255) NOT NULL,
  `postcode` varchar(10) NOT NULL,
  `map` varchar(50) NOT NULL,
  `now` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

--
-- Dumping data for table `fixtures`
--

INSERT INTO `fixtures` (`id`, `team`, `opponent`, `location`, `match_date`, `description`, `postcode`, `map`, `now`) VALUES
(5, 'Holding Group', 'Beechfield', 1, '2009-08-09 22:33:24', 'sdma dsa<br>', ' ', '', '2009-08-07'),
(6, 'Holding Group', 'Moorside', 1, '2009-08-15 10:41:47', '<br>', ' ', '', '0000-00-00'),
(7, '0', '', 0, '0000-00-00 00:00:00', '', '', '', '0000-00-00'),
(8, 'Holding Group', 'Moorside Blue', 1, '2009-08-23 10:42:18', '<br>', ' ', '', '0000-00-00');

PHP code

<?

echo "<br /> <br />";
$w = mktime(0, 0, 0, date("m"), date("d")+14, date("y"));
$four = date("Y-m-d", $w);
$wk = mktime(0, 0, 0, date("m"), date("d"), date("y"));
$today = date("Y-m-d", $wk);

mysql_connect('localhost', 'root', '') or die(mysql_error());
mysql_select_db('season_2008to2009') or die(mysql_error());

echo "<br />";

$results = "SELECT *, DATE_FORMAT(match_date, '%e %M, %Y') as date, DATE_FORMAT(match_date, '%l:%i%p') as time FROM fixtures";
$queryit = mysql_query($results) or die(mysql_error());
while ($dater = mysql_fetch_array($queryit)){
$check = $dater['match_date'];
$checkit = date("Y-m-d", strtotime($check));
if ($checkit < $today){
echo "No forthcoming fixtures";
} elseif ($checkit > $four)
{
echo "No Forthcoming Fixtures";
} elseif ($checkit <= $four)
{
echo $dater['opponent']. " - ". $dater['date'] . " - ". $dater['time'] ."<br />";
}
else 
{
echo "Something is wrong";
}
}

?> 

what it is doing now is working but because it is cycling through a while loop it is outputting this.

 

 

Beechfield - 9 August, 2009 - 10:33PM

Moorside - 15 August, 2009 - 10:41AM

No forthcoming fixturesNo Forthcoming Fixtures

 

As you can see it shouldn't be displaying the bottom two lines the no forthcoming fixtures i only want it to display the fixture which are fourteen days in advance and no more nothing before todays date and nothing after fourteen days but i also want it to display No Forthcoming Fixtures if there are no forthcoming fixtures that is the only bit im now having problems with.

 

Hope you can help.

[billynastie]

Also thanks for your reply ive tried using INTERVAL within the mysql query but the query has to be broke into two date and time for outputting but im also using strtotime but im only a novice and don't really know properly what im doing hence im doing it a way in which i understand i also understand functions but dont know how to write one properly but im presuming it can by done via a number of methods quite easily.

[billynastie]

Any Help would be helpful I know someone know an easy solution to this.

[billynastie]

Full working code as i did a bit of reading and found a minor solution to make it work would love to hear from anybody who could take what ive writter and either clean it up or write it as a function.

 

<?

echo "<br /> <br />";
$w = mktime(0, 0, 0, date("m"), date("d")+14, date("y"));
$four = date("Y-m-d", $w);
$wk = mktime(0, 0, 0, date("m"), date("d"), date("y"));
$today = date("Y-m-d", $wk);
mysql_connect('localhost', 'root', '') or die(mysql_error());
mysql_select_db('season_2008to2009') or die(mysql_error());
$color="1";
$results = "SELECT *, DATE_FORMAT(match_date, '%e %M, %Y') as date, DATE_FORMAT(match_date, '%l:%i %p') as time FROM fixtures ORDER BY match_date ASC";
$queryit = mysql_query($results) or die(mysql_error());
#
echo '<table width="500" border="1" align="center" cellpadding="2" cellspacing="0"> 
<caption>Forthcoming Fixtures</caption>
<tr>
    <th>Opponent</th>
<th>Home Team</th>
    <th>Match Location</th>
<th>Match Date</th>
<th>Match Time</th>	
  </tr>
  ';
while ($dater = mysql_fetch_array($queryit)){
$check = $dater['match_date'];
$checkit = date("Y-m-d", strtotime($check));
#
if($color==1){
#
echo "<tr bgcolor='#FFC600'>";
if ($checkit <= $four and $checkit >= $today)
{
$loc = $dater['location'];
if ($loc == '1')
{
$location = 'Home';
} else {
$location = 'Away';
}
echo "<td>".$dater['opponent']. "</td><td>".$dater['team']."</td><td>".$location."</td><td>". $dater['date'] . "</td><td>". $dater['time'] ."</td></tr>";
}
#
$color="2";
}
#
else{
#
echo "<tr bgcolor='#CCC600'>";
if ($checkit <= $four and $checkit >= $today)
{
$loc = $dater['location'];
if ($loc == '1')
{
$location = 'Home';
} else {
$location = 'Away';
}
echo "<td>".$dater['opponent']. "</td><td>".$dater['team']."</td><td>".$location."</td><td>". $dater['date'] . "</td><td>". $dater['time'] ."</td></tr>";
}
#
$color="1";
}
}
echo '</table>';
?> 

 

Hope someone reads this and finds it useful to work out a date problem of their own.

[Catfish]

just a quick fix suggestion, if you have it outputting what you want and its outputting "no forthcoming fixtures" simply because it's finding a true condition in your code... just remove the echo() lines outputting "no forthcoming fixtures" ?

 

but i guess you might want to keep your code clean to add additional features in the future that wouldn't groove on that.