Heim > Backend-Entwicklung > PHP-Tutorial > 重复写入数据库之后,同样的查询语句却查询不出结果

重复写入数据库之后,同样的查询语句却查询不出结果

WBOY
Freigeben: 2016-06-23 14:11:42
Original
1074 Leute haben es durchsucht

<?phpset_time_limit(0);  $filepath=$_GET['uploadfile'];require_once '../conn.php';error_reporting(E_ALL ^ E_NOTICE); $line=0;$shipments=0;$i=0;$s=0;$f=0;$strSqla="SELECT MAX(number) FROM `sale_orders_restock`";  //查询sale_orders_restock表id字段最大值$querya = mysql_query($strSqla,$conn); $resultaa= mysql_fetch_row($querya);$maxnum=$resultaa[0];$maxnum++;$file = fopen($filepath,"r") or die("打开文件失败"); while (!feof($file)) { $buffer = fgets($file);if ($line<>0){	$u=explode('	', trim($buffer));if(!empty($u[0])){     $sku=$u[10]; $sql_itemcode="SELECT * FROM `sku_rule` WHERE `sku` = '$sku'";$querya = mysql_query($sql_itemcode,$conn); $rs= mysql_fetch_array($querya);$jj_sku=$rs['item_code'];$delivery_areas=$rs['delivery_areas'];$zhanghao=$rs['account'];  $or_id=$u[0];$or_it_id=$u[1];$payments_date=$u[3];$buyer_email=$u[7];$buyer_name=mb_convert_encoding(addslashes($u[8]), 'UTF-8','utf-8, shft-jis, gbk');$buyer_phone=mb_convert_encoding(addslashes($u[9]), 'UTF-8','utf-8, shft-jis, gbk');$product_name=mb_convert_encoding(addslashes($u[11]), 'UTF-8','utf-8, shft-jis, gbk');$quantity=$u[12];$recipient_name=mb_convert_encoding(addslashes($u[16]), 'UTF-8','utf-8, shft-jis, gbk');$ship_address1=mb_convert_encoding(addslashes($u[17]), 'UTF-8','utf-8, shft-jis, gbk');$ship_address2=mb_convert_encoding(addslashes($u[18]), 'UTF-8','utf-8, shft-jis, gbk');$ship_address3=mb_convert_encoding(addslashes($u[19]), 'UTF-8','utf-8, shft-jis, gbk');$ship_city=mb_convert_encoding(addslashes($u[20]), 'UTF-8','utf-8, shft-jis, gbk');$ship_state=mb_convert_encoding(addslashes($u[21]), 'UTF-8','utf-8, shft-jis, gbk');$ship_postal=mb_convert_encoding(addslashes($u[22]), 'UTF-8','utf-8, shft-jis, gbk');$ship_country=mb_convert_encoding($u[23], 'UTF-8','utf-8, shft-jis, gbk');$cancel_order=0;$logistics_number="";$logistics_company="";$strSql_item="SELECT * FROM `jperp`.`1_item_codes` WHERE `item_code`='$jj_sku'"; $query_item = mysql_query($strSql_item,$conn); $rs=mysql_affected_rows();$kit=0;if($rs>1){while($rs = mysql_fetch_array($query_item) ){$or_it="";$jj_sku=$rs['stock_id'];$item_quantity=$rs['quantity'];$quantity=$quantity*$item_quantity;if($kit>0){$or_it=$or_it_id.$kit;}else{$or_it=$or_it_id;} $sql_sale=" INSERT INTO `amazon_erp`.`sale_orders_restock` (`number` ,`order_id` ,`order_item_id`,`shipments` ,`payments_date` ,`buyer_email` ,`buyer_name`,`buyer_phone_number`,`sku`,`jj_sku`,`product_name`,`quantity_purchased`,`recipient_name`,`ship_address_1`,`ship_address_2`,`ship_address_3`,`ship_state`,`ship_postal_code`,`ship_country`,`account`,`ship_city`,`cancel_order`,`delivery_areas`,`logistics_number`,`logistics_company`)VALUES ('$maxnum','$or_id','$or_it',  '$shipments', '$payments_date','$buyer_email', '$buyer_name','$buyer_phone','$sku' ,'$jj_sku', '$product_name', '$quantity', '$recipient_name', '$ship_address1', '$ship_address2','$ship_address3','$ship_state' , '$ship_postal', '$ship_country', '$zhanghao', '$ship_city','$cancel_order','$delivery_areas','$logistics_number','$logistics_company')";  mysql_query($sql_sale);  $myaf = mysql_affected_rows(); $kit++; if($myaf>0) { echo "import   $or_id    Success  $myaf kit_$kit";echo "<br/>";$s++;}else{ echo "<br/><font color='#FF0000'>"; echo "import   $or_id    Failure  $myaf kit_$kit"; echo "</font>"; echo mysql_error(); echo "<br/>";  $f++; } $i++;}}else{ $sql_sale=" INSERT INTO `amazon_erp`.`sale_orders_restock` (`number` ,`order_id` ,`order_item_id`,`shipments` ,`payments_date` ,`buyer_email` ,`buyer_name` ,`buyer_phone_number` ,`sku`,`jj_sku` ,`product_name` ,`quantity_purchased`,`recipient_name`,`ship_address_1`,`ship_address_2`,`ship_address_3`,`ship_state`,`ship_postal_code`,`ship_country`,`account`,`ship_city`,`cancel_order`,`delivery_areas`,`logistics_number`,`logistics_company`)VALUES ('$maxnum','$or_id','$or_it_id',  '$shipments', '$payments_date','$buyer_email', '$buyer_name','$buyer_phone','$sku' ,'$jj_sku', '$product_name', '$quantity', '$recipient_name', '$ship_address1', '$ship_address2','$ship_address3','$ship_state' , '$ship_postal', '$ship_country', '$zhanghao', '$ship_city','$cancel_order','$delivery_areas','$logistics_number','$logistics_company')";  mysql_query($sql_sale);   $myaf = mysql_affected_rows(); if($myaf>0) {echo "import   $or_id    Success  $myaf";if(empty($jj_sku) or empty($delivery_areas)){ echo "<font color='#FF0000'>"; echo "delivery_areas Or item_code is empty!!!     "; echo "</font>"; }echo "<br/>";$s++;}else{ echo "<br/><font color='#FF0000'>"; echo "import   $or_id    Failure  $myaf"; echo "</font>"; echo mysql_error(); echo "<br/>";   $f++; }  $i++;}}  }  $line++;      }          mysql_close($conn);   //mysql_close($sql_detrde);echo "<br/>";echo "<br/>";echo "<br/>Success: $s";echo "<br/><font color='#FF0000'>";echo "Failure: $f";echo "</font>";echo "<br/>Total: $i";echo "<br/>";echo "<br/>Possible file upload attack!\n";echo '<script>window.open("outputcsv_warehouse_mix.php?number='.$maxnum.'","_blank");</script>';  echo '<script>window.open("output_pdf_mix.php?number='.$maxnum.'","_blank");</script>';  echo '<script>window.open("outputcsv_warehouse_mix_label.php?number='.$maxnum.'","_blank");</script>';  echo '<script>window.open("output_pdf_mix_a4.php?number='.$maxnum.'","_blank");</script>';  ?> 
Nach dem Login kopieren


