php,mysqli连接数据库增删改查，自测

header("Content-type: text/html; charset=utf-8");

class NewsController extends Controller

{

    public $pageTitle = '新闻公告';

    protected $hostDb = '127.0.0.1';

    protected $userDb = 'qqq';

    protected $pwdDb = 'qqqq';

    protected $databaseDb = 'qqqq';

    protected $showNum = '10';

    /*数据库连接*/

    protected function linkDb()

{

        $conn = new mysqli($this->hostDb, $this->userDb, $this->pwdDb, $this->databaseDb);

        $conn->set_charset('utf8');

        if ($conn->connect_errno) {

            die("could not connect to the database:\n" . $conn->connect_error);//诊断连接错误

        }

        return $conn;

}

    /*新闻公告页面*/

    public function actionView()

{

        $uid = Yii::app()->user->id;

        $newsList = $this->selectAllList();

        $this->render('view', array(

            'newsList' => $newsList['data'],

            'userid' => $uid,

            'total_page' => ceil($newsList['total'] / $this->showNum)

));

}

    /*查询新闻列表*/

    protected function selectAllList()

{

        $db = $this->linkDb();

        $count = "SELECT COUNT(*) as total FROM mc_news";

        $resultCount = $db->query($count);

        $total = $resultCount->fetch_all(MYSQLI_ASSOC);

        $page = isset($_GET['page']) ? $_GET['page'] : '1';

        $start = ($page - 1) * $this->showNum;

        $sql = "select `id`,`title`,`desc`,`create_time`,`up_time`,`uid_read` from mc_news ORDER BY create_time desc ";

        $sql .= " LIMIT " . $start . "," . (int)$this->showNum;

        $result = $db->query($sql);

        $rows = $result->fetch_all(MYSQLI_ASSOC);

        $arr = array(

            'total' => $total[0]['total'], 'data' => $rows

        );

        return $arr;

}

    /*新闻详情*/

    public function actionGetNewsInfo()

{

        $uid = Yii::app()->user->id;

        $nws_id = $_REQUEST['id'];

        $db = $this->linkDb();

        $sql = "select `title`,`desc`,`up_time`,`uid_read` from mc_news where id=" . $nws_id . " ";//查询

        $result = $db->query($sql);

        $row = $result->fetch_assoc();

        $row['up_time'] = date("Y-m-d", strtotime($row['up_time']));

        if ($uid != 1) {

            $uid_read_arr = explode(',',$row['uid_read']);

            if(!in_array($uid,$uid_read_arr)){

                $uid_read = $row['uid_read'].','.$uid;

                if(empty($row['uid_read'])){

                    $uid_read =$uid;

}

                $sql = "UPDATE mc_news SET uid_read='" .$uid_read . "' WHERE id='" . $nws_id . "' ";

                $db->query($sql);

}

}

        exit(CJSON::encode($row));

}

    /*新闻编辑*/

    public function actionEditNews()

{

        $db = $this->linkDb();

        $sql = "UPDATE mc_news SET title='" . $_REQUEST['title'] . "',`desc`='" . $_REQUEST['desc'] . "' WHERE id='" . $_REQUEST['id'] . "' ";

        $result = $db->query($sql);

        if ($result) {

            $code = 1;

            $msg = 'success';

        } else {

            $code = 0;

            $msg = 'error';

}

        $json = array('code' => $code, 'msg' => $msg);

        exit(CJSON::encode($json));

}

    /*新闻添加*/

    public function actionAddNews()

{

        $nws_title = $_REQUEST['title'];

        $nws_desc = $_REQUEST['desc'];

        $db = $this->linkDb();

        $sql = "INSERT INTO mc_news SET `title`='" . $nws_title . "',`desc`='" . $nws_desc . "',`create_time`='" . time() . "'  ";

        $result = $db->query($sql);

        if ($result) {

            $code = 1;

            $msg = 'success';

        } else {

            $code = 0;

            $msg = 'error';

}

        $json = array('code' => $code, 'msg' => $msg);

        exit(CJSON::encode($json));

}

    /*删除*/

    public function actionDeleteNews()

{

        $nws_id = $_REQUEST['id'];

        $db = $this->linkDb();

        $sql = "DELETE from mc_news where id=" . $nws_id . " ";

        $result = $db->query($sql);

        if ($result) {

            $code = 1;

            $msg = 'success';

        } else {

            $code = 0;

            $msg = 'error';

}

        $json = array('code' => $code, 'msg' => $msg);

        exit(CJSON::encode($json));

}

    /*检查未读条数*/

    public function actionGetNumber()

{

        $uid = Yii::app()->user->id;

        $sql = "SELECT uid_read FROM mc_news";

        $result = $this->linkDb()->query($sql);

        $rows = $result->fetch_all(MYSQLI_ASSOC);

        foreach ($rows as $res){

            $uid_read_arr = explode(',',$res['uid_read']);

            if(!in_array($uid,$uid_read_arr)){

              $data['uid_read'][] = array(

                'read'=> $res['uid_read']

);

}

}

        $count = isset($data['uid_read'])?count( $data['uid_read']):'0';

        if($uid==1)$count=0;

        $json = array('code' => 1, 'count' =>$count);

        exit(CJSON::encode($json));

}

}