抓取表中满足条件的数据及图片

1、config.ini

;-*- coding=utf-8 -*-
;数据库配置
[db]
db_host = 10.10.21.120
db_user = dev
db_pass = 123456
db_name = findgame

tablePrefix = princessgames

;采集视频保存路径
;store_path = E:/wamp/www/findgame-com/xoxokids/web/html/store/
old_img_path = E:/wamp/www/findgame-com/gameomgme/web/html/store/cover/
new_img_path = E:/wamp/www/findgame-com/princessgames.me/web/html/store/cover/

2、db.php

<?php
$file = 'config.ini';
$iniArr = parse_ini_file($file);

$con = @mysql_connect($iniArr['db_host'], $iniArr['db_user'], $iniArr['db_pass'],$iniArr['db_name']);
if(!$con) {
   die('Could not connect: '.mysql_error());
}
mysql_select_db($iniArr['db_name'], $con);

/**
 * 生成文件存储路径 90/code/
 */
function getNewGameUrl($code, $type=true) {
    $mdCode = md5($code);
    if($type) {
        return substr($mdCode, strlen($mdCode) - 2, 2).'/';
    }
    return substr($mdCode, strlen($mdCode) - 2, 2).'/'.$code.'/';
}

/**
 * 过滤斜线
 *
 * @param string
 * @param string  $string
 * @return string
 */
function q($string) {
    if ($string == '' and $string != 0) {
        return 'NULL';
    } else {
        if (get_magic_quotes_gpc()) $string = stripslashes($string);
        return "".addslashes($string)."";
    }
}

/**
 * 创建文件夹
 */
function createDir($path) {
   if(!file_exists($path)) {
      mkdir($path, 0755, true);
    }else {
        return true;
    }
    //ccc($path, 0777);
    if (is_dir($path)) {
        return true;
    }
    return false;
}

/**
 * 赋予文件夹权限
 */
function ccc($path, $u = 'nobody', $g = 'nobody') {
   $ignore = array('.', '..');
   $dh = @opendir($path);
   chown($path, $u);
   chgrp($path, $g);
   while (false !== ($file = readdir($dh))) {
      if (!in_array($file, $ignore)) {
         $d = "{$path}/{$file}";
         chown($d, $u);
         chgrp($d, $g);
         if (is_dir($d)) {
            chmod($d, 0755);
            ccc($d, $u, $g);
         } else {
            chmod($d, 0664);
         }
      }
   }
   closedir($dh);
}
3、抓取脚本
<?php
/*
 * @name
 * @author Aimee
 * @version 1.0
 */
require_once dirname(__FILE__).'/db.php';

date_default_timezone_set('Asia/Shanghai');

//define('OLD_PATH', $iniArr['gamePath']);
define('OLD_IMG_URL', $iniArr['old_img_path']);

//define('CHANNEL_IMG_URL', $iniArr['channel_img_path']);
define('NEW_IMG_URL', $iniArr['new_img_path']);

define('DATABASE', $iniArr['db_name']);
define('TABLEPREFIX', $iniArr['tablePrefix']);

class Migrate {
   public $database = DATABASE;//原数据所在库
   public $tablePrefix = 'gameomgme';
   public function game() {
      //$sql = "select * from ".$this->database."._games where published=1 and featured=1 order by id asc";
        $sql_name = 'select * from '.$this->tablePrefix.'_game where name like "%Frozen%" or name like "%Sofia%" or name like "%Rapunzel%" or name like "%Strawberry%" or name like "%Ariel%" or name like "%Snow White%" or name like "%Cinderella%"';
        $query_name = mysql_query($sql_name);
        $get_gamesId = '';
        while($rs_name = mysql_fetch_assoc($query_name)){
            $get_gamesId .= $rs_name['id'].',';
        }
        $new_gamesId = substr($get_gamesId,0,strlen($get_gamesId)-1);//按游戏名称查找的满足条件的游戏id组成的字符串

        $this->insert_game($sql_name);

        //tags表与tag_relations表关联查询
        $sql_tagid = 'select game_id from '.$this->tablePrefix.'_tag_relations r left join '.$this->tablePrefix.'_tags t on r.tag_id=t.id
        where t.tag_name="Dessert" or t.tag_name="Sofia" or t.tag_name="Rapunzel" or t.tag_name="Strawberry" or t.tag_name="Ariel" or t.tag_name="Snow White" or t.tag_name="Cinderella"';

        $query_tagid = mysql_query($sql_tagid);

        $gameid_str = '';
        while($rs_tagid = mysql_fetch_assoc($query_tagid)){
            $gameid_str .= $rs_tagid['game_id'].',';
        }
        $newstr = substr($gameid_str,0,strlen($gameid_str)-1);

        $sql_game = 'select * from '.$this->tablePrefix.'_game where id in('.$newstr.') and id not in('.$new_gamesId.')';//已插入新表的游戏不查找

        $this->insert_game($sql_game);
        //$moveImgError = array();
      //$this->log("Image move error id:".implode(',',$moveImgError));
   }

