PHP高级开发案例(1):使用MYSQL语句跨表查询无法导出全部记录的解决方案

在实际案例开发过程中，使用mysql跨表查询语句来导出数据库内容。但是经过比对，总是发现导出的记录少于实际的记录。经查发现：用户表的部分用户被删除，导致无法正常读取而缺少导出记录的。

原代码

  //$sql = "select a.pro_id,a.user_id,a.pro_serial,a.pro_name,a.pro_category,a.smallCategory,a.threeCategory,a.user_city,a.user_area,a.pro_declarant,b.user_id,b.user_phone,c.pro_id,c.leader_name,c.leader_phone,(CASE a.pro_audit when 0 then '未审核' when 2 then '区县审核' when 3 then '市级审核' when 4 then '省级审核' when 5 then '审核完成' when -1 then '退回' END) AS pro_audit,(CASE a.user_surveyor_type when 2 then '县属' when 3 then '市属' when 4 then '省属' END) AS user_surveyor_type,d.pro_id,d.agency_name FROM " . $db->table('project') . " AS a, " . $db->table('user') . " AS b," . $db->table('leader') . " AS c";//", " . $db->table('agency') . " AS d ";
        $sql = "select a.pro_id,a.user_id,a.pro_serial,a.pro_name,a.pro_category,a.smallCategory,a.threeCategory,a.user_city,a.user_area,a.pro_declarant,b.user_id,b.user_phone,c.pro_id,c.leader_name,c.leader_phone,(CASE a.pro_audit when 0 then '未审核' when 2 then '区县审核' when 3 then '市级审核' when 4 then '省级审核' when 5 then '审核完成' when -1 then '退回' END) AS pro_audit,(CASE a.user_surveyor_type when 2 then '县属' when 3 then '市属' when 4 then '省属' END) AS user_surveyor_type FROM " . $db->table('project') . " AS a, " . $db->table('user') . " AS b," . $db->table('leader') . " AS c";//", " . $db->table('agency') . " AS d ";
        $sql .= " WHERE a.user_id = b.user_id AND a.pro_id = c.pro_id";//  AND a.pro_id = d.pro_ida.pro_audit  0 AND
        $sql .= " ORDER BY a.pro_id DESC";
        $row = $db->queryall($sql);
        $infoName = "项目信息表" . date("Y.m.d");

解决方案

 case "exp";
        require 'libs/project.export.php';
        $sql = "select pro_id,user_id,pro_serial,pro_name,pro_category,smallCategory,threeCategory,user_city,user_area,pro_declarant,(CASE pro_audit when 0 then '未审核' when 2 then '区县审核' when 3 then '市级审核' when 4 then '省级审核' when 5 then '审核完成' when -1 then '退回' END) AS pro_audit,(CASE user_surveyor_type when 2 then '县属' when 3 then '市属' when 4 then '省属' END) AS user_surveyor_type FROM " . $db->table('project') . " WHERE 1";
        $sql .= " ORDER BY pro_id DESC";
        $row = $db->queryall($sql);

        //读取用户信息;
        foreach ($row as $k => $v) {
            $sql_u = "select user_phone from " . $db->table('user');
            $sql_u .= " WHERE user_id =" . $v['user_id'];
            $row[$k]["user"] = $db->queryall($sql_u);
        }

        //读取负责人信息;
        foreach ($row as $k => $v) {
            $sql_l = "select leader_name,leader_phone from " . $db->table('leader');
            $sql_l .= " WHERE pro_id =" . $v['pro_id'];
            $row[$k]["leader"] = $db->queryall($sql_l);
        }

        //读取机构信息;
        foreach ($row as $k => $v) {
            $sql_a = "select agency_name from " . $db->table('agency');
            $sql_a .= " WHERE pro_id =" . $v['pro_id'];
            $row[$k]["leader"] = $db->queryall($sql_a);
        }
        
        $infoName = "项目信息表" . date("Y.m.d");

@lockdata.cn