使用php将带值和不存在的位置插入mssql - Insert with values and where not exists into mssql using php

Insert with values and where not exists into mssql using php

我在php中有一个代码，它插入到具有给定值的表中，但如果要插入的日期存在，我必须检查要插入的表。如果它确实存在，则不要插入，但如果不存在，则插入。

这是我对PHP 的代码/查询

$querysms = "INSERT INTO tblKMRUN2 (Long,Lat,Account,TrxDateTime,PhilTime,DeviceStatus,Engine,Speed2,PlateNo,ProcStatus)
                                                 values ($longfin,$latfin,$account,'$findate','$findate','$finstatus','$lastengine',$speed,'$pltefin','0')
                                                 where not exist (SELECT * FROM tblKMRUN2 WHERE Long        = $longfin   and
                                                                                                Lat         = $latfin    and
                                                                                                Account     = $account   and
                                                                                                TrxDateTime = $findate   and
                                                                                                PhilTime    = $findate   and
                                                                                                DeviceStatus= $finstatus and
                                                                                                Engine      = $lastengine and
                                                                                                Speed2      = $speed     and
                                                                                                PlateNo    = $pltefin)";

这是我的数据查询

INSERT INTO tblKMRUN2 (Long,Lat,Account,TrxDateTime,PhilTime,DeviceStatus,Engine,Speed2,PlateNo,ProcStatus) 
           values (121.0002,14.3043,353816053658193,'2015-02-11 13:52:19.000','2015-02-11 13:52:19.000','OK','ENGINE OFF',0.10,'AT5000','0')
           select * from tblKMRUN2 
           where not exists (SELECT * FROM tblKMRUN2 WHERE Long = 121.0002 and Lat = 14.3043 and Account = 353816053658193 and TrxDateTime = '2015-02-11 13:52:19.000' and PhilTime = '2015-02-11 13:52:19.000' and DeviceStatus= 'OK' and Engine = 'ENGINE OFF' and Speed2 = 0.10 and PlateNo = 'AT5000')

您的代码没有正确检查特定的行，而是检查整个表，因此它仍然会插入。要解决此问题，您可以使用一个基本函数来检查有多少行符合您的描述，然后决定插入。

试试这个：

where not exists (SELECT * ...

成为

where 1 > (SELECT count(*) ...

希望有帮助：）

插入前检查值。。。

IF EXISTS (
SELECT 1 FROM tblKMRUN2 WHERE
 Long        = $longfin   and
Lat         = $latfin    and
Account     = $account   and
TrxDateTime = $findate   and
PhilTime    = $findate   and
DeviceStatus= $finstatus and
Engine      = $lastengine and
Speed2      = $speed     and
PlateNo    = $pltefin
)
                                                                                                    )
begin
INSERT INTO tblKMRUN2 
(Long,Lat,Account,TrxDateTime,PhilTime,DeviceStatus,Engine,Speed2,PlateNo,ProcStatus)
values     ($longfin,$latfin,$account,'$findate','$findate','$finstatus','$lastengine',$speed,'$pltefin','0')
end
else
begin
select 'values exists'
end