齐力众信上机题

1.根据等级和积分设计一个表

如我是70分，我可以通过积分快速找到对应的等级。

1.建表
create table dengji (
        id int(11) auto_increment not null primary key,
        startscore int(11) not null default 0,
        endscore int(11) not null default 0,
        rankname char(4) not null default '' 
        )ENGINE=InnoDB  DEFAULT CHARSET=utf8;

2.插入数据
insert into dengji (startscore,endscore,rankname) values (1,50,'L1'),(51,100,'L2'),(101,150,'L3');

PHP代码:

<?php
//pdo连接数据库
$dsn = 'mysql:dbname=code;host=127.0.0.1';
$user = 'root';
$password = '';
try{
$dbh = new PDO($dsn,$user,$password);
}catch(PDOException $e){
echo 'Connection failed:' . $e->getMessage();
}
$score = $_GET['sc'];
$sql = 'select * from dengji where startscore<=? and ?<=endscore';
$st = $dbh->prepare($sql);
$st->execute([$score,$score]);
$res = $st->fetch(PDO::FETCH_ASSOC);
echo $res['rankname'];
?>
// http://www.typ.com/code/dengji.php?sc=140 地址栏这么测试;

2.做出城市展现(如以下格式) 用递归最佳

北京
    -东城区
    -西城区
山西
    -晋中市
    -吕梁市

//1.建表
create table city (
        id int auto_increment not null primary key,
        name char(10) not null default '',
        pid int not null default 0
        )ENGINE=InnoDB  DEFAULT CHARSET=utf8;
//2.插入数据
insert into city (name,pid) values ('北京',0),('东城区',1),('西城区',1),('山西',0),('吕梁市',4),('晋中市',4);

PHP代码:

<?php
//连接数据库
$dsn = 'mysql:dbname=code;host=127.0.0.1';
$user = 'root';
$pwd = '';
try{
    $pdo = new PDO($dsn,$user,$pwd);
    $pdo->query('SET NAMES utf8');
}catch(PDOException $e){
    echo 'Connection feild:' . $e->getMessage();
}
    $sql = 'select * from city';
//获取二维数组
$res = $pdo->query($sql)->fetchAll(2);
//递归方法获取标准的二维数组
function getTree($pid=0,$lev=0){
    $tree=array();
    global $res;
    foreach ($res as $cat) {
        if ($cat['pid'] == $pid) {
            $cat['lev'] = $lev;
            $tree[] = $cat;
            $tree = array_merge($tree,getTree($cat['cid'],$lev+1));
                }
            }
            return $tree;
        }
//循环输出
$arr = getTree(0,0);
foreach ($arr as $v) {
    echo str_repeat("|--", $v['lev']) . $v['name'].'<br>';
}