我的查询
SELECT POSSESSION.*,PLOT.*,SCHEME_BLOCK.BLOCK
FROM POSSESSION, PLOT, SCHEME_BLOCK
WHERE POSSESSION.PLOT_ID=PLOT.PLOT_ID AND PLOT.PLOT ='10'
AND PLOT.BLOCK_ID = SCHEME_BLOCK.BLOCK_ID AND SCHEME_BLOCK.BLOCK_ID='3'
AND POSSESSION.CREATED_ON between '1420066800' and '1443650400'
我的PHP代码如下。
$query = "SELECT " . $_REQUEST['tb'] . ".*,PLOT.*,SCHEME_BLOCK.BLOCK
FROM " . $_REQUEST['tb'] . ", PLOT, SCHEME_BLOCK WHERE " . $_REQUEST['tb'] . ".PLOT_ID=PLOT.PLOT_ID AND PLOT.PLOT ='" . $_REQUEST['ps'] . "'
AND PLOT.BLOCK_ID = SCHEME_BLOCK.BLOCK_ID AND SCHEME_BLOCK.BLOCK_ID='" . $_REQUEST['bid'] . "' AND SCHEME_BLOCK.CREATED_ON between '".strtotime(date('d-M-Y', strtotime($_REQUEST['sdate'])))."' and '".strtotime(date('d-M-Y', strtotime($_REQUEST['edate'])))."'";
插入我正在执行此操作的日期时
$CREATED_ON=date("d-M-Y");
错误:
ORA-01843: not a valid month
01843. 00000 - "not a valid month"
*Cause:
*Action:
请帮帮我...我怎样才能把它弄对...我用 php 编码
提前致谢
占有。在"1420066800"和"1443650400"之间CREATED_ON
当你在single-quotes
中有值时,它是一个string
,所以'1443650400'
不是一个DATE
。您必须使用 TO_DATE
和适当的format model
将其显式转换为 DATE 。
首先,这段代码看起来像你并不真正理解的简单复制+粘贴:
strtotime(date('d-M-Y', strtotime($_REQUEST['sdate'])))
它实际上是这样做的:
- 取一个字符串
- 转换为时间戳
- 转换为字符串
- 再次转换为时间戳
无论如何,Oracle不理解DATE列中的Unix时间戳。您必须使用 TO_DATE() 函数来创建适当的日期,当然,还有准备好的语句以使其一切正常。这一行的内容:
$query = "SELECT .........
AND POSSESSION.CREATED_ON between TO_DATE(:created_from, 'YYYY-MM-DD')
and TO_DATE(:created_to, 'YYYY-MM-DD')";
。参数数组如下所示:
$params = array(
'created_from' => date('Y-m-d', $created_from_unix_timestamp),
'created_to' => date('Y-m-d', $created_from_unix_timestamp),
);
。或者这个:
$params = array(
'created_from' => $created_from_datetime_object->format('Y-m-d')
'created_to' => $created_to_datetime_object->format('Y-m-d')
);
不过,您必须知道,没有时间的日期默认为 00:00:00,因此created_on
2014-01-10 01:00:00
的行大于 2014-01-10
。
最后但并非最不重要的一点是,将原始外部输入注入到代码中,如下所示:
"SELECT " . $_REQUEST['tb'] . "...
。是被黑客入侵的召唤。认真地。通常的借口("我稍后会修复它","它只是供内部使用")只是借口。
尝试重新表述您的查询:
$query = "SELECT " . $_REQUEST['tb'] . ".*,PLOT.*,SCHEME_BLOCK.BLOCK
FROM " . $_REQUEST['tb'] . ", PLOT, SCHEME_BLOCK WHERE " . $_REQUEST['tb'] . ".PLOT_ID=PLOT.PLOT_ID AND PLOT.PLOT ='" . $_REQUEST['ps'] . "'
AND PLOT.BLOCK_ID = SCHEME_BLOCK.BLOCK_ID AND SCHEME_BLOCK.BLOCK_ID='" . $_REQUEST['bid'] . "' AND TRUNC(SCHEME_BLOCK.CREATED_ON, 'DD/MM/YYYY') between " . "TO_DATE('" . date('d-m-Y', strtotime($_REQUEST['sdate'])) . "', 'DD/MM/YYYY') and TO_DATE('" . date('d-m-Y', strtotime($_REQUEST['edate'])) . "', 'DD/MM/YYYY')";