向东博客 专注WEB应用 构架之美 --- 构架之美，在于尽态极妍 | 应用之美，在于药到病除|赢在IT，Playin' with IT,Focus on Killer Application,Marketing Meets Technology.

<?php
/**
* 倒库程序雏形，将space的数据分析后导入到现有数据库中
*
*/
class import_export
{
  private  $dsn="";
  private  $user="";
  private  $password="";
  private  $pdo="";
  
  public function import_export($dsn,$user,$password)
  {
    $this->dsn = $dsn;
    $this->user = $user;
    $this->password = $password;
    $this->pdo = new PDO($this->dsn, $this->user, $this->password);
  }
  
  public function fetch_result($query)
  {
    $rs = $this->pdo->query($query);
    $result_arr = $rs->fetchAll();
    return $result_arr;
    
    
  }
  
  public function insert($insert_sql)
  {
    $count = $this->pdo->exec($insert_sql);
    if($count)
    {
      return true;
    }else{
      return false;      
    }
  }
  
  public function get_pdo_obj()
  {
    return $this->db;
  }
  
  public function lastInsertId()
  {
    return $this->pdo->lastInsertId();
    
  }
}
?>
<?php
$time_begin = time();
$conf_array = array("db_host"=>"localhost","port"=>"3306","dbname"=>"public","user"=>"root","password"=>"");
$dsn = "mysql:host={$conf_array['db_host']};port={$conf_array['port']};dbname={$conf_array['dbname']}";
$user = $conf_array['user'];
$password = $conf_array['password'];
$pdo = new import_export($dsn,$user,$password);
for($i=1;$i<100000000000;$i++)
{
  $time_end = time();
  if($time_end - $time_begin >= 60)
  {
    exit;
  }
  $insert_sql = "INSERT INTO `information` ( `infoid` , `group` , `productid` , `connection` , `invitetype` , `content` , `addition` ) VALUES (NULL , '1', '2', '2', '1', '的的的的的的的的的的的的的的的的的的', '的的的的的的的的的的的的的的的的的的的的的的的的');"; //插入数据库的sql语句
  $import_export2 = $pdo->insert($insert_sql);
  $insert_id = $pdo->lastInsertId();
  $insert_sql2 = "INSERT INTO `relation` ( `id` , `owneruid` , `otheruid` , `type` , `status` , `infoid` , `ctime` , `uniq_key` ) VALUES (NULL , '1', '2', '1', '11', $insert_id, '2008-10-02 20:30:21', '45646546456456456456456456456456465');"; //插入数据库的sql语句
  $import_export2 = $pdo->insert($insert_sql2);
  
}
?>

mysql>show procedure status where Db='test'

mysql>create procedure hi() select 'hello';

mysql>call hi();

　mysql> call hi();
+-------+
| hello |
+-------+
| hello |
+-------+
1 row in set (0.00 sec)
　　Query OK, 0 rows affected (0.01 sec)

DROP PROCEDURE hi;

DELIMITER ||
CREATE PROCEDURE showdb( v_offset int, v_pagesize int )  
BEGIN
  SET @stmt = concat("SELECT * FROM  `comment` ORDER BY id DESC LIMIT ",v_offset,",",v_pagesize);
  PREPARE s1 FROM @stmt;
  EXECUTE s1;
  DEALLOCATE PREPARE s1;
END||
DELIMITER ;

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`proc_test` $$
CREATE PROCEDURE `test`.`proc_test` (out a int)
BEGIN
    select count(*) into a from tblname;
END $$

DELIMITER ;

$db->query("CALL test.proc_test(@a)");
$res = $db->query("select @a");
$row = $res->fetch_array();
echo $row['@a'];

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`proc_test` $$
CREATE PROCEDURE `test`.`proc_test` ()
BEGIN
    select * from tbl_name;
END $$

DELIMITER ;

$res=$db->query("CALL test.proc_test()");
while ($arr=$res->fetch_array())
{
    echo $arr["Field"] ."<br/>";
}

1 define('CLIENT_MULTI_RESULTS', 131072);
2
3 $link = mysql_connect("127.0.0.1", "root", "",1,CLIENT_MULTI_RESULTS) or die("Could not connect: ".mysql_error());

