2013年9月

编译php vld扩展

wget http://pecl.php.net/get/vld-0.12.0.tgz
tar zxf vld-0.12.0.tgz
phpize
./configure
make && make install

# vim /usr/local/php/etc/php.ini
# extension=vld.so

# 查看扩展是否已经加载
php -m | grep vld

# 试一试
php -dvld.active=1 test.php

Mysql ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in..的解决方法

昨天在数据库中查看论坛用户的活动时间和访问时间的间隔时,出现ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in..的错误。记录一下。

ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in..的解决方法:


mysql> select lastvisit, lastactivity from pre_common_member_status limit 1;
+------------+--------------+
| lastvisit  | lastactivity |
+------------+--------------+
| 1199200260 |   1198336989 |
+------------+--------------+
1 row in set (0.01 sec)

mysql> select lastvisit-lastactivity from pre_common_member_status limit 1;
+------------------------+
| lastvisit-lastactivity |
+------------------------+
|                 863271 |
+------------------------+
1 row in set (0.05 sec)

mysql> select abs(lastvisit-lastactivity) from pre_common_member_status limit 1;

+-----------------------------+
| abs(lastvisit-lastactivity) |
+-----------------------------+
|                      863271 |
+-----------------------------+
1 row in set (0.03 sec)

mysql> select lastactivity-lastvisit from pre_common_member_status limit 1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`discuz`.`pre_c
ommon_member_status`.`lastactivity` - `discuz`.`pre_common_member_status`.`las
tvisit`)'
mysql> select cast(lastactivity as signed)-cast(lastvisit as signed) from pre_co
mmon_member_status limit 1;
+--------------------------------------------------------+
| cast(lastactivity as signed)-cast(lastvisit as signed) |
+--------------------------------------------------------+
|                                                -863271 |
+--------------------------------------------------------+
1 row in set (0.02 sec)

mysql> select abs(cast(lastactivity as signed)-cast(lastvisit as signed)) from p
re_common_member_status limit 1;
+-------------------------------------------------------------+
| abs(cast(lastactivity as signed)-cast(lastvisit as signed)) |
+-------------------------------------------------------------+
|                                                      863271 |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

通过上面的查询我们可以发现,当两个时间戳相减为负数时才会出现ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in..这个错误,但在这个表中两个值中大小不是固定的,lastactivity有可能比lastvisit大,也有可能比lastvisit小。。所以这里可以用cast()来解决。

用select语句来代替show create table来显示表结构

用select语句来代替show create table来简单显示表结构,此语句不包含创建表索引语句。

select语句查询表结构:


SET @db_name='aa';
SET @table_name='bb';
SELECT TABLE_NAME,
CONCAT('CREATE TABLE ','`',TABLE_NAME,'` (',GROUP_CONCAT(
"\r\n",
CONCAT(CONCAT('`',COLUMN_NAME,'`'),
' ',
COLUMN_TYPE,
' ',
IF(IS_NULLABLE='NO','NOT NULL',''),
 IF(COLUMN_TYPE='text','',IF(COLUMN_DEFAULT IS NULL AND IS_NULLABLE='NO','',CONCAT(' DEFAULT ',IF(COLUMN_DEFAULT IS NULL,'NULL',CONCAT('\'',COLUMN_DEFAULT,'\'')))))),
IF(EXTRA='','',' auto_increment')
),
CONCAT(",\r\n",'PRIMARY KEY (`',(SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME=@table_name AND COLUMN_KEY='PRI'),'`)',"\r\n",')'),
(SELECT CONCAT(' ENGINE=',ENGINE,' DEFAULT CHARSET=',SUBSTRING(TABLE_COLLATION,1,LOCATE('_',TABLE_COLLATION)-1)) FROM information_schema.TABLES WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME=@table_name)
) AS TABLE_SCHEMA
FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME=@table_name;

话说这个改改貌似可以用来注入了。。