表结构
id int(11) 否
number int(11) 否
order_id varchar(40) 否
order_item_id bigint(40) 否
shipments int(11) 否 0
payments_date varchar(50) 否
buyer_email tinytext 否
buyer_name tinytext 否
buyer_phone_number varchar(50) 否
sku varchar(50) 否
jj_sku varchar(50) 否
product_name text 否
quantity_purchased int(11) 否
recipient_name tinytext 否
ship_address_1 text 否
ship_address_2 text 否
ship_address_3 text 否
ship_state tinytext 否
ship_postal_code varchar(50) 否
ship_country varchar(50) 否
account varchar(30) 否
ship_city varchar(30) 否
cancel_order int(2) 否
delivery_areas varchar(10) 否
logistics_number varchar(20) 否
logistics_company varchar(40)

查询语句
 $Sql_hkpdf = "SELECT * , group_concat('  ' , concat_ws( ' X ', `jj_sku` , `quantity_purchased` ) , if(`quantity_purchased`> 1, '****' , '  ') ),group_concat('  ' , `order_id` , '  ') FROM ( select * from `sale_orders_restock` order by `jj_sku`) T WHERE `number` =$id AND 1=(select count(*) from `sale_orders_restock` where `recipient_name`=T.`recipient_name`) AND `quantity_purchased`=1 AND `delivery_areas` = 'HKS'  GROUP BY `recipient_name` , `ship_address_1` ORDER BY length(group_concat( ' ' , concat_ws( ' X ', `jj_sku` , `quantity_purchased` ) , ' ' )) DESC,`jj_sku`";  $Result = mysql_query($Sql_hkpdf);
