Pre 1901 dates

[MargateSteve]

Been a while since I hit a snag but have a new one!

 

I have hit the old problem that I need to be able to input dates pre 13th December 1901 which appears to not work when using strtotime

<?php echo date("F j, Y ", strtotime($row_seasonview['season_start'])); ?>

 

Following a bit of googling it seemed that the alternative was to set the format in the query

SELECT *,
DATE_FORMAT(`season_start`,'%D %M %Y') AS startdate,
DATE_FORMAT(`season_end`,'%D %M %Y') AS enddate 
FROM seasons 
WHERE season_id = %s

and to call the date from that

<?php echo $row_seasonview['startdate']; ?>

but this just comes back with 'Query was empty'. If I remove the two DATE_FORMAT lines the query runs fine.

 

I know there must be a way round this but cannot fathom it out. Any suggestions would be greatfully welcomed!!!

 

Thanks in advance

Steve

[Pikachu2000]

How are they stored in the database; what format, and what is the data type for those fields?

[PFMaBiSmAd]

The "query was empty error" is because the string you put into the mysql_query() statement was empty or the wrong variable name. You would need to post your code if you want help with the error.

 

If you have pre 1901 dates stored as DATE or DATETIME data types, using DATE_FORMAT() should work.

[MargateSteve]

Thanks for the quick responses.

The table I am working with (with a few rows) is

CREATE TABLE `seasons` (
  `season_id` int(11) NOT NULL auto_increment,
  `season_name` char(9) default NULL,
  `season_start` date default NULL,
  `season_end` date default NULL,
  PRIMARY KEY  (`season_id`)
) TYPE=MyISAM AUTO_INCREMENT=119 ;

-- 
-- Dumping data for table `seasons`
-- 

INSERT INTO `seasons` VALUES (1, '1896/97', '1896-07-01', '1897-06-30');
INSERT INTO `seasons` VALUES (2, '1897/98', '1897-07-01', '1898-06-30');
INSERT INTO `seasons` VALUES (3, '1898/99', '1898-07-01', '1899-06-30');
INSERT INTO `seasons` VALUES (4, '1899/00', '1899-07-01', '1900-06-30');
INSERT INTO `seasons` VALUES (6, '1901/02', '1901-07-01', '1902-06-30');
INSERT INTO `seasons` VALUES (8, '1903/04', '1903-07-01', '1904-06-30');
INSERT INTO `seasons` VALUES (9, '1904/05', '1904-07-01', '1905-06-30');
INSERT INTO `seasons` VALUES (10, '1905/06', '1905-07-01', '1906-06-30');

 

The page itself is a VIEW RECORD page called seasonview.php and it is linked to from the LIST page with

<a href="seasonview.php?season_id='.$row['season_id'].'"><img src="../../images/icons/Search.png" height="20"  alt="View" /></a>

 

The code using strtotime is

SELECT * 
FROM seasons 
WHERE season_id = %s

and

<tr valign="baseline"><td nowrap="nowrap" align="right">Season_start:</td>
          <td><?php echo date("F j, Y ", strtotime($row_seasonview["season_start"]))  
	  ; ?></td>
        </tr>
        <tr valign="baseline">
          <td nowrap="nowrap" align="right">Season_end:</td>
          <td><?php echo date("F j, Y ", strtotime($row_seasonview["season_end"]))  
	  ; ?></td></tr>

but anything pre-1901 shows up as 'December 13, 1901'

 

When I try to format it via the query I use

SELECT *,
DATE_FORMAT(`season_start`,'%D %M %Y') AS startdate,
DATE_FORMAT(`season_end`,'%D %M %Y') AS enddate 
FROM seasons 
WHERE season_id = %s

and

  <tr valign="baseline">
          <td nowrap="nowrap" align="right">Season_start:</td>
          <td><?php echo $row_seasonview['startdate']; ?></td>
        </tr>
        <tr valign="baseline">
          <td nowrap="nowrap" align="right">Season_end:</td>
          <td><?php echo $row_seasonview['enddate']; ?></td>
        </tr>

