当日期字段为空时，通过 PHP 将 NULL 插入到 PostgreSQL 数据库中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8703606/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert NULL into a PostgreSQL DB via PHP when a date field is empty
提问by Matt C
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:
我有一个包含日期字段的 csv 数据集，该字段可能为空，也可能不为空(='')。我已将 Postgres 设置为允许此字段为 null，并为其指定日期格式。当我运行我的 PHP 导入脚本（如下）时，导入失败，因为空字符串不是日期格式。我的理解是我应该将它设置为NULL，只有当该字段实际上为空时我才想要这样做。所以我想有条件地设置它，我认为它会像这样工作：
<?php if (empty($data[3])){
$data[3] = NULL;
// (i've tried "null", 'null', "NULL", null, etc.)
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 Warning: pg_query(): Query failed: ERROR: invalid input syntax for type date: "NULL". PHP 不能将 NULL 传递给 apg_query吗？我应该将条件逻辑放在查询中吗？我的代码如下。非常感谢您的任何建议。
<?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!";}
采纳答案by mu is too short
Your problem is that you have single quotes inside your SQL:
您的问题是您的 SQL 中有单引号：
INSERT INTO product (first_field, second_field, third_field, my_date)
VALUES ('$data[0]', '$data[1]', '$data[2]', '$data[3]')
so if $data[0]is the string "NULL", you'll end up with this:
所以如果$data[0]是 string "NULL"，你最终会得到这个：
INSERT INTO product (first_field, second_field, third_field, my_date)
VALUES ('NULL', ...
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 $datavalues rather than inside your SQL:
并且您将尝试插入包含 NULL 而不是 NULL 文字本身的字符串。您必须在$data值内而不是在 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])) . "'";
}
And then later:
然后后来：
pg_query($_db, "INSERT INTO product (first_field, second_field, third_field, my_date)
VALUES ($data[0], $data[1], $data[2], $data[3])";
Or better, switch to PDOand use prepared statements.
或者更好的是，切换到PDO并使用准备好的语句。
回答by Kouber Saparev
Mind using the NULLIF() PostgreSQL function.
注意使用 NULLIF() PostgreSQL 函数。
<?php
pg_query($_db, "INSERT INTO product (first_field, second_field, third_field, my_date)
VALUES ('$data[0]', '$data[1]', '$data[2]', NULLIF('$data[3]', ''))";
?>
When the arguments are equal, i.e. when '$data[3]' == '', then NULLvalue will be returned.
当参数相等时，即当'$data[3]' == '' 时，将返回NULL值。
回答by Niet the Dark Absol
Use $data[3] = 'null'- null as a string, so that when you insert it in the query it looks like null, not .
使用$data[3] = 'null'- null 作为字符串，这样当您将它插入查询时，它看起来像null，而不是。
(Although, you said you tried that... Are you sure you got the same error for that?)
（虽然，你说你试过了......你确定你有同样的错误吗？）
EDIT: Ah, took a closer look at your error message. Did you define the date field in the database as a "possibly null" value? I'm used to MySQL, mind you, so I could be wrong on that part. But anyway, try inserting '0000-00-00 00:00:00'as a representation of a null date.
编辑：啊，仔细看看你的错误信息。您是否将数据库中的日期字段定义为“可能为空”的值？请注意，我已经习惯了 MySQL，所以我在这方面可能是错的。但无论如何，尝试插入'0000-00-00 00:00:00'作为空日期的表示。
回答by phsaires
The sintax for NULLIF is: NULLIF('$emptysstring','')::int. WHERE "::int" is relative a integer.
NULLIF 的语法是：NULLIF('$emptysstring','')::int。WHERE "::int" 是相对整数。
I hope this help you. Good luck.
我希望这对你有帮助。祝你好运。
