$list=$cate->field(“id,name,pid,path,concat(path,’-‘,id) as
bpath”)->order(‘bpath’)->select(); 

sqlite3是一个简单的前端数据库,对于一些动作和前段保存数据比较多的游戏使用还是很方便

 

图片 1

 

 代码如下

图片 2

 

//////////////
//////无限分类的数据库设计及样例
//////////////
mysql> create
database db_kind;
Query OK, 1 row affected

图片 3

#ifndef __Sqlite3Test__DataBaseHelper__

mysql> use db_kind;
Database changed
mysql> create table tb_kind(
-> id int not null auto_increment primary key,
-> pid int,
-> path varchar(200)
-> );
Query OK, 0 rows affected

所以对于前台而言,控制器已经帮忙排好序了,至于如何让它变的更清晰,count是关键

#define __Sqlite3Test__DataBaseHelper__

mysql> insert into tb_kind values(null,”新闻”,0,0);
Query OK, 1 row affected

我认为count也可以以path取值,它相当于一个在排序的基础上,使分类可视化更明了的一个工具。。。

 

mysql> insert into tb_kind values(null,”视频”,0,0);
Query OK, 1 row affected


#include

mysql> insert into tb_kind values(null,”图片”,0,0);
Query OK, 1 row affected

 

mysql> insert into tb_kind values(null,”博客”,0,0);
Query OK, 1 row affected

#include “sqlite3.h”

mysql> insert into tb_kind values(null,”体育新闻”,1,”0-1″);
Query OK, 1 row affected

#include

mysql> insert into tb_kind values(null,”娱乐新闻”,1,”0-1″);
Query OK, 1 row affected

#include

mysql> insert into tb_kind values(null,”财经新闻”,1,”0-1″);
Query OK, 1 row affected

 

mysql> select * from
db_kind;
ERROR 1146 : Table ‘db_kind.db_kind’ doesnot exist
mysql> select * from tb
_kind;
+—-+———-+—–+——+
| id | pname | pid | path |
+—-+———-+—–+——+
| 1 | 新闻 | 0 | 0 |
| 2 | 视频 | 0 | 0 |
| 3 | 图片 | 0 | 0 |
| 4 | 博客 | 0 | 0 |
| 5 | 体育新闻 | 1 | 0-1 |
| 6 | 娱乐新闻 | 1 | 0-1 |
| 7 | 财经新闻 | 1 | 0-1 |
+—-+———-+—–+——+
7 rows in set
mysql> insert into tb_kind values(null,”篮球新闻”,5,”0-1-5″);
Query OK, 1 row affected

#define DBNOTFOUND INT_MAX

mysql> insert into tb_kind values(null,”足球新闻”,5,”0-1-5″);
Query OK, 1 row affected

 

mysql> select * from tb_kind;
+—-+———-+—–+——-+
| id | pname | pid | path |
+—-+———-+—–+——-+
| 1 | 新闻 | 0 | 0 |
| 2 | 视频 | 0 | 0 |
| 3 | 图片 | 0 | 0 |
| 4 | 博客 | 0 | 0 |
| 5 | 体育新闻 | 1 | 0-1 |
| 6 | 娱乐新闻 | 1 | 0-1 |
| 7 | 财经新闻 | 1 | 0-1 |
| 8 | 篮球新闻 | 5 | 0-1-5 |
| 9 | 足球新闻 | 5 | 0-1-5 |
+—-+———-+—–+——-+
9 rows in set

class DataBaseHelper

mysql> insert into tb_kind values(null,”NBA”,8,”0-1-5-8″);
Query OK, 1 row affected