不过这个还是有局限性的,group_concat长度一般默认为1024,所以对于结构太复杂的表可能会发生截断,显示不完整。

MySQL server has gone away解决办法

插入大量数据出现 2006 Lost connection to MySQL server during query或MySQL server has gone away。

解决办法:将max_allowed_packet值调大。

Like this:


mysql> SHOW GLOBAL VARIABLES LIKE '%packet';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+
1 row in set (0.00 sec)

mysql> SET GLOBAL max_allowed_packet=10485760;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE '%packet';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 10485760 |
+--------------------+----------+
1 row in set (0.00 sec)
mysql>

That's all.

利用php的imagick给gif图片加水印

尝试多次直接用GD库给gif图片加水印均未达到理想效果后,决定用imagick扩展给gif图片加水印,虽然帧数太多的情况下速度不太给力外,效果蛮理想。

开启imagick扩展,可参考http://www.vpser.net/manage/lnmp-lnmpa-imagemagick-install-tutorial.html

果断先上图:

再上代码:



$image_name = '01351346.gif';
//$image_name = 'Left_spinning_dancer.gif';
//$image_name = 'gifmerge.gif';

function createTextImagickDraw($fontSize=12, $fillColor='', $underColor='', $font='msyh.ttf'){
	$draw = new ImagickDraw();
	$draw->setFont($font);
	$draw->setFontSize($fontSize);
	//$draw->setGravity(Imagick::GRAVITY_SOUTHEAST);//设置水印位置
	if(!empty($underColor)) $draw->setTextUnderColor(new ImagickPixel($underColor));
	if(!empty($fillColor)) $draw->setFillColor(new ImagickPixel($fillColor));
	return $draw;
}

function createWaterImagickDraw($waterImg='water.png',$x=10,$y=85,$width=16,$height=16){
	$water = new Imagick($waterImg);
	//$second->setImageOpacity (0.4);//设置透明度
	$draw = new ImagickDraw();
	//$draw->setGravity(Imagick::GRAVITY_CENTER);//设置位置
	$draw->composite($water->getImageCompose(), $x, $y, $width, $height,$water);

	return $draw;

}
$image = new Imagick($image_name);
$animation = new Imagick();
$animation->setFormat( "gif" );
$image = $image->coalesceImages();
$unitl = $image->getNumberImages();

for ($i=0; $i<$unitl; $i++) {
	$image->setImageIndex($i);
	$thisimage = new Imagick();
	$thisimage->readImageBlob($image);
	$delay = $thisimage->getImageDelay();
	$thisimage->annotateImage(createTextImagickDraw(12, 'red'), 30, 100, 0, '阿维卡');
	$thisimage->annotateImage(createTextImagickDraw(12, 'green'), 10, 120, 0, 'http://kller.cn');
	$thisimage->annotateImage(createTextImagickDraw(12, 'blue'), 10, 140, 0, 'http://www.aweika.com');
	$thisimage->drawImage(createWaterImagickDraw('f.jpg'));
	$animation->addImage($thisimage);
	$animation->setImageDelay( $delay );
}
$animation->writeImages('new/'.$image_name, true);
header( "Content-Type: image/gif" );
echo $animation->getImagesBlob();

附件:(你们懂的)

imagick.zip

mysql load data infile一例

对于load data infile的用法,官方的手册写的已经很全面了,不准备多说,仅写个实例。:)

表结构


CREATE TABLE `domain` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `extension` varchar(20) NOT NULL,
  `status` tinyint(1) unsigned NOT NULL,
  `create_time` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
)

data.txt

内容分别为域名和状态。


kller.cn,0
aweika.com,1
type.so,1
qiyuuu.com,1
xiaosong.org,1
inote.cc,2
xxx.com.cn,1

sql语句


LOAD DATA INFILE 'D:\data.txt'
INTO TABLE `domain`
FIELDS TERMINATED BY ','
(@domain,`status`)
SET `create_time`=UNIX_TIMESTAMP(),
`name`=SUBSTRING(@domain,1,LOCATE('.', @domain)-1),
`extension`=SUBSTRING(@domain,LOCATE('.', @domain)+1);

