我尝试将空的MySQL值设置为零。我使用了IFNULL,但它不起作用。对不起,我的英语不好。
<select name="stock_i_h_id" id="stock_i_h_id" class="form-control m-bot15" required>
<option value="" selected="selected">--Select Item --</option>
<?php
$ids=array();
$qry = mysql_query("SELECT IFNULL(stock_i_h_id,0) FROM other_stock_item_creation where userId='".$_SESSION['userId']."' and status_my='0'");
while($row_sid = mysql_fetch_assoc($qry)){
$ids[]=$row_sid["stock_i_h_id"];
}
$st = implode(",",$ids);
$ids2=array();
$qry2 = mysql_query("SELECT IFNULL(stock_i_h_id,0) FROM dpr_stock_item where userId='".$_SESSION['userId']."' and status_my='0'");
while($row_sid2 = mysql_fetch_assoc($qry2)){
$ids2[]=$row_sid2["stock_i_h_id"];
}
$st2 = implode(",",$ids2);
$stock_item_head_name = mysql_query("select * from stock_item_head_name where userId='".$_SESSION['userId']."' and status_my='0' and id not in (".$st.",".$st2.") order by id ASC");
$current_date= date('d-m-Y');
while($rows_stock_item_head_name = mysql_fetch_assoc($stock_item_head_name))
{
?>
<option value="<?php echo $rows_stock_item_head_name['id'] ?>"><?php echo $rows_stock_item_head_name['stock_item_name'] ?></option required>
<?php
}
?>
如果 $qry
和 $qry2
返回空值,则 $st 和 $st 2 都变为空,然后我在第三个查询中得到NOT IN('','')
而不是NOT IN(0,0)
( $stock_item_head_name
)
编辑:当表other_stock_item_creation
和dpr_stock_item
都有条目时,我的代码无需IFNULL
即可正常工作。但是当表为空时,它不适用于IFNULL
或IF(stock_i_h_id = '', 0, stock_i_hd)
像这样做
SELECT if(stock_i_h_id is null,0,stock_i_h_id ) as stock_i_h_id
如果stock_i_h_id
为 null,则返回零,否则返回 stock_i_h_id 的值
列stock_i_h_id
的类型是什么?使其默认为空!
ALTER TABLE `other_stock_item_creation` change `stock_i_h_id` `stock_i_h_id` int(10) unsigned DEFAULT NULL;
比(如果stock_i_h_id
默认值为空):
UPDATE `other_stock_item_creation` set `stock_i_h_id`=NULL WHERE stock_i_h_id='';
并尝试您的代码。
如果值为 NULL
,那么IFNULL
肯定会起作用。您的列必须具有不同的值。如果它是一个字符串,这可能有效:
SELECT IF(stock_i_h_id = '', 0, stock_i_hd) ...