but that is when I get 'Query was empty'

 

Any suggestions as to what I am doing wrong?

Thanks

Steve

[Pikachu2000]

Since season_id should be numeric, try it with: WHERE season_id = %d

[MargateSteve]

Since season_id should be numeric, try it with: WHERE season_id = %d

I just tried that but it still came back with an empty query. Really is puzzling me.

Steve

[Pikachu2000]

Post the entire code chunk in which the query string is formed. There has to be more going on here.

[MargateSteve]

I have had a little play around and think I have done more harm than good and have completely messed the query up. The page now shows the table but without the data in it instead of showing a blank page with 'Query was empty'.

My SQL is

<?php
mysql_select_db($database_Test, $Test);
$seasonview = "SELECT *,
DATE_FORMAT(`season_start`,'%D %M %Y') AS startdate,
DATE_FORMAT(`season_end`,'%D %M %Y') AS enddate 
FROM seasons WHERE season_id = %d";
$result_seasonview = mysql_query($seasonview);
$row_seasonview = mysql_fetch_assoc($result_seasonview);
?>

and the html to show it is

<table align="center">
        <tr valign="baseline">
          <td nowrap="nowrap" align="right">Season_id:</td>
          <td><?php echo $row_seasonview['season_id']; ?></td>
        </tr>
        <tr valign="baseline">
          <td nowrap="nowrap" align="right">Season_name:</td>
          <td><?php echo $row_seasonview['season_name']; ?></td>
        </tr>
        <tr valign="baseline">
          <td nowrap="nowrap" align="right">Season_start:</td>
          <td><?php echo $row_seasonview['startdate']; ?></td>
        </tr>
        <tr valign="baseline">
          <td nowrap="nowrap" align="right">Season_end:</td>
          <td><?php echo $row_seasonview['enddate']; ?></td>
        </tr>
             </table>

 

Can anyone sort out the mess I have made???

 

Thanks as always

Steve

[Pikachu2000]

Where is the value for season_id supposed to be coming from? You have the query string set up as though you want to use printf() with it, but no value to use.

 

Perhaps a quick overview of what you're trying to accomplish would be helpful, such as what happens immediately before arriving at this script, and what it should output.

[MargateSteve]

The value is passed from a LIST page using

<code><a href="seasonview.php?season_id='.$row['season_id'].'"><img src="../../images/icons/Search.png" height="20"  alt="View" /></a></code>

 

The list page has all of the seasons on it and the page with the query and code that I posted before is the VIEW page to show the details of one record from the list.

 

The query works fine if I do not use DATE_FORMAT, but then strtotime in the php to echo the data wont show dates before 1901.

Steve

[Pikachu2000]

The query can't work simply by removing the DATE_FORMAT()s; there's no value assigned in the WHERE clause.

 

If you haven't assigned a value from the GET var to a variable and validated/sanitized it:

$season_id = !empty($_GET['season_id']) && is_numeric($_GET['season_id'] ) ? (int) $_GET['season_id'] : '';

 

Then this query string should do the trick:

$seasonview = "SELECT *,
DATE_FORMAT(`season_start`,'%D %M %Y') AS startdate,
DATE_FORMAT(`season_end`,'%D %M %Y') AS enddate
FROM seasons WHERE season_id = $season_id";

 

 

[MargateSteve]

That works perfectly.

 

The strange thing is that when the query was just

<php>SELECT *

FROM seasons

WHERE season_id = %s</php>

without the extra line of code, the page returned the correct data but gave the problem with old dates.

 

I have also used 'WHERE xxx = %s' in quite a few other pages and that worked fine so was really puzzled why it did not work here.

 

I will now go away and try to understand that new line of code!!

 

Thanks as always

Steve