1 <?php
2     define('CLIENT_MULTI_RESULTS', 131072);
3
4     $link = mysql_connect("127.0.0.1", "root", "",1,CLIENT_MULTI_RESULTS) or die("Could not connect: ".mysql_error());
5     mysql_select_db("vs") or die("Could not select database");
6 ?>
7
8 <?php
9         $result = mysql_query("call get_news_from_class_id(2)") or die("Query failed:" .mysql_error());
10         while($row = mysql_fetch_array($result, MYSQL_ASSOC))
11         {
12                 $line = '<tr><td><a target = _blank href=\''.$row["url"].'\'>'.$row["title"].'('.$row["page_time"].')'.'</a></td></tr>';
14                 echo $line;
15                 printf("\n");
16
17         }
18         mysql_free_result($result);
19 ?>
20
21 <?php
22     mysql_close($link);
23 ?>

   1. mysql> INSERT INTO t VALUES (NULL, 'aaaa'), (NULL, 'bbbb'), (NULL, 'cccc');  
   2. mysql> SELECT * FROM t;  
   3. +----+------+  
   4. | id | name |  
   5. +----+------+  
   6. |  1 | Bob  |  
   7. |  2 | aaaa |  
   8. |  3 | bbbb |  
   9. |  4 | cccc |  
  10. +----+------+  
  11. mysql> SELECT LAST_INSERT_ID();  
  12. +------------------+  
  13. | LAST_INSERT_ID() |  
  14. +------------------+  
  15. |                2 |  
  16. +------------------+  

alter table `relation` drop index gots;   alter table `relation` drop index gots;  
alter table `relation` add index gots(`group`,`owneruid`, `type`,`status`);

show index from  p2p_main;  //p2p_main 是表名称


mysql> show index from  p2p_main;      
+----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| p2p_main |          0 | PRIMARY     |            1 | id          | A         |     3052412 |     NULL | NULL   |      | BTREE      |         |
| p2p_main |          1 | oweneruid   |            1 | oweneruid   | A         |      610482 |     NULL | NULL   |      | BTREE      |         |
| p2p_main |          1 | oweneruid_2 |            1 | oweneruid   | A         |      610482 |     NULL | NULL   |      | BTREE      |         |
| p2p_main |          1 | oweneruid_2 |            2 | otheruid    | A         |     1017470 |     NULL | NULL   |      | BTREE      |         |
+----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

alter table `p2p_main` add index oweneruid_2(oweneruid ,otheruid );//建立索引,括号里面不能有逗号和单引号
alter table `p2p_main` drop index oweneruid_2; //删除索引：是建立在oweneruid 和otheruid 的联合索引

explain SELECT SQL_NO_CACHE * FROM `mytest` force index(rstatus,uid) WHERE  rstatus =1 AND uid IN ( 3,2,7) \G;

-- 表的结构 `p2p_info`
--