    //满足条件的数据插入表中
    function insert_game($sql){
        $flag = false;
        $query = mysql_query($sql);
        while ($row = mysql_fetch_assoc($query)) {
            $row['name'] = addslashes($row['name']);
            $row['code'] = addslashes($row['code']);
            $row['description'] = addslashes($row['description']);
            $row['cover_img'] = addslashes($row['cover_img']);
            $sql = "insert into ".TABLEPREFIX."_game(resource_id,name,code,category_id,tags,instructions,description,cover_img,width,height,entry_time,hits,week_hits,month_hits,like_high,play_model,stick,featured,featured_time,published,is_adapt,outerlink,game_url,properties)
      values('".$row['resource_id']."','".$row['name']."','".$row['code']."',0,'".$row['tags']."','".$row['instructions']."','".$row['description']."','".$row['cover_img']."','".$row['width']."','".$row['height']."','".$row['entry_time']."','".$row['hits']."','".$row['week_hits']."','".$row['month_hits']."','".$row['like_high']."','".$row['play_model']."','".$row['stick']."','".$row['featured']."','".$row['featured_time']."','".$row['published']."','".$row['is_adapt']."','".$row['outerlink']."','".$row['game_url']."','".$row['properties']."')";
            mysql_query($sql);//执行sql,将满足条件的数据插入到数据表中
            $insertId = mysql_insert_id();//查找新加入的游戏id
            //echo $insertId;exit;
            if($rc=mysql_affected_rows()){
                echo $msg = "into ".$row['name']."games success!\n";
                $flag = true;
            }else{
                echo $msg = "The game ".$row['name']." import failed!\n";
            }

            //查找该游戏的tags
            //$sql_tags = 'select * from '.$this->tablePrefix.'_tags where id=(select tag_id from '.$this->tablePrefix.'_tag_relations where game_id='.$row['id'].')';
            $sql_tags = 'select * from '.$this->tablePrefix.'_tags t  left join '.$this->tablePrefix.'_tag_relations r on t.id=r.tag_id where r.game_id='.$row['id'];
            //echo $sql_tags;exit;
            $query_tags = mysql_query($sql_tags);

            while ($row_tags = mysql_fetch_assoc($query_tags)) {
                if($row_tags["cover_img"] == NULL){
                    $row_tags["cover_img"] = '';
                }
                $row_tags['tag_name'] = addslashes($row_tags['tag_name']);
                $sql_newid = "select id from ".TABLEPREFIX."_tags where tag_name='".$row_tags['tag_name']."'";
                $quert_newid = mysql_query($sql_newid);
                $row_tagids = mysql_fetch_assoc($quert_newid);
                if(!$row_tagids['id']){
                    $sql3 = "insert into ".TABLEPREFIX."_tags(tag_name,code,status,cover_img) values('".$row_tags['tag_name']."','".$row_tags['code']."','".$row_tags['status']."','".$row_tags['cover_img']."')";
                    mysql_query($sql3);
                    $insert_tagid = mysql_insert_id();
                    $sql2 = "insert into ".TABLEPREFIX."_tag_relations(game_id,tag_id) values('".$insertId."','".$insert_tagid."')";
                    mysql_query($sql2);
                }else{
                    echo $msg = "tag_name".$row_tags['tag_name']." is exist";
                }
                /*$sql3 = "insert into ".TABLEPREFIX."_tags(tag_name,code,status,cover_img) values('".$row_tags['tag_name']."','".$row_tags['code']."','".$row_tags['status']."','".$row_tags['cover_img']."')";
                mysql_query($sql3);
                //获取新加入的tagid
                //$row_tags['tag_name'] = addslashes($row_tags['tag_name']);
                $sql_newid = "select id from ".TABLEPREFIX."_tags where tag_name='".$row_tags['tag_name']."'";
                $quert_newid = mysql_query($sql_newid);
                if(!$quert_newid){
                    echo $sql_newid. "\n";
                    echo mysql_errno() . ": " . mysql_error(). "\n";
                }else{
                    $row_tagids = mysql_fetch_assoc($quert_newid);
                    $insert_tagid = $row_tagids['id'];

                    //$insert_tagid = mysql_insert_id();
                    $sql2 = "insert into ".TABLEPREFIX."_tag_relations(game_id,tag_id) values('".$insertId."','".$insert_tagid."')";

                    mysql_query($sql2);
                }*/

                if($rc=mysql_affected_rows()){
                    echo $msg = "Related tags successfully imported into the game\n";
                    $flag = true;
                }else{
                    echo $msg = "The game is not related tags\n";
                }
            }

            $arr=explode('?',$row['cover_img']);
            $image=$arr[0];

            //迁移图片至渠道目录
            $row['image'] = NEW_IMG_URL.$image;
            $pos = strrpos($row['cover_img'],'/');
            $new_dir = substr($row['cover_img'],0,($pos+1));
            if($row['image']) {
                $this->moveFile(OLD_IMG_URL.$image, $row['image'], NEW_IMG_URL.$new_dir);
            }

        }
        $this->log($msg);
        return $flag;
    }
    //移动封面，游戏文件
    function moveFile($oldGame, $newGame, $newGamePath) {
        $flag = false;
        if(file_exists($newGame)) {
            return true;
        }
        if(file_exists($oldGame)) {
            if(createDir($newGamePath)) {

                if(copy($oldGame, $newGame)) {
                    echo $msg = $oldGame." copy to ".$newGame." succesfully!"."\n";
                    $flag = true;
                } else {
                    echo $msg = $oldGame." copy to ".$newGame." failed!\n";
                }
            } else {
                echo $msg = "Create directory failed ".$newGamePath."\n";
            }
        } else {
            echo $msg = "Source image is not exist: ".$oldGame."\n";
        }
        $this->log($msg);
        return $flag;
    }



   /**
    * 记录转化日志
    */
   function log($msg) {
      $file = dirname(__FILE__).'/migrate.log';
      $fp = fopen($file, 'ab+');
      $msg = $msg."\r\n";
      fwrite($fp, $msg);
      fclose($fp);
   }
}

$m = new Migrate();
$m->game();
?>