{ //=====数据库操作手柄======

mysql> insert into tb_kind values(null,”CBA”,8,”0-1-5-8″);
Query OK, 1 row affected

public:

mysql> select * from tb_kind;
+—-+———-+—–+———+
| id | pname | pid | path |
+—-+———-+—–+———+
| 1 | 新闻 | 0 | 0 |
| 2 | 视频 | 0 | 0 |
| 3 | 图片 | 0 | 0 |
| 4 | 博客 | 0 | 0 |
| 5 | 体育新闻 | 1 | 0-1 |
| 6 | 娱乐新闻 | 1 | 0-1 |
| 7 | 财经新闻 | 1 | 0-1 |
| 8 | 篮球新闻 | 5 | 0-1-5 |
| 9 | 足球新闻 | 5 | 0-1-5 |
| 10 | NBA | 8 | 0-1-5-8 |
| 11 | CBA | 8 | 0-1-5-8 |
+—-+———-+—–+———+
11 rows in set

static DataBaseHelper* sharedDataBaseHelper();

mysql> select concat(path,”-“,id) from tb_kind;
+———————+
| concat(path,”-“,id) |
+———————+
| 0-1 |
| 0-2 |
| 0-3 |
| 0-4 |
| 0-1-5 |
| 0-1-6 |
| 0-1-7 |
| 0-1-5-8 |
| 0-1-5-9 |
| 0-1-5-8-10 |
| 0-1-5-8-11 |
+———————+
11 rows in set

~DataBaseHelper();

mysql> select concat(path,”-“,id) from tb_kind;
+———————+
| concat(path,”-“,id) |
+———————+
| 0-1 |
| 0-2 |
| 0-3 |
| 0-4 |
| 0-1-5 |
| 0-1-6 |
| 0-1-7 |
| 0-1-5-8 |
| 0-1-5-9 |
| 0-1-5-8-10 |
| 0-1-5-8-11 |
+———————+
11 rows in set

int countForTable(const char * table);

mysql> select concat(path,”-“,id) as abs from tb_kind order by
abs.path;
ERROR 1054 : Unknown column ‘abs.path’ in ‘order clause’
mysql> select concat(path,”-“,id) as abs from tb_kind order by abs

sqlite3_stmt * queryTable(const char * table, const char *fields,
const char *condition, int offset=0, int count=0);

+————+
| abs |
+————+
| 0-1 |
| 0-1-5 |
| 0-1-5-8 |
| 0-1-5-8-10 |
| 0-1-5-8-11 |
| 0-1-5-9 |
| 0-1-6 |
| 0-1-7 |
| 0-2 |
| 0-3 |
| 0-4 |
+————+
11 rows in set
mysql> select concat(path,”-“,id) as,id,name,path abs from tb_kind
order by abs;
ERROR 1064 : You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near ‘id,name,path abs from tb_kind order by abs’ at line 1
mysql> select concat(path,”-“,id) as abs,
id,pname,path abs from tb_kind order by abs;
+————+—-+———-+———+
| abs | id | pname | abs |
+————+—-+———-+———+
| 0-1 | 1 | 新闻 | 0 |
| 0-1-5 | 5 | 体育新闻 | 0-1 |
| 0-1-5-8 | 8 | 篮球新闻 | 0-1-5 |
| 0-1-5-8-10 | 10 | NBA | 0-1-5-8 |
| 0-1-5-8-11 | 11 | CBA | 0-1-5-8 |
| 0-1-5-9 | 9 | 足球新闻 | 0-1-5 |
| 0-1-6 | 6 | 娱乐新闻 | 0-1 |
| 0-1-7 | 7 | 财经新闻 | 0-1 |
| 0-2 | 2 | 视频 | 0 |
| 0-3 | 3 | 图片 | 0 |
| 0-4 | 4 | 博客 | 0 |
+————+—-+———-+———+
11 rows in set
mysql>

static void destroy();

php处理分类源码

void openSqliteInAndroid();

<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN”
“”>
<html xmlns=””>
<head>
<meta http-equiv=”Content-Type” content=”text/html; charset=utf-8″
/>
<title>无标题文档</title>
</head>
<body>
<!–显示结果
新闻
体育新闻
篮球新闻
NBA
CBA
足球新闻
娱乐新闻
财经新闻
视频
图片
博客
–>
<?
$conn=mysql_connect(“localhost”,”root”,”root”);
mysql_select_db(“db_kind”);
mysql_query(“set names utf8”);
$sql=”select concat(path,’-‘,id) as abspath,id,pname,path from tb_kind
order by abspath”;
$rs=mysql_query($sql);
while($result=mysql_fetch_assoc($rs)){
$num=count(explode(“-“,$result[path]))-1;
$new_str=str_repeat(“—“,$num);
echo $new_str.$result[pname];
echo “<br>”;
}
$str=str_repeat(“=”,10);
echo $str;
$num=count(explode(“-“,”0-1-5-8”))-1;
echo $num;
?>
</body>
</html>

 

private:

DataBaseHelper();

static DataBaseHelper *dataBaseHelper;

sqlite3 *database;

};

 

 

