mysql 数据库分类设计方法与PHP结合
以上是数据表
代码如下:
<?php
define('DB_HOST','localhost');
define('DB_USER','root');
define('DB_PASSWORD','123456');
define('DB_NAME','test');
define('DB_CHARSET','utf8');
class Model
{
public static $conn = null;
function __construct()
{
if(is_null(self::$conn))
{
$link = new mysqli(DB_HOST,DB_USER,DB_PASSWORD,DB_NAME);
if(mysqli_connect_errno())
{
die('error: ' . mysqli_connect_error());
}
$link->query("set names " . DB_CHARSET);
self::$conn = $link;
}
return self::$conn;
}
// 增加内容
function add($sql)
{
self::$conn->query($sql);
return self::$conn->insert_id;
}
// 删除
function del($sql)
{
self::$conn->query($sql);
return self::$conn->affected_rows;
}
// 修改
function update($sql)
{
self::$conn->query($sql);
return self::$conn->affected_rows;
}
// 查找
function query($sql)
{
$arr = array();
$res = self::$conn->query($sql);
if(!$res)
{
return 0;
}
while($row = $res->fetch_assoc())
{
$arr[] = $row;
}
return $arr;
}
// 析构函数
/*
function __destruct()
{
self::$conn->close();
}
*/
}
// 无限分类，做成分类树
function t($parent = 0)
{
$model = new Model();
$sql = "select * from cate where parent = " . $parent . " order by sort asc";
$result = $model->query($sql);
if($result)
{
foreach($result as $key=>$val)
{
$children = t($val['id']);
$result[$key]['children'] = $children;
}
}
return $result;
}
$aa = t(0);
print_r($aa);
/*
Array
(
[0] => Array
(
[id] => 3
[name] => 手机/数码
[parent] => 0
[sort] => 50
[children] => Array
(
[0] => Array
(
[id] => 4
[name] => 功能机
[parent] => 3
[sort] => 100
[children] => Array
(
[0] => Array
(
[id] => 10
[name] => 苹果
[parent] => 4
[sort] => 100
[children] => Array
(
)
)
)
)
[1] => Array
(
[id] => 5
[name] => 智能机
[parent] => 3
[sort] => 100
[children] => Array
(
[0] => Array
(
[id] => 11
[name] => 山寨机
[parent] => 5
[sort] => 100
[children] => Array
(
[0] => Array
(
[id] => 12
[name] => 合约机
[parent] => 11
[sort] => 100
[children] => Array
(
)
)
)
)
)
)
)
)
[1] => Array
(
[id] => 7
[name] => 电器
[parent] => 0
[sort] => 80
[children] => Array
(
[0] => Array
(
[id] => 9
[name] => 冰箱
[parent] => 7
[sort] => 100
[children] => Array
(
)
)
)
)
[2] => Array
(
[id] => 1
[name] => 服装
[parent] => 0
[sort] => 100
[children] => Array
(
[0] => Array
(
[id] => 2
[name] => 男装
[parent] => 1
[sort] => 100
[children] => Array
(
[0] => Array
(
[id] => 8
[name] => 男上装
[parent] => 2
[sort] => 100
[children] => Array
(
)
)
)
)
[1] => Array
(
[id] => 6
[name] => 女装
[parent] => 1
[sort] => 100
[children] => Array
(
)
)
)
)
)
*/
// 查找父级路径，用于制作面包屑
function tt($id = 11)
{
$model = new Model();
$sql = "select * from cate where id = " . $id;
$result = $model->query($sql);
if($result)
{
$children = tt($result[0]['parent']);
$result[]['children'] = $children;
}
return $result;
}
$bb = tt(12);
print_r($bb);
/*
Array
(
[0] => Array
(
[id] => 12
[name] => 合约机
[parent] => 11
[sort] => 100
)
[1] => Array
(
[children] => Array
(
[0] => Array
(
[id] => 11
[name] => 山寨机
[parent] => 5
[sort] => 100
)
[1] => Array
(
[children] => Array
(
[0] => Array
(
[id] => 5
[name] => 智能机
[parent] => 3
[sort] => 100
)
[1] => Array
(
[children] => Array
(
[0] => Array
(
[id] => 3
[name] => 手机/数码
[parent] => 0
[sort] => 50
)
[1] => Array
(
[children] => Array
(
)
)
)
)
)
)
)
)
)
*/
<?php $link = mysql_connect('localhost', 'root', '123456') or die('Error:' . mysql_errro()); mysql_select_db('test', $link); mysql_query("set names utf8", $link); $id = isset($_GET['id']) && intval($_GET['id']) > 0 ? intval($_GET['id']) : 0; $arr = array(); // 从子类向顶级父类寻找，适合制作面包屑,测试数据为 cate = 3 function fromSonToParent($cate = 0) { global $link; $index = 0; $cats = array(); $sql = "select * from cate"; $res = mysql_query($sql); if(mysql_num_rows($res)) { $arr = array(); while($r = mysql_fetch_assoc($res)) { $arr[] = $r; } while(1) { foreach($arr as $k => $v) { if($cate == $v['id']) { $cate = $v['parent']; $cats[] = array('id'=>$v['id'], 'name'=>$v['name']); unset($arr[$k]); $index++; break; } } if($index == 0 || $cate == 0) { break; } } } return $cats; } $a = fromSonToParent($id); krsort($a); print_r($a); /* Array ( [3] => Array ( [id] => 3 [name] => 手机/数码 ) [2] => Array ( [id] => 5 [name] => 智能机 ) [1] => Array ( [id] => 11 [name] => 山寨机 ) [0] => Array ( [id] => 12 [name] => 合约机 ) ) */ // 从父类中寻找所有子类,一般找分类下的产品,此 id = 12,测试数据 function fromParentToSon($cate = 0) { global $link; $index = 0; $cats = array(); $sql = "select * from cate"; $res = mysql_query($sql); if(mysql_num_rows($res)) { $arr = array(); $ids = array(); while($r = mysql_fetch_assoc($res)) { $arr[] = $r; } $len = count($arr); while(1) { foreach($arr as $k => $v) { if($cate == $v['parent'] || in_array($v['parent'], $ids)) { array_push($ids,$v['id']); $cats[] = array('id'=>$v['id'], 'name'=>$v['name']); unset($arr[$k]); } } $index++; if($index == $len) { break; } } } return $cats; } $a = fromParentToSon($id); print_r($a); /* Array ( [0] => Array ( [id] => 4 [name] => 功能机 ) [1] => Array ( [id] => 5 [name] => 智能机 ) [2] => Array ( [id] => 10 [name] => 苹果 ) [3] => Array ( [id] => 11 [name] => 山寨机 ) [4] => Array ( [id] => 12 [name] => 合约机 ) ) */
浙公网安备 33010602011771号