CREATE TABLE IF NOT EXISTS `p2p_info` (
  `infoid` int(10) unsigned NOT NULL auto_increment,
  `group` enum('1','2') collate utf8_bin NOT NULL,
  `productid` char(32) collate utf8_bin NOT NULL,
  `connection` enum('11','22') collate utf8_bin NOT NULL,
  `invitetype` enum('111','222') collate utf8_bin NOT NULL,
  `content` text collate utf8_bin NOT NULL,
  `addition` text collate utf8_bin NOT NULL,
  PRIMARY KEY  (`infoid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- 表的结构 `p2p_main`
--

CREATE TABLE IF NOT EXISTS `p2p_main` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `oweneruid` int(11) NOT NULL,
  `otheruid` int(11) NOT NULL,
  `type` enum('1','2') NOT NULL default '1',
  `status` enum('11','22') NOT NULL default '11',
  `infoid` int(11) NOT NULL,
  `ctime` datetime NOT NULL,
  `uniq_key` varchar(64) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

alter table `index_analyse_xiangdong` ADD UNIQUE key id4(id);
alter table index_analyse_xiangdong drop index id4;

可以用sql强制使用或不使用索引
IGNORE INDEX (invitetype)
USE INDEX (invitetype)
如
select count  from p2p_info1 IGNORE INDEX (invitetype) where invitetype="111";

CREATE TABLE customer (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;

show engines;

Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 5 to server version: 3.23.50-log

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> CREATE TABLE CUSTOMER (A INT, B CHAR (20), INDEX (A)) TYPE = InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO CUSTOMER VALUES (10, 'Heikki');
Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO CUSTOMER VALUES (15, 'John');
Query OK, 1 row affected (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM CUSTOMER;
+------+--------+
| A      | B        |
+------+--------+
| 10     | Heikki |
+------+--------+
1 row in set (0.00 sec)

1. 下面语句输出什么？为什么？
if ($a=$b)  printf("a==b");
else  printf("a!=b")；
2. $array['anykey']和$array[anykey]有什么区别？
3. 写出使用telnet命令通过GET的方式访问http://www.toplee.com/index.php 得到页面返回的方法（不是用PHP实现，用dos或者unix命令行实现）。

我来做个标准：

1. 禁用COOKIE 后 SEESION 还能用吗?
能用,页面get mode提交session.
2. 抓取远程图片到本地,你会用什么函数?
我会用curl
3. 已知姓名A,姓名B,给一个求他们缘份的算法
ascii （最好简单为好）
4. 你觉得在PV10W的时候, 同等配置下,LUNIX 比WIN快多少?
百分之二十以上
5. 简述POST 和GET传输的最大容量分别是多少?
无限 和 1024
6. 用最少的代码写一个求3值最大值的函数.
max()

$date = date('Y-m-d',strtotime("-1 day"));//昨天
$date = date('Ymd',strtotime("-1 day"));

$fromDate1=mktime(9,21,0,10,23,2009);//开始时间 --->  时，分，秒 月，日，年
         echo date("M-d-Y H:i:s",$fromDate1);

$date_d=date("d")-1;//天数减一天，数字想减少罢了。
$starttime = date("Y-m-d 00:00:00", strtotime("-3 day"));//天数减少3天，蕴含日期相见月份相减少。  date("Y-m-d H:i:s")

$lastread = time()-3*24*60*60;//减少是秒数，看下文sql构造：）
echo date("Y-m-d H:i:s",$lastread);

$date_time = time();
$date_time2 = time();
$date_time2-$date_time >= 60  //60秒

<?php
require_once ("/data0/vshare/app/rank/config.php");
define("USER_SEARCH_IASK_LOG_PATH", "/data0/vshare/logs/user_search/iasks/");//删除的用户及视频日志目录（爱问）
echo "程序于 " . date("Y-m-d H:i:s") . " 开始执行：\n";
$date_d=date("d")-1;
if($date_d<10)
{
  $date_d="0".$date_d;
}
$iask_filename = USER_SEARCH_IASK_LOG_PATH.date("Ym")."/".$date_d."/boke_ay_" . date("Ymd", strtotime("-1 day")) .".log";
$iask_filename_html = USER_SEARCH_IASK_LOG_PATH.date("Ym")."/".$date_d."/boke_ay_" . date("Ymd", strtotime("-1 day")) .".html";
$conn_db_3306_r = @mysql_connect(DBHOST_VIDEO_R_aoyun, DBUSER_VIDEO_R_aoyun, DBPASSWD_VIDEO_R_aoyun) or
    die(DBHOST_VIDEO_R_aoyun.DBUSER_VIDEO_R_aoyun.DBPASSWD_VIDEO_R_aoyun."Can't connect to 3306 port readonly database");//长连接
//统计昨天登录过的用户个人中心信息
echo "正在从MySQL数据库中取出所有的用户userid，请稍候....";
//exit(0);
//$select_dt_user_r = @mysql_select_db("dt_video", $conn_db_3306_r) or die("Can't select database");
$select_dt_user_r = @mysql_select_db("dt_manage", $conn_db_3306_r) or die("Can't select database");
//$sql_tb_usertotal_r = "SELECT videoid,up_userid FROM tb_video_total where activeid = 0 limit 10";/*上线后打开*/
$starttime = date("Y-m-d 00:00:00", strtotime("-3 day"));
$endtime = date("Y-m-d 00:00:00");
$sql_tb_usertotal_r = "select videoid,up_userid from tb_video_activity where (adddate>'".$starttime."' or appr_time>'".$starttime."') and adddate<'".$endtime."' and appr_status=1 and active_status in(1,0) and conv_status=1 and del_status=0 and activeid=128;";/*上线后打开*/
$query_tb_usertotal_r = mysql_query($sql_tb_usertotal_r, $conn_db_3306_r) or die("Query Error:" .
    mysql_error() . "\n");
echo  $sql_tb_usertotal_r."\n";
echo "\n[取出所有的用户userid:完成]\n";
$i=0;
echo $iask_filename."\n";

if(file_exists($iask_filename))
{
    unlink($iask_filename);
}

while ($row_tb_usertotal_r = mysql_fetch_assoc($query_tb_usertotal_r))
{
     if (!($i % 2000))
    {
      sleep(1);        
    }
  $vid = $row_tb_usertotal_r['videoid'];
  $uid = $row_tb_usertotal_r['up_userid'];
  $result['vid'] = $vid;
  $result['uid'] = $uid;
  /*  
  echo "VID:". $result['vid'] = $vid."\n";
  echo "UID:".$result['uid'] = $uid."\n";
  */
  $content = getContent($result,$i);
  us_writeFile($content, $iask_filename, USER_SEARCH_IASK_LOG_PATH .date("Ym")."/".$date_d);
  $i=$i+1;
  
    
}
if (file_exists($iask_filename) && is_readable($iask_filename))
{
  echo "正在向爱问推送数据，请稍候....\n";
  //pclose(popen("touch {$iask_filename} && chmod 0664 {$iask_filename} && /usr/bin/rsync -vzrtup {$iask_filename} 10.69.2.48::usersource_video/",
        //"r"));
  $command="/usr/bin/rsync -avgt --progress --password-file=/usr/local/etc/rsyncd.secrets " . USER_SEARCH_IASK_LOG_PATH ."*"." backupuser@219.142.118.48::boke_2008_sum_olympic_video";
  echo $command."\n";
  pclose(popen("touch {$iask_filename} && chmod 0664 {$iask_filename} && $command",
        "r"));
  
  $command="/usr/bin/rsync -avgt --progress --password-file=/usr/local/etc/rsyncd.secrets " . USER_SEARCH_IASK_LOG_PATH ."*"." backupuser@172.16.153.70::olympic_video/";
  pclose(popen("touch {$iask_filename} && chmod 0664 {$iask_filename} && $command",
        "r"));
    $command="/usr/bin/rsync -avu " . $iask_filename ." 172.16.6.198::vblog/";
    pclose(popen("touch {$iask_filename} && chmod 0664 {$iask_filename} && $command",
        "r"));
    $command="cp {$iask_filename} {$iask_filename_html}";
    pclose(popen($command, "r"));
    $command="/usr/bin/rsync -avu {$iask_filename_html} {$iask_filename} 10.68.1.30::v_blog_sina_com_cn/htdocs/ay_data/";
    pclose(popen($command, "r"));
    $command="/usr/bin/rsync -avu {$iask_filename_html} {$iask_filename} 10.68.1.71::v_blog_sina_com_cn/htdocs/ay_data/";
    pclose(popen($command, "r"));
  
  echo "[推送完成]\n";
}



echo "\n[完成]\n";


echo "程序于 " . date("Y-m-d H:i:s") . " 执行结束。\n\n";

function us_path_validate($path)
{
    $arraypath = split("\/+", $path);
    $tmppath = "";
    for ($i = 0; $i < count($arraypath); $i++)
    {
        if ("" == $arraypath[$i])
            continue;
        $tmppath .= (0 == $i ? "":"/") . $arraypath[$i];
        if (!is_dir($tmppath))
        {
            if (!mkdir($tmppath, 0777))
            {
                return false;
            }
        }
    }
    return true;
}

function us_writeFile($content, $filename, $dirpath)
{
    if (!us_path_validate($dirpath))
        exit();
    $fp = fopen($filename, "a+");
    if (!@fwrite($fp, $content . "\n"))
    {
        @fclose($fp);
        return false;
    }
    fclose($fp);
    return true;
}

function getContent($result,$i)
{
  /*
         if($i==0)
  {
    $str  = "VID\tUID\t日期\n";
          $str .= $result['vid'] . "\t";//视频总量
          $str .= $result['uid'] . "\t";//userid,用户UID
          $str .= date("Y-m-d",strtotime("-1 day")). "";
    return $str;
  }else{
  */
    $str  = $result['vid'] . "\t";//视频总量
          $str .= $result['uid'] . "\t";//userid,用户UID
          $str .= date("Y-m-d",strtotime("-1 day"))   . "";
    return $str;
   //    }
}
?>

function GetUnreadNum($uid)
{
  $cache = $this->_connectCache();
  $num = $cache->get(CACHE_SYSLOG."_new_".$uid);
  if($num===false)
  {
    $lastread = $cache->get(CACHE_SYSLOG."_read_".$uid);
    if($lastread===false) $lastread = time()-3*24*60*60;

    $db = $this->_connectDb(0);
    $sql = "select count(*) from syslog where uid='$uid' and time>FROM_UNIXTIME($lastread)";
    $st = $db->query($sql);
    $num = $st->fetchColumn()+0;
    $cache->set(CACHE_SYSLOG."_new_".$uid,$num);
  }
return $num;
}