php mysql 应用操作数据库实例

<?php
require('./common.php');
$mysql_server_name='localhost'; //改成自己的mysql数据库服务器
$mysql_username='root'; //改成自己的mysql数据库用户名
$mysql_password='123456'; //改成自己的mysql数据库密码
$mysql_database='myrx2'; //改成自己的mysql数据库名


$conn=mysql_connect($mysql_server_name,$mysql_username,$mysql_password) or die("error connecting") ; //连接数据库
 
if(!$conn){
        mysql_error();
}
mysql_query("set names 'utf8'"); //数据库输出编码 应该与你的数据库编码保持一致.南昌网站建设公司百恒网络PHP工程师建议用UTF-8 国际标准编码.
//开始业务逻辑
dump_rukudan_items_fast($mysql_server_name,$mysql_username,$mysql_password,$mysql_database,$conn);
//结束业务逻辑
mysql_close($conn);


function dump_rukudan_items_fast($mysql_server_name,$mysql_username,$mysql_password,$mysql_database,$conn){
$start_time=time();
$test_count=0;
mysql_select_db($mysql_database); //打开数据库
$limit=50000;
$start=0;
//$max_id=622858;
while(true){
//批量获取入库单items和对应库存
$patch_rukuan_items_and_kucun_sql="select rkdi.id as rkd_item_id,rkdi.sssl_s,kc.id as kc_id,rkdi.kcsl as kcsl from tprj_rukudan_items rkdi inner join tprj_kucun kc on rkdi.id=kc.rkd_item_id order by rkdi.id limit ".$start.",".$limit;
// echo $patch_rukuan_items_and_kucun_sql;
// echo "\n";
$patch_rukuan_items_and_kucun=qj($patch_rukuan_items_and_kucun_sql,$conn);
//选出数据的数量
$get_count=count($patch_rukuan_items_and_kucun);
if($get_count<=0){
break;
}
//组装库存id
$kc_ids="";
foreach($patch_rukuan_items_and_kucun as $item){
$kc_ids.=(",".$item['kc_id']);
}
$kc_ids=substr($kc_ids,1);
//查询出库单items
$chukudan_items_sql="select i.kc_id as kc_id,sum(i.material_num) as sssl_s from tprj_chukudan_items i left join tprj_chukudan c on i.ckd_id=c.id where c.ckd_status=1 and i.kc_id in (".$kc_ids.") group by i.kc_id";
$chukudan_items=qj($chukudan_items_sql,$conn);
//组装库存id和出库数量的对应关系
$chukudan_items_map=array();
foreach($chukudan_items as $item){
$chukudan_items_map[$item['kc_id']]=$item['sssl_s'];
}
//遍历出库单items记录需要删除的id
foreach($patch_rukuan_items_and_kucun as $item){
//出库数量
$chuku_count=array_key_exists($item['kc_id'],$chukudan_items_map)?$chukudan_items_map[$item['kc_id']]:0;
//查看库存不对的入库单item的id
                if(($item['sssl_s']-$chuku_count)!=$item['kcsl']){
                        $update_kcsl_sql="update tprj_rukudan_items set kcsl=".($item['sssl_s']-$chuku_count)." where id=".$item['rkd_item_id'];
                        //de($update_kcsl_sql,$conn);
                        $test_count++;
                        echo $item['rkd_item_id']."_".($item['sssl_s']-$chuku_count)."_".$item['kcsl'];
                        echo "\n";
                }
}
$start+=$get_count;
}
$end_time=time();
echo "last time is ".($end_time-$start_time)."and not del is ".$test_count;
echo "\n";
}