执行结果

php对特定数组进行压缩

特定的数组形式如下:


$dataArr = array(
'0'=>array('c'=>'A','f'=>55,'t'=>60),
'1'=>array('c'=>'A','f'=>61,'t'=>70),
'2'=>array('c'=>'A','f'=>71,'t'=>80),
'3'=>array('c'=>'A','f'=>81,'t'=>90),
'4'=>array('c'=>'B','f'=>91,'t'=>100),
'5'=>array('c'=>'B','f'=>101,'t'=>110),
'6'=>array('c'=>'A','f'=>111,'t'=>120),
'7'=>array('c'=>'B','f'=>121,'t'=>130),
'8'=>array('c'=>'B','f'=>131,'t'=>140),
'9'=>array('c'=>'B','f'=>141,'t'=>150)
);

压缩方法:


function change($dataArr)
{
	$j=0;
	for ($i=0;$i<=count($dataArr);$i++)
	{
		if($dataArr[$i]['t']+1 == $dataArr[$i+1]['f'] && $dataArr[$i]['c'] == $dataArr[$i+1]['c'] && $i!=count($dataArr)-1)
		{
			$dataArr[$i]['t'] = $dataArr[$i+1]['t'];
			unset($dataArr[$i+1]);
			$j++;
		}
	}
	if($j != 0) $dataArr = change(array_values($dataArr));
	return $dataArr;
}

处理代码:


$dataArr = change($dataArr);
var_dump($dataArr);

执行结果:


array
  0 => 
    array
      'c' => string 'A' (length=1)
      'f' => int 55
      't' => int 90
  1 => 
    array
      'c' => string 'B' (length=1)
      'f' => int 91
      't' => int 110
  2 => 
    array
      'c' => string 'A' (length=1)
      'f' => int 111
      't' => int 120
  3 => 
    array
      'c' => string 'B' (length=1)
      'f' => int 121
      't' => int 150

应用

这段代码印象比较深刻,当时我从那个纯真ip中导出ip数据库,做了n多步处理后只保留了城市,于是就变成了下面这个样子:

处理前

仔细看就会发现有好多同城市的ip上一条结束和下一条开始其实是连续的,也就是说完全可以组成一条数据。处理方法上面已给出。成功的从原表285739条数据处理成29251条。处理后的数据如下图:

处理后

好吧,就写到这。

php大数(浮点数)取余

一般我们进行取余运算第一个想到的就是用百分号%,但当除数是个很大的数值,超出了int范围时,这样取余就不准确了。

php大数(浮点数)取余函数


/**
 * php大数取余
 *
 * @param int or float $bn 除数
 * @param int $sn 被除数
 * @return int 余数
 */

//大数(浮点数)取余方法
function Kmod($bn, $sn)
{
	return intval(fmod(floatval($bn), $sn));
}

测试代码:


//大数(浮点数)取余方法
function Kmod($bn, $sn)
{
	return intval(fmod(floatval($bn), $sn));
}

//整数取余方法
function mod($bn, $sn)
{
	return $bn%$sn;
}

//最大的int整数
$bn = PHP_INT_MAX;
$sn = 11;

var_dump($bn);
var_dump(Kmod($bn, $sn));
var_dump(mod($bn, $sn));

//给最大的int整数加1
$bn = PHP_INT_MAX + 1;
var_dump($bn);
var_dump(Kmod($bn, $sn));
var_dump(mod($bn, $sn));

执行结果:


int 2147483647

int 1

int 1

float 2147483648

int 2

int -2

我们可以发现当给最大的整型数加1之后,得到的$bn已经变为了浮点型。

php自用分页类

很早以前写的了,现整理共享出来。:)

相关代码在下面,先发下效果图:

下拉列表分页PMA和NORMAL两种模式差别效果图:

PMA模式

NORMAL模式