template

class DataBaseTable

{ //数据库表类

protected:

DataBaseTable(){}

virtual void parseStatement(sqlite3_stmt *) = 0;

public:

static T findDataById(int tid)

{ //=====根据ID找到数据=======

char condition[20];

sprintf(condition, “id=%d”, tid);

sqlite3_stmt * stmt =
DataBaseHelper::sharedDataBaseHelper()->queryTable(T::tableName(),
NULL, condition,0,1);

T t;

t._id = DBNOTFOUND;

if ((sqlite3_step(stmt)==SQLITE_ROW))

{

t.parseStatement(stmt);

}

sqlite3_finalize(stmt);

return t;

}

 

 

static T findDataByTmp(const char *tmp,int tid)

{

char condition[30];

sprintf(condition, “%s=%d”,tmp,tid); //根据条件判断

sqlite3_stmt * stmt =
DataBaseHelper::sharedDataBaseHelper()->queryTable(T::tableName(),
NULL, condition);

T t;

// t._id = DBNOTFOUND;

if ((sqlite3_step(stmt)==SQLITE_ROW))

{

t.parseStatement(stmt);

}

sqlite3_finalize(stmt);

return t;

}

 

 

 

static T findDataByIdAndName(int tid, const char* name)

{ //根据ID和名字找到数据

char condition[30];

sprintf(condition, “id=%d and name=’%s'”, tid, name);

sqlite3_stmt * stmt =
DataBaseHelper::sharedDataBaseHelper()->queryTable(T::tableName(),
NULL, condition);

T t;

t._id = DBNOTFOUND;

if ((sqlite3_step(stmt)==SQLITE_ROW))

{

t.parseStatement(stmt);

}

sqlite3_finalize(stmt);

return t;

}

//vector容器模板拿到数据

static std::vector findData(const char *condition=NULL, int offset=0,
int count=0)

{

std::vector res;

sqlite3_stmt * stmt =
DataBaseHelper::sharedDataBaseHelper()->queryTable(T::tableName(),
NULL, condition);

while ((sqlite3_step(stmt)==SQLITE_ROW))

{

T t;

t.parseStatement(stmt);

res.push_back(t);

}

sqlite3_finalize(stmt);

return res;

}

 

 

static int count()

{ //返回数据库表的大小

return
DataBaseHelper::sharedDataBaseHelper()->countForTable(T::tableName());

}

};

 

 

#endif /* defined(__Sqlite3Test__DataBaseHelper__) */

 

 

 

 

 

#include “DataBaseHelper.h”

#include

#include “../CCFileUtils.h”

#include

#include

#include “cocos2d.h”

 

 

using namespace std;

using namespace cocos2d;

 

DataBaseHelper *DataBaseHelper::dataBaseHelper = NULL;

 

DataBaseHelper::DataBaseHelper()

{

#if (CC_TARGET_PLATFORM ==
CC_PLATFORM_IOS)

//如果在IOS平台需要打开相应的数据库路径

std::string path =
cocos2d::CCFileUtils::sharedFileUtils()->fullPathForFilename(“data/gameDataBean.db”);

CCLog(“——%s————-“,path.c_str());

int res = sqlite3_open(path.c_str(), &database);

if (res != SQLITE_OK)

{

CCLog(“–>>open db fail,error code is %d”, res);

}

 

#elif (CC_TARGET_PLATFORM == CC_PLATFORM_ANDROID)

openSqliteInAndroid();

#endif

 

}

 

