当日期字段为空时，通过 PHP 将 NULL 插入到 PostgreSQL 数据库中 [英] Insert NULL into a PostgreSQL DB via PHP when a date field is empty

问题描述

我有一个包含日期字段的 csv 数据集，该字段可能为空，也可能不为空(='').我已将 Postgres 设置为允许此字段为 null，并为其指定日期格式.当我运行 PHP 导入脚本(如下)时，导入失败，因为空字符串不是日期格式.我的理解是我应该将它设置为 NULL，我只想在该字段实际上为空时才这样做.所以我想有条件地设置它，我认为它会像:

I have a csv data set containing a date field which my may or may not be empty (=''). I've set Postgres to allow null on this field, and have given it the Date format. When I run my PHP import script (below), the import fails because the empty string is not in the date format. My understanding is that I should set it to NULL, which I want to do only if the field is in fact empty. So I want to conditionally set it, which I thought would work like:

    <?php if (empty($data[3])){
          $data[3] = NULL;
         // (i've tried "null", 'null', "NULL", null, etc.)

当我通过这种方法执行导入时，只要日期字段为空，我就会收到 PHP 警告:pg_query():查询失败:错误:日期类型的输入语法无效:NULL".PHP 不能将 NULL 传递给 pg_query 吗?我应该将条件逻辑放在查询中吗?我的代码如下.非常感谢您的建议.

When I execute the import via this method, whenever the date field is empty, I get PHP Warning: pg_query(): Query failed: ERROR: invalid input syntax for type date: "NULL". Can PHP not pass NULL into a pg_query? Should I be putting the conditional logic inside the query instead? My code is below. Thank you very much for any advice.

<?php
$conn_string = "host=localhost port=5432 dbname=mydb user=myusername password=mypassword";
$_db = pg_connect($conn_string);

$fileName = "../feeds/stale_prod_report.20111215.csv";
$row = 0;

if ($_db->connect_error) {
die('Connection Error (' . $_db->connect_errno . ') ' . $_db->connect_error);
}

if (($handle = fopen($fileName, "r")) !== FALSE) {
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
    $num = count($data);
    $row++;
    for ($c=0; $c < $num; $c++) {
        $data[$c] = pg_escape_string(utf8_encode($data[$c]));
    } 

    //if date is empty, insert a dummy - not accepting null
    if (empty($data[16])){
        $data[16] = NULL;
    }

    if($row !== 1){
        pg_query($_db, "INSERT INTO product (first_field, second_field, 
        third_field, my_date) 
    VALUES ('$data[0]', '$data[1]', '$data[2]', '$data[3]')";
}
fclose($handle);

} else {echo "Could not find the file!";}

推荐答案

你的问题是你的 SQL 中有单引号:

Your problem is that you have single quotes inside your SQL:

INSERT INTO product (first_field, second_field, third_field, my_date) 
VALUES ('$data[0]', '$data[1]', '$data[2]', '$data[3]')

所以如果 $data[0] 是字符串 "NULL"，你会得到这个:

so if $data[0] is the string "NULL", you'll end up with this:

INSERT INTO product (first_field, second_field, third_field, my_date) 
VALUES ('NULL', ...

并且您将尝试插入包含 NULL 而不是 NULL 文字本身的字符串.您必须在 $data 值内而不是在 SQL 内进行引用:

and you'll be trying to insert a string that contains NULL rather than the NULL literal itself. You'll have to do your quoting inside the $data values rather than inside your SQL:

# Warning: my PHP is a bit rusty but I think this is right
if(empty($data[0])) {
    $data[0] = "NULL";
}
else {
    $data[0] = "'" . pg_escape_string(utf8_encode($data[$c])) . "'";
}

然后:

pg_query($_db, "INSERT INTO product (first_field, second_field, third_field, my_date) 
    VALUES ($data[0], $data[1], $data[2], $data[3])";

或者更好的是，切换到 PDO 并使用准备好的语句.

Or better, switch to PDO and use prepared statements.

这篇关于当日期字段为空时，通过 PHP 将 NULL 插入到 PostgreSQL 数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！