分页类Page.class.php


/**
* 本类主要用于实现简单分页
* Page.class.php
*/

class Page
{
	private $page_num; //每页显示的信息条数
	private $page_all_no; //信息的总条数
	private $page_len; //显示多少个页码
	private $page; //当前的页数
	private $page_max; //页数最大值
	private $page_no_array; //页数的数组
	public $start_num; //查询语句limit的起始值
	private $page_change; //在第几个页码开始 页码递增
	private $URL; //获取当前页面的URL

	public function __construct($page_all_no, $page_num=5, $page_len=5)
	{
		$this->page_all_no       = intval($page_all_no);
		$this->page_num          = intval($page_num);
		$this->page_len          = intval($page_len);
		$this->URL               = $_SERVER['REQUEST_URI'];
		$this->max_page(); //得到页数的最大值
		$this->page_no(); //得到当前页数
		$this->page_no_array(); //页数数组
		$this->start_num(); //得到sql语句中limit的起始值
		$this->change_page(); //得到递增开始的页码$this->page_change
		$this->getURL(); //得到当前的URL并处理返回
	}

	private function isArray($str,$str_self)
	{
		if(!is_array($str)) throw new Exception("$str_self must be an Array type");
	}

	private function page_no()
	{
		$this->page = isset($_GET['page']) ? $_GET['page'] : 1;
		if(isset($_GET['page']) && $_GET['page'] < 1) $this->page = 1;
		if(isset($_GET['page']) && $_GET['page'] > $this->page_max) $this->page = $this->page_max;
		return $this->page;
	}

	private function max_page()
	{
		return $this->page_max = $this->page_all_no <= 0 ? 1 : ceil($this->page_all_no/$this->page_num);
	}

	private function change_page()
	{
		return $this->page_change = ceil($this->page_len / 2);
	}

	private function page_no_array()
	{
		return $this->page_no_array = range(1, $this->page_max);
	}

	private function start_num()
	{
		return $this->start_num = $this->page_num * ($this->page - 1);
	}

	private function getURL()
	{
		if(!empty($_SERVER['argc']) ? $_SERVER['argc'] == 0 : strpos($_SERVER['REQUEST_URI'], '?') === false)
		{
			$this->URL = $this->URL.'?';
		}else{
			$url_a = "/\?page=[0-9]{1,}/";
			$url_b = "/&page=[0-9]{1,}/";
			ereg("\?page=[0-9]{1,}", $this->URL) ? $this->URL = preg_replace($url_a, "?", $this->URL):
			ereg("&page=[0-9]{1,}", $this->URL) ? $this->URL = preg_replace($url_b, "&", $this->URL):$this->URL = $this->URL.'&';
		}
		return $this->URL;
	}

	private function header_info($total_data_modifier='总数:', $total_page_modifier=' 总页数:', $current_page_modifier=' 当前页:', $header_info_modifier='', $header_data_color = 'red'){
		if($this->page_max != 1 && $this->page_all_no != 0)
		{
			$header_info = $total_data_modifier.''.$this->page_all_no.'';
			$header_info .= $total_page_modifier.''.$this->page_max.'';
			$header_info .= $current_page_modifier.''.$this->page.' '."\r\n";
			if(!empty($header_info_modifier)) $header_info = $header_info_modifier.$header_info;
			return $header_info;
		}else{
			return NULL;
		}
	}

	private function first($first_format = '第一页')
	{
		return $this->page == 1 ? $first_format."\r\n" : ''.$first_format.''."\r\n";
	}

	private function last($last_format = '上一页')
	{
		if($this->page == 1) return $last_format."\r\n";
		return $this->page-1 == 1 ? ''.$last_format.''."\r\n" : ''.$last_format.''."\r\n";
	}

