Manipulating date with "00" in it coming from mySQL

Posted by JackM on May 13, 2009, 12:05 pm

Please log in for more thread options

I need a little direction in manipulating a date being pulled from a
mySQL db. It's not something I usually need to do so I am at a loss on
how to accomplish it.

The date is stored in the yyyy-mm-dd format in a field named dt that is
of the date type. Some of the entries are entered as 1970-00-00 or
1970-05-00 where an actual month and/or day are unknown. I am trying to
manipulate those dates to drop the zeros and only print out the year.

I am using $title1 = date('Y F d', strtotime($row["dt"])); to get the
info from the db and it generally works fine when all three parameters
are anything but zeros. I am using that because some of the dates are
pre-1970.

I have tried exploding an array:

$arrayData = explode(" ", trim($title1));
if (strcmp('00', $arrayData[1])){ //skip if arr[1] is 00
if (strcmp('', $arrayData[1])) {
$title1 = date('Y', strtotime($row["dt"]));
}
}

but it didn't work. It strips all of the month/days out. Then I tried:

if $title1(date('F')) == 00 {
$title1(date('F')) = "";
}
if $title1(date('d')) == 00 {
$title1(date('d')) = "";
}

and that didn't work either. Some of the dates that are in the db as
1971-00-00 or 1968-00-00 are coming out as November 30. Ugh.

Can anyone point me in the right direction to do this? Thanks.

Posted by =?ISO-8859-1?Q?=22=C1lvaro_G=2 on May 13, 2009, 12:53 pm

Please log in for more thread options

JackM escribió:
show/hide quoted text

A little hint:

SELECT
        '2009-05-00',
        YEAR('2009-05-00'),
        MONTH('2009-05-00'),
        DAY('2009-05-00'),
        IF(DAY('2009-05-00')=0, YEAR('2009-05-00'), '2009-05-00')

show/hide quoted text

show/hide quoted text
0-1-2. Print your data on screen and see what's inside of it:

var_dump($arrayData)

show/hide quoted text

date('d') is today and if you run it tomorrow it'll be tomorrow; it has
nothing to do with whatever you fetched from DB. And $title1() is a call
to a variable function that doesn't exist, so it should trigger an error.

show/hide quoted text

You don't seem to be very familiar with the PHP syntax. The manual has a
chapter I recommend reading:

http://es.php.net/manual/en/langref.php

Read at least the first sections. You learn more than with most tutorials.

Also, configure your development system to display errors. Find the
php.ini file and edit these directives:

display_errors
error_reporting

--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--

Posted by JackM on May 13, 2009, 2:38 pm

Please log in for more thread options

Álvaro G. Vicario wrote:
show/hide quoted text

Are you saying that I need to run a second SELECT statement for just the
date manipulation? I won't have the exact 2009-05-00 information to do
that with each entry.

I would have already run a query to get the date pulled from the db in
the first place:

"SELECT * FROM table WHERE EXTRACT(YEAR FROM `dt`) < 1971 ORDER BY dt ASC";

I now have that in the $title1 variable as 1969 January 00. Am I
accessing it incorrectly to be able to manipulate it?

Posted by Bart Friederichs on May 14, 2009, 2:16 am

Please log in for more thread options

JackM wrote:

show/hide quoted text

1. Don't do SELECT *. SELECT what you need. Trust me.
2. I'd do WHERE YEAR(dt) < 1971. No idea though if it gives you any
performance or other advantages.

If PHP's date type cannot handle '0' days or months (apparently it
can't), use a simple explode:

$dt = "2009-00-00";
$date = explode("-", $dt);

Then, $date[0] will hold "2009", $date[1] "00" and $date[2] "00". With
intval() you can cast and do arithmetic later.

Good luck.
Bart

Posted by JackM on May 14, 2009, 10:07 am

Please log in for more thread options

Bart Friederichs wrote:
show/hide quoted text

Thanks Bart. I tried your suggestion and I can get it to format the way
that I want. However, in the cases where $date[2] is equal to 00, the
month is printing out as one month earlier than it should.

Example: date coming directly out of the database is 1971-11-00. When
it is printed, it is coming out as 1971 October. Here's what I used to
manipulate it:

$dt = $row["dt"];
$date = explode("-", $dt);

//If array has a number for year and month but not day:
if (($date[1] != 00) && ($date[2] == 00)) {
        $title1 = date('Y F', strtotime($row["dt"]));
}

//If array has no number month and thus cannot have a number for day:
if ($date[1] == 00) {
        $title1 = $date[0];
}

//If array has a number for year and month and day:
if (($date[1] != 00) && ($date[2] != 00)) {
        $title1 = date('Y F d', strtotime($row["dt"]));
}

Where did I make an error to cause the month displayed to be wrong? I
used strtotime because I could not get $date[1] to format correctly
(using full month names) any other way.