微擎 site.php 数据,微擎的一些数据库用法

数据库操作

1、数据基本操作

tablename()

$sql = "SELECT * FROM ".tablename('users');

echo $sql;

//输出 SELECT * FROM ims_users

2、范围条件操作

array('>', '', '!=', '>=', '<=', 'NOT IN',

'not in', '+=', '-=');

//获取adid大于269的公众号

$accout = pdo_get('account', array('acid >' => '269'));

//增加一次用户的错误登录次数，两次变为2即可

pdo_update('users_failed_login', array('count +=' =>

1),array('username' => 'qiuweinan'));

3、查询

pdo_get：根据条件(AND连接)到指定的表中获取一条记录

array

| boolean pdo_get($tablename, $condition = array(), $fields =

array());

//根据uid获取用户的用户名和用户Id信息

//生成的SQL等同于：SELECT username,uid FROM ims_users WHERE uid = '1'

LIMIT 1

$user = pdo_get('users', array('uid' => 1), array('username',

'uid'));

//生成的SQL等同于：SELECT username FROM ims_users WHERE username =

'qiuweinan' AND status = '1' LIMIT 1

$user = pdo_get('users', array('username' => 'qiuweinan',

'status' => 1), array('username'));

pdo_getcolumn：根据条件(AND连接)到指定的表中获取一条记录的指定字段

string

| int pdo_getcolumn($tablename, $condition = array(),

$field);

//根据uid获取用户的用户名

//生成的SQL等同于：SELECT username FROM ims_users WHERE uid = '1'

LIMIT1

$username = pdo_getcolumn('users', array('uid' => 1),

'username');

pdo_getall：根据条件(AND连接)到指定的表中获取全部记录

array

| boolean pdo_getall($tablename, $condition = array(), $fields =

array(), $keyfiled = '');

//获取全部启用的用户

//生成的SQL等同于：SELECT * FROM ims_users WHERE status = '1'

$user = pdo_getall('users', array('status' => 1));

pdo_getslice：根据条件(AND连接)到指定的表中获取某个区间的记录，此函数和pdo_getall的区别是可以指定limit的值

array

| boolean pdo_getslice($tablename, $condition = array(), $limit =

array(), &$total = null, $fileds = array(), $keyfield =

'');

$user = pdo_getslice('users', array(), array(0,10), $total);

echo $total;

pdo_fetch：根据SQL语句，查询一条记录

array

| boolean pdo_fetch($sql, $params = array());

// :uid 是参数的一个点位符，没有使用引号，传入的第二个参数中要与SQL中的占位名称相同

$user = pdo_fetch("SELECT username, uid FROM ".tablename('users')."

WHERE uid = :uid LIMIT 1", array(':uid' => 1));

// LIKE 占位的使用方法

$user = pdo_fetch("SELECT * FROM ".tablename('users')." WHERE

username LIKE :username", array(':username' =>

'%qiuweinan%'));

pdo_fetchcolumn：根据SQL语句，查询第一条记录的第N列的值，此语句与pdo_fetch使用相同，只是此函数返回的不是一个数组而是一个字符串

string

| boolean pdo_fetchcolumn($sql, $params = array(), $column =

0);

//获取用户的总数，返回的值是一个数字

$user_total = pdo_fetchcolumn("SELECT COUNT(*) FROM

".tablename('users'));

pdo_feachall：根据SQL语句，查询全部记录，使用方法与pdo_feach相同

array

| boolean pdo_fetchall($sql, $params = array(), $keyfield =

'');

//需要注意的是，返回的数组的键值为用户的uid

$user = pdo_fetchall("SELECT username,uid FROM

".tablename('users'), array(), 'uid');

4、变更

pdo_insert：对指定数据表插入一条新记录

int

| boolean pdo_insert($tablename, $data = array(), $replace =

false);

//添加一条用户记录，并判断是否成功

$user_data = array(

'username' => 'qiuweinan1',

'status' => '1',

);

$result = pdo_insert('users', $user_data);

if(!empty($result)){

$uid = pdo_insertid();

message('添加用户成功，UID为' .$uid);

}

pdo_update：更新指定的数据表记录

array

| boolean pdo_updata($tablename, $data = array(), $condition, $glue

= 'AND');

//更新uid=2的用户的用户名

$user_data = array(

'username' => 'qiuweinan2',

);

$result = pdo_update('users', $user_data, array('id' =>2));

if(!empty($result)){

message('更新成功');

}

pdo_delete：删除指定条件的数据

int

| boolean pdo_delete($tablename, $condition = array(), $glue =

'AND');

//删除用户名为qiuweinan2的记录

$result = pdo_delete('users', array('username' =>

'qiuweinan2'));

if(!empty($result)){

message('删除成功');

}

5、运行SQL

pdo_query：运行一条SQL语句

int

| boolean pdo_query($sql, $params = array());

//更新uid=2的用户的用户名

$result = pdo_query("UPDATA ".tablename('users')." SET username =

:username, age = :age WHERE uid = :uid", array(':username' =>

'qiuweinan2', ':age' => 18, ':uid' => 2));

//删除用户名为qiuweinan2的记录

$result = pdo_query("DELETE FROM ".tablename('users')." WHERE

username = :username", array(':username' => 'qiuweinan2'));

if(!empty($result)){

message('删除成功');

}

pdo_run：批量执行语句

boolean

run($sql, $stuff = 'ims_');

$sql = <<

CREATE TABLE IF NOT EXISTS `ims_multisearch` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`weid` int(10) unsigned NOT NULL,

PRIMARY KEY (`id`)

)ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ims_multisearch_fields` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`reid` int(10) unsigned NOT NULL,

`type` tinyint(1) unsigned NOT NULL DEFAULT `1`,

PRIMARY KEY(`id`),

KEY 'idx_reid' (`reid`)

)ENGINE=MyISAM DEFAULT CHARSET=utf8;

EOF;

pdo_run($sql);

6、辅助函数

pdo_fieldexists：检查表中是否存在某个字段

boolean

pdo_fieldexists($tablename, $fieldname);

//如果shopping_goods表中不存在credit字段，则新增credit字段

if(!pdo_fieldexists('shopping_goods', 'credit')) {

pdo_query("ALTER TABLE ".tablename('shopping_goods')." ADD

`credit` int(11) NOT NULL DEFAULT '0';");

}

pdo_indexexists：检查表中是否存在某个索引

boolean

pdo_indexexists($tablename, $indexname);

//如果site_slide表中不存在multiid索引，则新增multiid索引

if(!pdo_indexexists('site_slide', 'multiid')) {

pdo_query("ALTER TABLE ".tablename('site_slide')." ADD INDEX

`multiid` (`multid`);")

}

pdo_tableexists：检查数据库中是否存在某个表

boolean

pdo_tableexists($tablename);

pdo_debug：调试运行SQL语句，显示执行过的SQL的栈情况

array

pdo_debug($output = true, $append = array());

pdo_debug();

//调用该函数结果如下

Array

(

[0] => Array

(

[sql] => SET NAMES 'utf8';

[error] => Array

(

[0] => 00000

[1] =>

[2] =>

)

)

[1] => Array

(

[sql] => SELECT `value` FROM `ims_core_cache` WHERE

`key`=:key

[params] => Array

(

[:key] => setting

)

[error] => Array

(

[0] => 00000

[1] =>

[2] =>

)

)

)