CODEWITHSUNDEEP
phpmysqldatetime
quentinxs
quentinxs

Reputation: 866

MySQL YEAR(4) and PHP DateTime not playing nice

I have a very rough script for the monotonous task of populating one of my database tables with teams attending different events every year. But it would be a lot nicer if I didn't have to go back and correct the year in the database every time.

I have a table of yearly events that gets queried, which in turn allows the script to fetch the list of teams for that event. The year is set using the following:

$year = date('Y');

For each team-event-year combination the following query fires:

INSERT INTO attendance (`team`,`event`,`year`) VALUES ('$team','$event','$year');

Everything looks fine (to my eye), but the year keeps returning as 0000 in the database even though the script prints 2012 when debugging.

My code is as follows:

// from shared parent script

$year = (date('m') < 9) ? date('Y') : date('Y')+1;

// attendance.php - file with code in question

<?php
header("Refresh: 43200;"); // refresh every 12 hours
ob_start();

echo "<p>Filling event rosters for the {$year} season.</p>"; ob_flush(); flush();

function getTeamList($event) {
    echo "<h3>Event '{$event}':</h3>"; ob_flush(); flush();

    echo "Removing old entries... "; ob_flush(); flush();
    mysql_query("DELETE FROM attendance WHERE event='$event' AND year='$year'");

    echo "Loading list from FIRST TIMS... "; ob_flush(); flush();
    $page =
file_get_contents("https://my.usfirst.org/myarea/index.lasso?page=teamlist&event_type=FRC&event=$event&year=$year&sort_teams=number");
    $page = explode('<td nowrap>',$page);
    $page = implode('<td>',array_slice($page,'1'));
    if ($event == 'CMP') {
        $page2 =
file_get_contents("https://my.usfirst.org/myarea/index.lasso?page=teamlist&event_type=FRC&event=$event&year=$year&sort_teams=number&skip_teams=250");
        $page2 = explode('<td nowrap>',$page2);
        $page2 = implode('<td>',array_slice($page2,'1'));
        $page = '<html><body><center><table>
<tr>
<td>'.$page.' <tr bgcolor="#FFFFFF">
        <td>'.$page2;
    }
    $timsList = new DOMDocument;
    $timsList->loadHTML($page);
    $listings = $timsList->getElementsByTagName('a');

    echo "Adding all current entries... "; ob_flush(); flush();
    foreach ($listings as $listing) {
        if (is_numeric($listing->nodeValue)) {
            $team = $listing->nodeValue;
            $query = "INSERT INTO attendance (`team`,`event`,`year`) VALUES ('$team','$event','$year')";    // Build SQL query for events
            mysql_query($query);
        }
    }
    echo "Done."; ob_flush(); flush();
    if ($event == 'CMP') {
        $div = array('archimedes','curie','galileo','newton');
        foreach ($div as $division) {
            mysql_query("DELETE FROM attendance WHERE event='$division' AND year='$year'");
            $page =
file_get_contents("https://my.usfirst.org/myarea/index.lasso?page=teamlist&event_type=FRC&event=$event&division=$division&year=$year&sort=teamnum");
            $page = explode('<td nowrap>',$page);
            $page = implode('<td>',array_slice($page,'1'));
            $timsList = new DOMDocument;
            @$timsList->loadHTML($page);
            $listings = $timsList->getElementsByTagName('a');
            foreach ($listings as $listing) {
                if (is_numeric($listing->nodeValue)) {
                    $team = $listing->nodeValue;
                    $query  = "INSERT INTO attendance (`team`,`event`,`year`) VALUES ('$team','$division','$year')";    // Build SQL Query
for Divisions
                    mysql_query($query);
                }
            }
        }
    }
}


if (isset($_GET["event"])) {
    $event = strtoupper(mysql_real_escape_string($_GET["event"]));
    mysql_select_db("frc");
    getTeamList($event);
} else {
    mysql_select_db("frc_{$year}scouting");
    $events = mysql_query("SELECT id FROM event WHERE event.start >= NOW()") or die(mysql_error());
    mysql_select_db("frc");
    while ($row = mysql_fetch_array($events)) {
        $event = $row['id'];
        getTeamList($event);
    }
}
?>

Upvotes: 0

Views: 212

Answers (4)

Hugo Delsing
Hugo Delsing

Reputation: 14173

What is the datatype of the year field in MYSQL? If its set as datetime field, then your date is not valid and it will return 0000 for the year. (Invalid dates are stored as 0000-00-00 00:00:00 in mysql datetime)

You could alter the database field to require an INT to make it work. Another option is to send in the year as $year.'-01-01 01:01:01' to store it as the januari first of the date. But considering thats a bogus year, why store the extra data? Just use INT.

UPDATE:

The code you show is correct. Nothing wrong with it. There must be something going on in the code you are not showing, or you are thinking you set everything like you said but havent.

$mysqli = new mysqli(...);

$team = 10;
$event = 'Testing';
$year = date('Y');
$query = "INSERT INTO attendance (`team`,`event`,`year`) VALUES ('$team','$event','$year')";

print $query;
$mysqli->query($query);

Works like a charm using the exact datetypes as you in mysql.

UPDATE 2:

You are using $year in a function, but didnt include global $year;

function getTeamList($event) {
    global $year;
    //....
}

Upvotes: 1

AD7six
AD7six

Reputation: 66299

Year only accepts 1901 - 2155

The MySQL year type only accepts years within a specific range. If you insert an invalid value it's going to return zeros:

mysql> create database so;
Query OK, 1 row affected (0.00 sec)

mysql> use so;
Database changed
mysql> create table example ( y year(4) );
Query OK, 0 rows affected (0.09 sec)

mysql> insert into example values (1234);
Query OK, 1 row affected, 1 warning (0.08 sec)

mysql> select * from example;
+------+
| y    |
+------+
| 0000 |
+------+
1 row in set (0.00 sec)

mysql> insert into example values (2012);
Query OK, 1 row affected (0.07 sec)

mysql> select * from example;
+------+
| y    |
+------+
| 0000 |
| 2012 |
+------+
3 rows in set (0.00 sec)

mysql>

Given that this is how MySQL acts - it is quite likely that the value being inserted is not a number, or not a number within this range.

Upvotes: 1

Lobo
Lobo

Reputation: 4157

If the type of the year field is integer, chage to:

INSERT INTO attendance (`team`,`event`,`year`) VALUES ('$team','$event',$year);

Upvotes: 0

Sashi Kant
Sashi Kant

Reputation: 13465

You can try ::

INSERT INTO 
attendance (`team`,`event`,`year`) 
VALUES  ('$team','$event',YEAR(CURRENT_DATE))

Upvotes: 0

Related Questions