PHP文章数据渲染

页面效果

数据库查询结果

查询所有数据 php代码

$posts = baixiu_fetch_all("select 
posts.id as posts_id,
posts.title as posts_title,
posts.created as posts_created,
categories.name as categories_name,
categories.slug as categories_slug,
posts.`status`as posts_status,
users.slug as users_slug,
users.nickname as users_nickname
from posts
inner join categories on posts.category_id= categories.id
inner join users on posts.category_id = users.id
where {$where}
order by posts.created desc
limit {$offset},{$size}

");

html页面全部数据渲染

      <?php foreach  ($posts as $item): ?>
        <tr>
            <td class="text-center"><input type="checkbox"></td>
            <td><?php echo $item['posts_title']?></td>
            <!-- <td><?php// echo get_users($item['user_id']);?></td> -->
            <!-- <td><?php //echo get_categories($item['category_id'])?></td> -->
            <td><?php echo $item['categories_name'];?></td>
            <td><?php echo $item['categories_slug']?></td>
            <td class="text-center"><?php echo xiu_convert_date($item['posts_created'])?></td>
            <td class="text-center"><?php echo xiu_convert_status($item['posts_status'])?></td>
            <td class="text-center">
              <a href="javascript:;" class="btn btn-default btn-xs">编辑</a>
              <a href="/admin/posts-delete.php?id=<?php echo $item['posts_id'];?>" class="btn btn-danger btn-xs">删除</a>
            </td>
          </tr>
        <?php endforeach?>

时间格式处理 php 代码

** 转换时间格式
 * @param 参数 $created 
 * @return 
*/
function xiu_convert_date ($created) {
   $timestamp = strtotime($created);
   return date('Y年m月d日<b\r>H:i:s', $timestamp);
}

时间格式处理 HTML 代码

 <td class="text-center"><?php echo xiu_convert_date($item['posts_created'])?></td>

php三选状态处理

/** 转换时间格式 多种状态
 * @param 参数 $created 
 * @return 
*/
function xiu_convert_status ($status) {
     $dict =   array(
          'published' => '已发布',
          'drafted' => '草稿',
          'trashed' => '回收站'
        );
        //返回有值的数据
        return isset($dict[$status]) ? $dict[$status] : '未知';
}

HTML 三选状态处理

<td class="text-center"><?php echo xiu_convert_status($item['posts_status'])?></td>

//实现分页查询
效果图

php分页，分类

//实现分页功能
$page = empty($_GET['page'])? 1 : (int)$_GET['page'];
$size = 20;

//计算出越过多少条
$offset = ($page-1) * $size;

//限制-1值
if ($page < 1){
  header('location:/admin/posts.php?page=1');
}

//计算出展示页码 总共5页
 $begin = $page - 2;
 $end = $begin + 4;

 //判断临界点  大于0 小于最多纪录
  $begin = $begin < 1 ? 1 : $begin;
  $begin = $end - 4;

  $where = '1 = 1';

  //接收分类的分页处理
  $search = '';
  //传了参数就用选择语句  意思是没有all 并且存在分类，就选择分类  否则就不分类
   if (isset($_GET['categories']) && $_GET['categories'] !=='all' ){
     $where .= ' and categories.id='. $_GET['categories'];
     $search .='&categories=' . $_GET['categories']; //ID
  }
  
  //选择分类
  if (isset($_GET['status']) && $_GET['status'] !=='all' ){
    $where .= " and posts.status='{$_GET['status']}'";
    $search .= '&status=' . $_GET['status'];
  }



//获取分类总记录数跳转 array_valus();获取关联数组值
$total_count_p = baixiu_fetch_one("select count(1) as count
from posts
inner join categories on posts.category_id = categories.id
inner join users on posts.category_id = users.id
where {$where}
; ")["0"]['count'];


//记录行数

//测试
// $ss = baixiu_fetch_one("select count(1) as count
// from posts
// inner join categories on posts.category_id = categories.id
// inner join users on posts.category_id = users.id
// where {$where}
// ; ")["0"];


//记录总页数
$total_pages = ceil($total_count_p/$size); //分多少页
// var_dump($total_pages);

//声明显示的总页数
$visible = 5;
$begin = $page - ($visible-1)/2;
$end = $end > $total_pages ? $total_pages : $end;
$begin = $begin < 1 ? 1 : $begin;
$end = $begin + 4;
$end = $end > $total_pages ? $total_pages : $end;
if ($page > $total_pages){
  header('location:/admin/posts.php?page=' . $total_pages . $search);
}

php 查询代码 获取分类

//获取分类总记录数 array_valus();获取关联数组值
$total_count_p = baixiu_fetch_one("select count(1) as count
from posts
inner join categories on posts.category_id = categories.id
inner join users on posts.category_id = users.id
where {$where}
; ")["0"]['count'];

HTML 页面 渲染

            <option value="all">所有分类</option>
            <?php foreach($categories as $item):?>
            <option value="<?php echo $item['id']?>" <?php echo isset($_GET['categories']) && $_GET['categories'] === $item['id']? 'selected':'' ?>><?php echo $item['name']?></option>
            <?php endforeach?>
          </select>
          <select name="status" class="form-control input-sm">
            <option value="all" >所有状态</option>
            <option value="drafted" <?php echo isset($_GET['status']) && $_GET['status'] === 'drafted' ? 'selected' : ''; ?>>草稿</option>
            <option value="published"<?php echo isset($_GET['status']) && $_GET['status'] === 'published' ? 'selected' : ''; ?>>已发布</option>
            <option value="trashed" <?php echo isset($_GET['status']) && $_GET['status'] === 'trashed' ? 'selected' : ''; ?>>回收站</option>
          </select>
          <button class="btn btn-default btn-sm">筛选</button>
        </form>
        <ul class="pagination pagination-sm pull-right">
          <li><a href="?page=<?php echo $page - 1?>">上一页</a></li>
          <?php for ($i = $begin; $i <= $end ; $i++):?>
          <li <?php echo $i=== $page ? 'class = "active"': '';?>><a href="?page=<?php echo $i . $search;?>"><?php echo $i;?></a></li>
          <?php endfor?>
          

          <li><a href="?page=<?php echo $page + 1?>">下一页</a></li>
        </ul>

HTML 选择分类是自动分页

<option value="<?php echo $item['id']?>" <?php echo isset($_GET['categories']) && $_GET['categories'] === $item['id']? 'selected':'' ?>><?php echo $item['name']?></option>

源码

<?php
require_once '../function.php';
//获取用户登录信息
BAIXIU_get_current_user();


//实现分页功能
$page = empty($_GET['page'])? 1 : (int)$_GET['page'];
$size = 20;

//计算出越过多少条
$offset = ($page-1) * $size;

//限制-1值
if ($page < 1){
  header('location:/admin/posts.php?page=1');
}

//计算出展示页码 总共5页
 $begin = $page - 2;
 $end = $begin + 4;

 //判断临界点  大于0 小于最多纪录
  $begin = $begin < 1 ? 1 : $begin;
  $begin = $end - 4;

  $where = '1 = 1';

  //接收分类的分页处理
  $search = '';
  //传了参数就用选择语句  意思是没有all 并且存在分类，就选择分类  否则就不分类
   if (isset($_GET['categories']) && $_GET['categories'] !=='all' ){
     $where .= ' and categories.id='. $_GET['categories'];
     $search .='&categories=' . $_GET['categories']; //ID
  }
  
  //选择分类
  if (isset($_GET['status']) && $_GET['status'] !=='all' ){
    $where .= " and posts.status='{$_GET['status']}'";
    $search .= '&status=' . $_GET['status'];
  }



//获取分类总记录数跳转 array_valus();获取关联数组值
$total_count_p = baixiu_fetch_one("select count(1) as count
from posts
inner join categories on posts.category_id = categories.id
inner join users on posts.category_id = users.id
where {$where}
; ")["0"]['count'];


//记录行数

//测试
// $ss = baixiu_fetch_one("select count(1) as count
// from posts
// inner join categories on posts.category_id = categories.id
// inner join users on posts.category_id = users.id
// where {$where}
// ; ")["0"];


//记录总页数
$total_pages = ceil($total_count_p/$size); //分多少页
// var_dump($total_pages);


//声明显示的总页数
$visible = 5;
$begin = $page - ($visible-1)/2;
$end = $end > $total_pages ? $total_pages : $end;
$begin = $begin < 1 ? 1 : $begin;
$end = $begin + 4;
$end = $end > $total_pages ? $total_pages : $end;
if ($page > $total_pages){
  header('location:/admin/posts.php?page=' . $total_pages . $search);
}


// 接收筛选参数 如果没有筛选就默认为1




//获取全部数据
// $posts = baixiu_fetch_all("select * from posts inner join categories on posts.id = categories.id ");



//查询筛选分类数据
$categories = baixiu_fetch_all("select * from categories");

$posts = baixiu_fetch_all("select 
posts.id as posts_id,
posts.title as posts_title,
posts.created as posts_created,
categories.name as categories_name,
categories.slug as categories_slug,
posts.`status`as posts_status,
users.slug as users_slug,
users.nickname as users_nickname
from posts
inner join categories on posts.category_id= categories.id
inner join users on posts.category_id = users.id
where {$where}
order by posts.created desc
limit {$offset},{$size}

");
//




/** 转换时间格式
 * @param 参数 $created 
 * @return 
*/
function xiu_convert_date ($created) {
   $timestamp = strtotime($created);
   return date('Y年m月d日<b\r>H:i:s', $timestamp);
}

/** 转换时间格式 多种状态
 * @param 参数 $created 
 * @return 
*/
function xiu_convert_status ($status) {
     $dict =   array(
          'published' => '已发布',
          'drafted' => '草稿',
          'trashed' => '回收站'
        );
        //返回有值的数据
        return isset($dict[$status]) ? $dict[$status] : '未知';
}

//得到分类
// function get_categories ($category_id){
//  return baixiu_fetch_one("select name from categories where id = {$category_id}")['name'];
// }

// //得到作者
// function get_users ($users_id){
//   return baixiu_fetch_one("select nickname from users where id = {$users_id}")['nickname'];
//  }

 //一次性查询

?>
<!DOCTYPE php>
<php lang="zh-CN">
<head>
  <meta charset="utf-8">
  <title>Posts &laquo; Admin</title>
  <link rel="stylesheet" href="../assets/vendors/bootstrap/css/bootstrap.css">
  <link rel="stylesheet" href="../assets/vendors/font-awesome/css/font-awesome.css">
  <link rel="stylesheet" href="../assets/vendors/nprogress/nprogress.css">
  <link rel="stylesheet" href="../assets/css/admin.css">
  <script src="../assets/vendors/nprogress/nprogress.js"></script>
</head>
<body>
  <script>NProgress.start()</script>

  <div class="main">
  <?php include 'inc/nav.php';?>
    <div class="container-fluid">
      <div class="page-title">
        <h1>所有文章</h1>
        <a href="post-add.php" class="btn btn-primary btn-xs">写文章</a>
      </div>
      <!-- 有错误信息时展示 -->
      <!-- <div class="alert alert-danger">
        <strong>错误！</strong>发生XXX错误
      </div> -->
      <div class="page-action">
        <!-- show when multiple checked -->
        <a class="btn btn-danger btn-sm" href="javascript:;" style="display: none">批量删除</a>
        <form class="form-inline" action="<?php echo $_SERVER['PHP_SELF']; ?>">
          <select name="categories" class="form-control input-sm">
            <option value="all">所有分类</option>
            <?php foreach($categories as $item):?>
            <option value="<?php echo $item['id']?>" <?php echo isset($_GET['categories']) && $_GET['categories'] === $item['id']? 'selected':'' ?>><?php echo $item['name']?></option>
            <?php endforeach?>
          </select>
          <select name="status" class="form-control input-sm">
            <option value="all" >所有状态</option>
            <option value="drafted" <?php echo isset($_GET['status']) && $_GET['status'] === 'drafted' ? 'selected' : ''; ?>>草稿</option>
            <option value="published"<?php echo isset($_GET['status']) && $_GET['status'] === 'published' ? 'selected' : ''; ?>>已发布</option>
            <option value="trashed" <?php echo isset($_GET['status']) && $_GET['status'] === 'trashed' ? 'selected' : ''; ?>>回收站</option>
          </select>
          <button class="btn btn-default btn-sm">筛选</button>
        </form>
        <ul class="pagination pagination-sm pull-right">
          <li><a href="?page=<?php echo $page - 1?>">上一页</a></li>
          <?php for ($i = $begin; $i <= $end ; $i++):?>
          <li <?php echo $i=== $page ? 'class = "active"': '';?>><a href="?page=<?php echo $i . $search;?>"><?php echo $i;?></a></li>
          <?php endfor?>
          

          <li><a href="?page=<?php echo $page + 1?>">下一页</a></li>
        </ul>
      </div>
      <table class="table table-striped table-bordered table-hover">
        <thead>
          <tr>
            <th class="text-center" width="40"><input type="checkbox"></th>
            <th>标题</th>
            <th>作者</th>
            <th>分类</th>
            <th class="text-center">发表时间</th>
            <th class="text-center">状态</th>
            <th class="text-center" width="100">操作</th>
          </tr>
        </thead>
        <tbody>
        <?php foreach  ($posts as $item): ?>
        <tr>
            <td class="text-center"><input type="checkbox"></td>
            <td><?php echo $item['posts_title']?></td>
            <!-- <td><?php// echo get_users($item['user_id']);?></td> -->
            <!-- <td><?php //echo get_categories($item['category_id'])?></td> -->
            <td><?php echo $item['categories_name'];?></td>
            <td><?php echo $item['categories_slug']?></td>
            <td class="text-center"><?php echo xiu_convert_date($item['posts_created'])?></td>
            <td class="text-center"><?php echo xiu_convert_status($item['posts_status'])?></td>
            <td class="text-center">
              <a href="javascript:;" class="btn btn-default btn-xs">编辑</a>
              <a href="/admin/posts-delete.php?id=<?php echo $item['posts_id'];?>" class="btn btn-danger btn-xs">删除</a>
            </td>
          </tr>
        <?php endforeach?>
        


        </tbody>
      </table>
    </div>
  </div>
<?php $current_page = 'posts';?>
<?php include 'inc/aside.php';?>

  <script src="../assets/vendors/jquery/jquery.js"></script>
  <script src="../assets/vendors/bootstrap/js/bootstrap.js"></script>
  <script>NProgress.done()</script>

</body>
</php>

注意 需要载入function.php数据库查询源码
function.php

<?php

//载入配置爱文件
require_once 'config-2.php';
//封装公用函数

session_start();

//判断用户是否存在
function BAIXIU_get_current_user () {
    if (empty($_SESSION['current_login_user'])){
        //不存在跳转主页面
        header('Location:/admin/login-1.php');
        exit();
    }
    return $_SESSION['current_login_user'];
}


//通过一个数据库查询获取多条数据

function baixiu_fetch_all ($sql){
    $conn = mysqli_connect(BAXIU_DB_HOST, BAIXIU_DB_USER, BAIXIU_DB_PASS, BAIXIU_DB_NAME);
    if(!$conn){
        eixt('连接失败！');
    }

    //查询
   $query = mysqli_query($conn, $sql);
   if (!$query){
       //查询失败
       return false;
   }
   $result = array();

   //查询多条语句
   while ($row = mysqli_fetch_assoc($query)){
        $result[] = $row;
   }

   //释放结果
   mysqli_free_result($query);
   //断开连接
   mysqli_close($conn);

   return $result;
}

//获取单条数据

function baixiu_fetch_one ($sql) {
   $res[0] = baixiu_fetch_all ($sql);
   return isset($res[0]) ? $res[0] : null;
}

//获取添加

function baixiu_excute ($sql) {
    //建立连接
    $conn = mysqli_connect(BAXIU_DB_HOST, BAIXIU_DB_USER, BAIXIU_DB_PASS, BAIXIU_DB_NAME);

    if (!$conn) {
        exit("登录失败");
    }
    $query = mysqli_query($conn, $sql);
    if(!$query){
        //查询失败
        return false;
    }

    //对于增删改类型的操作都是获取受影响行数
      $affected_rows = mysqli_affected_rows($conn);
      return $affected_rows;

}


?>

配置文件config-2.php

<?php
//数据库连接配置文件

//本地主机
define('BAXIU_DB_HOST','localhost');

//主机用户名
define('BAIXIU_DB_USER','root');

//主机密码
define('BAIXIU_DB_PASS','123456');

//数据库名
define('BAIXIU_DB_NAME','baixiu-dev');

//设置默认网址根目录
define('DIR_ROOT',dirname(__FILE__));

?>