void DataBaseHelper::openSqliteInAndroid()

{

// android
系统不能对assets目录下的文件进行fopen操作,所以copy到
/data/data/包名/files/ 下面再操作

std::string path =
cocos2d::CCFileUtils::sharedFileUtils()->fullPathForFilename(“data/gameDataBean.db”);

std::string writalePath =
CCFileUtils::sharedFileUtils()->getWritablePath() +
“gameDataBean.db”;

 

unsigned long len = 0;

unsigned char *data = NULL;

 

data = CCFileUtils::sharedFileUtils()->getFileData(path.c_str(),
“r”, &len);

 

FILE *fp = fopen(writalePath.c_str(),”r”);

if(!fp)

{

// 数据库存在的话就别再copy过去了

FILE *fp1 = fopen(writalePath.c_str(), “w+”);

fwrite(data, sizeof(char), len, fp1);

fclose(fp1);

} else{

fclose(fp);

}

 

 

int res = sqlite3_open(writalePath.c_str(), &database);

if (res != SQLITE_OK)

{

CCLog(“–>>open db fail,error code is %d”, res);

}

}

 

DataBaseHelper* DataBaseHelper::sharedDataBaseHelper() {

if (!dataBaseHelper) {

dataBaseHelper = new DataBaseHelper();

::atexit(destroy);

}

return dataBaseHelper;

}

 

void DataBaseHelper::destroy()

{

if (dataBaseHelper)

{

delete dataBaseHelper;

}

}

 

DataBaseHelper::~DataBaseHelper()

{

sqlite3_close(database);

}

int DataBaseHelper::countForTable(const char * table) { //返回表的数量

char *sql = (char *)malloc(strlen(table)+22);

int count = -1;

sprintf(sql, “select count(*) from %s”, table);

 

//
在sqlite中并没有定义sqlite3_stmt这个结构的具体内容,它只是一个抽象类型,在使用过程中一般以它的指针进行操作,

//而sqlite3_stmt类型的指针在实际上是一个指向Vdbe的结构体得指针

sqlite3_stmt *statement;

 

if (sqlite3_prepare_v2(database, sql, -1, &statement,
NULL)==SQLITE_OK) { //sqlite3_prepare_v2 查询数据库接口

if (sqlite3_step(statement)==SQLITE_ROW)

{

count = sqlite3_column_int(statement, 0);

}

}

free(sql);

return count;

}

 

//查询表

sqlite3_stmt * DataBaseHelper::queryTable(const char * table, const
char *fields, const char *condition, int offset, int count)

{

string sql = string(“select “);

if (fields) {

sql.append(fields);

} else {

sql.append(“*”);

}

sql.append(” from “);

sql.append(table);

if (condition) {

sql.append(” where “);

sql.append(condition);

}

 

if (count)

{

sql.append(” limit “);

char tmp[20];

sprintf(tmp, “%d,%d”, offset, count);

sql.append(tmp);

}

sql.append(“;”);

// sqlite3_stmt
它是一个已经把sql语句解析了的、用sqlite自己标记记录的内部数据结构。

sqlite3_stmt *statement;

 

if (sqlite3_prepare_v2(database, sql.c_str(), -1, &statement,
NULL)==SQLITE_OK) {

return statement;

}

return NULL;

}

Author

发表评论

电子邮件地址不会被公开。 必填项已用*标注