WHMCS NEW MYSQL Action Mode

WHMCS 6.0 新MYSQL数据的操作代码示例
备用的 基本沿用了laravel的DB框架
使用直接看官方的也可以


数据库更改
参考
http://laravel.com/docs/5.1/database
http://laravel.com/docs/5.1/queries
https://github.com/illuminate/database

###使用
use Illuminate\Database\Capsule\Manager as DB;

查询
$users = DB::table('users')->get();
$user = DB::table('users')->where('name', 'John')->first();
$email = DB::table('users')->where('name', 'John')->value('email');
$users = DB::table('users')->where('votes', '=', 100)->get();
$users = DB::table('users')->where('votes', '>', 100)->get();
$users = DB::table('users')->where('votes', 100)->get();
$users = DB::table('users')
->where('votes', '>=', 100)
->get();

$users = DB::table('users')
->where('votes', '<>', 100)
->get();

$users = DB::table('users')
->where('name', 'like', 'T%')
->get();
$users = DB::table('users')
->where('votes', '>', 100)
->orWhere('name', 'John')
->get();
$users = DB::table('users')
->whereBetween('votes', [1, 100])->get();

$users = DB::table('users')
->whereNotBetween('votes', [1, 100])
->get();
$users = DB::table('users')
->whereIn('id', [1, 2, 3])
->get();
$users = DB::table('users')
->whereNotIn('id', [1, 2, 3])
->get();
$users = DB::table('users')
->whereNull('updated_at')
->get();
$users = DB::table('users')
->whereNotNull('updated_at')
->get();
DB::table('users')
->where('name', '=', 'John')
->orWhere(function ($query) {
$query->where('votes', '>', 100)
->where('title', '<>', 'Admin');
})
->get();

DB::table('users')
->whereExists(function ($query) {
$query->select(DB::raw(1))
->from('orders')
->whereRaw('orders.user_id = users.id');
})
->get();

$results = DB::select('select * from users where id = ?', array(1));
$users = DB::select('select * from users where active = ?', [1]);
$results = DB::select('select * from users where id = :id', ['id' => 1]);
$users = DB::table('users')->select('name', 'email as user_email')->get();
$users = DB::table('users')->distinct()->get();
$query = DB::table('users')->select('name');

##排序查询
$users = DB::table('users')
->orderBy('name', 'desc')
->get();

$users = DB::table('users')
->groupBy('account_id')
->having('account_id', '>', 100)
->get();

$users = DB::table('orders')
->select('department', DB::raw('SUM(price) as total_sales'))
->groupBy('department')
->havingRaw('SUM(price) > 2500')
->get();

$users = DB::table('users')->skip(10)->take(5)->get();

##群组查询
$users = DB::table('users')
->select(DB::raw('count(*) as user_count, status'))
->where('status', '<>', 1)
->groupBy('status')
->get();

##分片查询
DB::table('users')->chunk(100, function($users) {
foreach ($users as $user) {
}
});
##分片查询 强制结束
DB::table('users')->chunk(100, function($users) {
// Process the records...
return false;
});

##查询单列内容
$titles = DB::table('roles')->lists('title');
foreach ($titles as $title) {
echo $title;
}
##定制键
$roles = DB::table('roles')->lists('title', 'name');
foreach ($roles as $name => $title) {
echo $title;
}
##计算查询 支持 count, max, min, avg, and sum.
$users = DB::table('users')->count();
$price = DB::table('orders')->max('price');
$price = DB::table('orders')
->where('finalized', 1)
->avg('price');

##关联查询
$users = DB::table('users')
->join('contacts', 'users.id', '=', 'contacts.user_id')
->join('orders', 'users.id', '=', 'orders.user_id')
->select('users.*', 'contacts.phone', 'orders.price')
->get();

$users = DB::table('users')
->leftJoin('posts', 'users.id', '=', 'posts.user_id')
->get();

DB::table('users')
->join('contacts', function ($join) {
$join->on('users.id', '=', 'contacts.user_id')->orOn(...);
})
->get();
DB::table('users')
->join('contacts', function ($join) {
$join->on('users.id', '=', 'contacts.user_id')
->where('contacts.user_id', '>', 5);
})
->get();

##联合查询
$first = DB::table('users')
->whereNull('first_name');

$users = DB::table('users')
->whereNull('last_name')
->union($first)
->get();