	private function page_num_format($separator=' ', $left_modifier='[', $right_modifier=']', $both_sides=false, $current_page_color = 'red')
	{
		empty($separator) ? $separator = ' ' : $separator;
		$page_array = '';
		if($this->page_max <= $this->page_len)
		{
			for ($i=0; $i < $this->page_max; $i++)
			{
				if($this->page_no_array[$i] == $this->page)
				{
					$the[$i] = ''.$left_modifier.$this->page_no_array[$i].$right_modifier.''."\r\n";
				}else{
					if($i == 0)
					{
						$page_one = substr($this->URL,0,strlen($this->URL)-1);
						$the[$i] = ''.$left_modifier.$this->page_no_array[$i].$right_modifier.''."\r\n";
					}else{
						$the[$i] = ''.$left_modifier.$this->page_no_array[$i].$right_modifier.''."\r\n";
					}
				}
				$page_array .= $the[$i].$separator;
			}
			if($both_sides === false)
			{
				$page_array = substr($page_array,0,strrpos($page_array, $separator));
			}elseif ($both_sides === true){
				$page_array = $separator.$page_array;
			}else{
				throw new Exception('ERROR: $both_sides must be a boolean type.');
			}
		}else{
			if($this->page <= $this->page_change)
			{
				$i_start = 0;
			}else{
				$i_start = $this->page - $this->page_change;
				//如果最大的页码已显示,那么开始页就不会在递增
				if($i_start >= $this->page_max - $this->page_len){
					$i_start = $this->page_max - $this->page_len;
				}
			}
			$i_end = ($i_start+$this->page_len) - 1;
			for ($i = $i_start; $i <= $i_end; $i++)
			{
				if($this->page_no_array[$i] == $this->page)
				{
					$the[$i] = ''.$left_modifier.$this->page_no_array[$i].$right_modifier.''."\r\n";
				}else{
					if($i == 0)
					{
						$page_one = substr($this->URL,0,strlen($this->URL)-1);
						$the[$i] = ''.$left_modifier.$this->page_no_array[$i].$right_modifier.''."\r\n";
					}else{
						$the[$i] = ''.$left_modifier.$this->page_no_array[$i].$right_modifier.''."\r\n";
					}
				}
				$page_array .= $the[$i].$separator;
			}
			if($both_sides === false)
			{
				$page_array = substr($page_array,0,strrpos($page_array, $separator));
			}elseif ($both_sides === true){
				$page_array = $separator.$page_array;
			}else{
				throw new Exception('ERROR: $both_sides must be a boolean type.');
			}
		}
		return $page_array;
	}

	private function next($next_format = '下一页')
	{
		if($this->page >= $this->page_max) return $next_format."\r\n";
		return ''.$next_format.''."\r\n";
	}

	private function end($end_format = '最后一页')
	{
		return $this->page >= $this->page_max ? $end_format."\r\n" : ''.$end_format.''."\r\n";
	}

	public function select_page($target='self', $select_page_mode='PMA', $value_left_modifier='', $value_right_modifier='')
	{
		if($this->page_max != 1 && $this->page_all_no != 0)
		{
			if($select_page_mode === 'NORMAL')
			{
				$page_no_array = $this->page_no_array;
			}elseif($select_page_mode === 'PMA'){
				$page_no_array = $this->PMA_page_no_array();
			}else{
				throw new Exception('$select_page_mode is unknown mode');
			}
			$select_page = '';
			$select_page .= ''. "\n";
			return $select_page;
		}else{
			return NULL;
		}
	}

