php mysql 查询 时间段_php查询时间段 mysql存储unix时间

数据表的结构如下

CREATE TABLE IF NOT EXISTS `user_log` (

`id` varchar(100) NOT NULL default '0',

`username` varchar(30) NOT NULL,

`time` bigint(13) NOT NULL default '0',

`result` tinyint(1) NOT NULL default '0',

`money` bigint(40) NOT NULL default '0',

PRIMARY KEY  (`id`,`username`,`time`),

KEY `username` (`id`),

KEY `username_2` (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--

-- Dumping data for table `user_log`

--

INSERT INTO `user_log` (`id`, `username`, `time`, `result`, `money`) VALUES

('admin', 'cd772c84df8c443598d4b6ccef8593', 1237132333079, 0, 10),

('fandamo', '18df01f70a544e1c9a7ca3068be7ba', 1237258006688, 1, 23);

其中time为unix时间

我们要实现通过php页面输入一个这样格式2008-03-15 23:52:13-2009-03-15 23:52:13的时间段查询。

将查询页面的本地时间2009-03-15 23:52:13转换为unix时间1237132333

时间time2*1000作为time2，进行select * from table where ptime >=time1

adn ptime

<=time2;并将数据库内取出的数据进行unix时间转本地时间。由于数据库内时间以毫秒为单位，unix时间与本地时间以秒为单位，需要查询

时间取出时间/1000再进行时间转换,显示到页面。

核心代码

//将我们的search.php传递过来的参数进行赋值

$name=$_POST["name"];

$pTimeb=$_POST["pTimeb"];

$pTimes=$_POST["pTimes"];

$mNumberb=$_POST["mNumberb"];

$mNumbers=$_POST["mNumbers"];

//本地时间转换unix时间并简单增加到毫秒

$utcpTimeb = strtopTime( $pTimeb )*1000;

$utcpTimes = strtopTime( $pTimes )*1000;

//echo $pTimeb;

//echo $utcpTimeb;

//echo "

";

//echo $pTimes;

//echo $utcpTimes;

//

//echo $utcpTime;

//参数过滤并生成SQL------------------------

if($name != null){

$a = " and name like '%$name%'";}

if($pTimeb != null){

$b = " and pTime >= '$utcpTimeb'";}

if($pTimes != null){

$c = " and pTime <= '$utcpTimes'";}

if($mNumberb != null){

$d = " and mNumber >= '$mNumberb'";}

if($mNumberb != null){

$e = " and mNumber <= '$mNumbers'";}

$q = "SELECT * FROM user_log where (1=1)";

$q .=$a;

$q .=$b;

$q .=$c;

$q .=$d;

$q .=$e;

echo "

生成sql语句";

echo $q;

echo "

";

echo "

";

$result = mysql_query($q, $link);

echo "

结果反馈";

//$result = mysql_query("SELECT * FROM pay");

//对我们的查询结果中返回的时间进行秒换算，并转成本地时间

while ($row = mysql_fetch_array($result, MYSQL_NUM)) {

$unixpTime=$row[7]/1000;

echo "";

floor($unixpTime);

$aa= floor($unixpTime);

echo $aa;

//$aa='0123456789';

$query1 = mysql_query("SELECT FROM_UNIXpTime($aa)");

while ($row1 = mysql_fetch_array($query1, MYSQL_NUM))

{

//  printf (" pTime %s ", $row1[0]);

$starpTime=$row1[0] ;

}

echo "

";

//打印输出结果

printf ("玩家姓名: %s  充值卡号: %s 充值时间: $starpTime 是否生效: %s 充值金额: %s  ", $row[0], $row[1], $row[3] , $row[4], $row[5]);

printf ("充值卡号: %s  玩家姓名: %s 充值金额: %s 充值时间: %s ", $row[0], $row[1],  $row[5], $row[7]);

echo "

";

}

mysql_free_result($result);

?>

阅读(1812) | 评论(0) | 转发(0) |