Nach dem Login kopieren


第一次写入数据之后,查询没问题,能查询到相关数据,一但第二次写入重复的数据,同样的查询语句就查询不出结果,都是空值,请问题这是什么原因呢?


回复讨论(解决方案)

1=(select count(*) from `sale_orders_restock` where `recipient_name`=T.`recipient_name`
你能保证 recipient_name 唯一吗?

recipient_name这个不是唯一的,这里面没有唯一的字段,我后面加了个ID字段

我就是需要经常导入重复的数据,才这样的

既然不唯一,那么 1=(select count(*) from `sale_orders_restock` where `recipient_name`=T.`recipient_name`) 在什么时候能成立呢?
当然只在 recipient_name 的值只有一个的时候,否则不就是 2、3、4...了吗

`number` =$id AND 1=(select count(*) from `sale_orders_restock`
这里有个number,类似于批号,每次导入的都是不一样的,第一次导入number=1,第二次就会=2,在每批次的情况进下进行汇总的

number 不会作用到 select count(*) from 里面

xuzuning大大,那要怎么写啊

1=(select count(*) from `sale_orders_restock` where `recipient_name`=T.`recipient_name` and 'number'=$id) 这样??

select count(*) from `sale_orders_restock` where `recipient_name`=T.`recipient_name` and 'number'=$id
的含义是:统计表 sale_orders_restock 中满足 recipient_name`=T.`recipient_name` and 'number'=$id 这个条件的记录数
当重复插入后,记录数就可能大于 1
那么 1=(select ... 就不会成立了

大于1,语句够长,索引要好,不然数据量大了,可能会有慢查询

请问xuzuning大大,我应该怎么改语句才能满足我的需要呢??

问题解决了,xuzuning大大帮我看看,这样应该就可以了吧

SELECT * , group_concat('  ' , concat_ws( ' X ', `jj_sku` , `quantity_purchased` ) , if(`quantity_purchased`> 1, '****' , '  ') ),group_concat('  ' , `order_id` , '  ') FROM ( select * from `sale_orders` where `number` =$id order by `sku`) T WHERE `number` =$id AND 1=(select count(*) from `sale_orders` where `number` =$id and `recipient_name`=T.`recipient_name`) AND `quantity_purchased`=1 AND `delivery_areas` = 'JPS'  GROUP BY `recipient_name` , `ship_address_1` ORDER BY length(group_concat( ' ' , concat_ws( ' X ', `jj_sku` , `quantity_purchased` ) , ' ' )) DESC,`jj_sku`

Quelle:php.cn
Erklärung dieser Website
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Beliebte Tutorials
Mehr>
Neueste Downloads
Mehr>
Web-Effekte
Quellcode der Website
Website-Materialien
Frontend-Vorlage