douzhuo2002 2012-12-04 09:02
浏览 45
已采纳

MySQL YEAR（4）和PHP DateTime不好玩

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);
    }
}
?>
  • 写回答

4条回答 默认 最新

  • ds753947 2012-12-04 09:09
    关注

    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;
    //....
}
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(3条)

报告相同问题？

悬赏问题

  • ¥15 vue3页面el-table页面数据过多
  • ¥100 vue3中融入gRPC-web
  • ¥15 kali环境运行volatility分析android内存文件，缺profile
  • ¥15 写uniapp时遇到的问题
  • ¥15 vs 2008 安装遇到问题
  • ¥15 matlab有限元法求解梁带有若干弹簧质量系统的固有频率
  • ¥15 找一个网络防御专家，外包的
  • ¥100 能不能让两张不同的图片md5值一样，（有尝）
  • ¥15 informer代码训练自己的数据集，改参数怎么改
  • ¥15 请看一下，学校实验要求，我需要具体代码