	private function PMA_page_no_array()
	{
		$showAll = 200;
		$sliceStart = 5;
		$sliceEnd = 5;
		$percent = 20;
		$range = 10;

		if ($this->page_max < $showAll)	{
			$this->PMA_page_no_array = range(1, $this->page_max);
		} else {
			$this->PMA_page_no_array = array();
			for ($i = 1; $i <= $sliceStart; $i++) {
				$this->PMA_page_no_array[] = $i;
			}
			for ($i = $this->page_max - $sliceEnd; $i <= $this->page_max; $i++) {
				$this->PMA_page_no_array[] = $i;
			}
			$i = $sliceStart;
			$x = $this->page_max - $sliceEnd;
			$met_boundary = false;
			while ($i <= $x) {
				if ($i >= ($this->page - $range) && $i <= ($this->page + $range)) {
					$i++;
					$met_boundary = true;
				} else {
					$i = $i + floor($this->page_max / $percent);
					if ($i > ($this->page - $range) && !$met_boundary) {
						$i = $this->page - $range;
					}
				}
				if ($i > 0 && $i <= $x) {
					$this->PMA_page_no_array[] = $i;
				}
			}
			sort($this->PMA_page_no_array);
			$this->PMA_page_no_array = array_unique($this->PMA_page_no_array);
		}
		return $this->PMA_page_no_array;
	}

	public function key_change_page(){
		echo '';
	}

	public function eshow()
	{
		return $this->last().$this->next();
	}

	public function showForHelp()
	{
		return $this->first('首页', '').$this->last('上一页', '').$this->page_num_format('', '', '', false, '#FF8500').$this->next('下一页', '').$this->end('尾页', '');
	}

	public function show()
	{
		return $this->header_info().$this->first('首页', '').$this->last('上一页', '').$this->page_num_format('', '', '', false, '#FF8500').$this->next('下一页', '').$this->end('尾页', '');
	}
}

使用方法:


header('content-type:text/html;charset=utf-8');
include('Page.class.php');
$page_all_no = 12000; //数据的总条数
$page_num = 25; //设置每页显示的条数
$page_len = 7; //最多显示的页码数
$page = new Page($page_all_no, $page_num, $page_len);
$start_num = $page->start_num;
$sql = "select * from table limit {$start_num}, {$page_num}";
$page->key_change_page(); //方向键翻页
var_dump($sql);
//自带三种分页形式,可再按需要添加新方法,像下面那样调用
echo '

'.$page->select_page('self', 'NORMAL').'

'; //这里的第二个参数默认为PMA模式,两种模式差别在上面已给出 echo '

'.$page->eshow().'

'; echo '

'.$page->show().'

';

树莓派 开关控制LED

接线

Untitled Sketch_bb.jpg

代码实现

通过读取GPIO PIN的值,在按钮按下并松开之后更改LED的状态;从而进行灯的亮灭的切换。

#!/usr/local/env python
# encoding: utf-8

import RPi.GPIO as GPIO
import time

GPIO.setmode(GPIO.BCM)
GPIO.setup(18, GPIO.OUT)
GPIO.setup(17, GPIO.IN)

light = False

while 1:
	i = GPIO.input(17)
	if i:
		print('检测到按钮按下')
		while 1:
			i = GPIO.input(17)
			if not i:
				print('检测到按钮松开')
				GPIO.output(18, light)
				light = not light
				break
	time.sleep(.2)

树莓派 LED呼吸灯

预览

2013-09-03 22_00_48.gif

接线

+接个3.3v的电源,-接18口Untitled Sketch_bb.jpg

代码实现

#!/usr/bin/env python
# encoding: utf-8

import RPi.GPIO
import time
# 正常呼吸为3s一次,深呼吸6s一次
'''
RPi.GPIO.setmode(RPi.GPIO.BOARD)
RPi.GPIO.setup(12, RPi.GPIO.OUT)

pwm = RPi.GPIO.PWM(12, 50)
pwm.start(0)
'''
RPi.GPIO.setmode(RPi.GPIO.BCM)
RPi.GPIO.setup(18, RPi.GPIO.OUT)

pwm = RPi.GPIO.PWM(18, 50)
pwm.start(0)
try:
	while True:
		for i in xrange(0, 101, 2):
			pwm.ChangeDutyCycle(i)
			time.sleep(.03)
		for i in xrange(100, -1, -2):
			pwm.ChangeDutyCycle(i)
			time.sleep(.03)
except KeyboardInterrupt:
	pass

pwm.stop()

RPi.GPIO.cleanup()