插入
DB::insert('insert into users (id, name) values (?, ?)', [1, 'Dayle']);
DB::table('users')->insert(
['email' => '[email protected]', 'votes' => 0]
);
DB::table('users')->insert([
['email' => '[email protected]', 'votes' => 0],
['email' => '[email protected]', 'votes' => 0]
]);
##返回插入ID
$id = DB::table('users')->insertGetId(
['email' => '[email protected]', 'votes' => 0]
);

更新
$affected = DB::update('update users set votes = 100 where name = ?', ['John']);
DB::table('users')
->where('id', 1)
->update(['votes' => 1]);
DB::table('users')->increment('votes');
DB::table('users')->increment('votes', 5);
DB::table('users')->decrement('votes');
DB::table('users')->decrement('votes', 5);
DB::table('users')->increment('votes', 1, ['name' => 'John']);

删除表
$deleted = DB::delete('delete from users');
DB::table('users')->where('votes', '< ', 100)->delete();
DB::table('users')->truncate(); ##自增ID恢复为0

删除数据
DB::statement('drop table users');
多数据库
$users = DB::connection('foo')->select(...);
PDO模式
$pdo = DB::connection()->getPdo();

共享锁
DB::table('users')->where('votes', '>', 100)->sharedLock()->get();
DB::table('users')->where('votes', '>', 100)->lockForUpdate()->get();

创建表
DB::schema()->create('users', function($table) {
$table->increments('id');
$table->string('email')->unique();
$table->timestamps();
});
##判断是否存在 表 列
if (Schema::hasTable('users')) {

}
if (Schema::hasColumn('users', 'email')) {

}
## 创建非主链接
Schema::connection('foo')->create('users', function ($table) {
$table->increments('id');
});
##设置存储引擎
Schema::create('users', function ($table) {
$table->engine = 'InnoDB';
$table->increments('id');
});
##重命名
Schema::rename($from, $to);
##删除
Schema::drop('users');
Schema::dropIfExists('users');
##创建字段
Schema::table('users', function ($table) {
$table->string('email');
});

CREATE TABLE cloud_keypairs (
id bigint unsigned NOT NULL auto_increment COMMENT 'id',
uid bigint unsigned NOT NULL COMMENT 'owner, foreign key to account table',
keypair_name varchar(256) NOT NULL COMMENT 'name of the key pair',
fingerprint varchar(128) NOT NULL COMMENT 'fingerprint for the ssh public key',
public_key varchar(5120) NOT NULL COMMENT 'public key of the ssh key pair',
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

改为
DB::schema()->create('mod_sshkeys', function ($table) {
$table->bigIncrements('id');
$table->bigInteger('userid');
$table->string('name', 256);
$table->string('fingerprint', 256);
$table->longText('sshkeys', 5120);
//$table->primary('id'); #ID自动主键 不需要额外追加!
});

##设置变量
$table->bigIncrements('id'); Incrementing ID (primary key) using a "UNSIGNED BIG INTEGER" equivalent.
$table->bigInteger('votes'); BIGINT equivalent for the database.
$table->binary('data'); BLOB equivalent for the database.
$table->boolean('confirmed'); BOOLEAN equivalent for the database.
$table->char('name', 4); CHAR equivalent with a length.
$table->date('created_at'); DATE equivalent for the database.
$table->dateTime('created_at'); DATETIME equivalent for the database.
$table->decimal('amount', 5, 2); DECIMAL equivalent with a precision and scale.
$table->double('column', 15, 8); DOUBLE equivalent with precision, 15 digits in total and 8 after the decimal point.
$table->enum('choices', ['foo', 'bar']); ENUM equivalent for the database.
$table->float('amount'); FLOAT equivalent for the database.
$table->increments('id'); Incrementing ID (primary key) using a "UNSIGNED INTEGER" equivalent.
$table->integer('votes'); INTEGER equivalent for the database.
$table->json('options'); JSON equivalent for the database.
$table->jsonb('options'); JSONB equivalent for the database.
$table->longText('description'); LONGTEXT equivalent for the database.
$table->mediumInteger('numbers'); MEDIUMINT equivalent for the database.
$table->mediumText('description'); MEDIUMTEXT equivalent for the database.
$table->morphs('taggable'); Adds INTEGER taggable_id and STRING taggable_type.
$table->nullableTimestamps(); Same as timestamps(), except allows NULLs.
$table->rememberToken(); Adds remember_token as VARCHAR(100) NULL.
$table->smallInteger('votes'); SMALLINT equivalent for the database.
$table->softDeletes(); Adds deleted_at column for soft deletes.
$table->string('email'); VARCHAR equivalent column.
$table->string('name', 100); VARCHAR equivalent with a length.
$table->text('description'); TEXT equivalent for the database.
$table->time('sunrise'); TIME equivalent for the database.
$table->tinyInteger('numbers'); TINYINT equivalent for the database.
$table->timestamp('added_on'); TIMESTAMP equivalent for the database.
$table->timestamps(); Adds created_at and updated_at columns.

##修改表
Schema::table('users', function ($table) {
$table->string('email')->nullable();
});
->first() Place the column "first" in the table (MySQL Only)
->after('column') Place the column "after" another column (MySQL Only)
->nullable() Allow NULL values to be inserted into the column
->default($value) Specify a "default" value for the column
->unsigned() Set integer columns to UNSIGNED

Schema::table('users', function ($table) {
$table->string('name', 50)->change();
});

Schema::table('users', function ($table) {
$table->string('name', 50)->nullable()->change();
});

Schema::table('users', function ($table) {
$table->renameColumn('from', 'to');
});

Schema::table('users', function ($table) {
$table->dropColumn('votes');
});

Schema::table('users', function ($table) {
$table->dropColumn(['votes', 'avatar', 'location']);
});

##创建索引
$table->string('email')->unique();
$table->unique('email');
$table->index(['account_id', 'created_at']);
可用类型
$table->primary('id'); Add a primary key.
$table->primary(['first', 'last']); Add composite keys.
$table->unique('email'); Add a unique index.
$table->index('state'); Add a basic index.
##删除索引
$table->dropPrimary('users_id_primary'); Drop a primary key from the "users" table.
$table->dropUnique('users_email_unique'); Drop a unique index from the "users" table.
$table->dropIndex('geo_state_index'); Drop a basic index from the "geo" table.

##联动
Schema::table('posts', function ($table) {
$table->integer('user_id')->unsigned();
$table->foreign('user_id')->references('id')->on('users');
});
$table->foreign('user_id')
->references('id')->on('users')
->onDelete('cascade');
$table->dropForeign('posts_user_id_foreign');

最近通过搜索访问本文章的关键词:

基于PHP MYSQL的Tags/Tagging 标签系统设计实例

基于PHP MYSQL的Tags/Tagging 标签系统设计实例 / Tagging With PHP And MySQL

Contents

Abstract
The Database
The Database Connection
Adding A Type
Adding A Tag
Fetching Related Tags

Abstract

With the proliferation of web sites now driven by relational databases, it is not surprising that new and innovative ways continue to emerge for data relationships. Traditionally, groups of data have been stored in categories, but the demand for better and more prolific relationships has seen the evolution of tags. Tags are not replacing categories, but are further extending the relationship between data and objects within databases. 继续阅读 →

最近通过搜索访问本文章的关键词:

Warning: sqlite_open() [function.sqlite-open]: file is encrypted or is not a database in 错误解决

晕死 竟然遇到这种问题
Warning: sqlite_open() [function.sqlite-open]: file is encrypted or is not a database in 错误解决

提示文件可能加密或者不是数据库文件
悲剧

检查了下
竟然是版本的问题
2个db 一个OK
一个不行
建立的工具不一样

一个版本 显示 SQLite version: 2.8.17
另外一个显示 SQLite version: 3.7.7.1

拿2的class去读3的db 所以出现这个问题

转换下数据库就可以了
版本 2 转换3 或者3to2

解决

最近通过搜索访问本文章的关键词:

DirectAdmin 也不靠谱啊….

DirectAdmin 也不靠谱啊….

妈的 又一台机器硬盘快报销
启动3次才启动 检查发现有坏的区块
赶紧备份ing

结果发现备份网站文件 域名文件夹 备份 email ftp
全部能恢复 mysql恢复的时候 全部出错

找半天原因 才发现mysql文件全部被加了一个提示头
–all 什么的警告

DirectAdmin官方坛子里也有遇到这个问题的
让升级版本 重新备份 在恢复
问题是我已经删光了…
哪里去重新备份去….

妈的..真火大
还好用户不多 一个一个的修改 一个一个的导入
问题解决了…..

下次一定备份一定恢复一个试试看在删除….