数据库添加、修改、删除基础操作代码

时间：2015-01-23 来源：西部数据作者：西部数据

这是一款比较适合php初学者学的教程,我们利用一个简单的实例来对数据库添加、修改、删除,这样更系统的让各位知道php mysql数据库操作的要点.

主程序实例代码如下:

<?php

  require_once('common.php');

  $action = $_get['action'];

?>

<!doctype html public "-//w3c//dtd xhtml 1.0 transitional//en" "http://www.w3.org/tr/xhtml1/dtd/xhtml1-transitional.dtd">

<html xmlns="http://www.phpfensi.com/1999/xhtml">

<head>

<meta http-equiv="content-type" content="text/html; charset=gb2312" />

<title>人才列表</title>

<link href="style.css" type="text/css" rel="stylesheet" />

</head>

<body>

<div id="wrap">

<div id="main">

<?php

      if($action=='add'){

?>

    <form action="?action=save" method="post" name="form1">

  <table width="300" border="0" cellspacing="0" cellpadding="0"  class="post">

          <tr>

            <td colspan="2">添加人员</td></tr>

          <tr><td width="78">登陆账号</td>

            <td width="220"><input name="login" type="text" id="login" /></td>

          </tr>

          <tr>

            <td>登陆密码</td>

            <td><input name="pws" type="text" id="pws" /></td>

          </tr>

          <tr>

            <td>问题</td>

            <td><input name="question" type="text" id="question" /></td>

          </tr>

           <tr>

            <td>答案</td>

            <td><input name="answer" type="text" id="answer" /></td>

          </tr>

            <tr>

              <td colspan="2"><input name="button" type="submit" id="button" value=" 添加 " /></td>

            </tr>



      </table>

    </form>

<?php

      }

      elseif($action=='save'){

    $login = isset($_post['login']) ? $_post['login'] : '';

    $pws = isset($_post['pws']) ? $_post['pws'] : '';

    $question = isset($_post['question']) ? $_post['question'] : '';

    $answer = isset($_post['answer']) ? $_post['answer'] : '';

    $sql = "insert into person (login,pws,question,answer)

    values('$login','$pws','$question','$answer')";

    $db->query($sql);

    forward('发布成功','href','personlist.php');

          }

    elseif($action=='del'){

    $person_id=$_get['person_id'];

    $page=$_get['page'];

    $sql="delete from person where person_id='$person_id'";

    $db->query($sql);

    forward('删除成功','href','personlist.php?page='.$page);

          }

    elseif($action=='editsave'){

    $person_id = isset($_post['person_id']) ? $_post['person_id'] : '';

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

          $login = isset($_post['login']) ? $_post['login'] : '';

    $pws = isset($_post['pws']) ? $_post['pws'] : '';

    $question = isset($_post['question']) ? $_post['question'] : '';

    $answer = isset($_post['answer']) ? $_post['answer'] : '';

    $sql="update person set login='$login',pws='$pws',question='$question',answer='$answer' where person_id='$person_id'";

    $db->query($sql);

          forward('修改成功','href','personlist.php?page='.$page);

          }

   elseif($action=='edit'){

    $person_id=$_get['person_id'];

    $page=$_get['page'];

    $sql="select * from person where person_id='$person_id'";

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

    $row = $db->fetch_array($query);

    $login=$row['login'];

    $pws=$row['pws'];

    $question=$row['question'];

    $answer=$row['answer'];

       ?>

    <form action="?action=editsave" method="post" name="form1">

  <table width="300" border="0" cellspacing="0" cellpadding="0"  class="post">

          <tr>

          <input name="page" type="hidden"  value="<?php echo $page?>"/>

          <input name="person_id" type="hidden"  value="<?php echo $person_id?>"/>

            <td colspan="2">修改人员</td></tr>

          <tr><td width="78">登陆账号</td>

            <td width="220"><input name="login" type="text" id="login"  value="<?php echo $login?>"/></td>

          </tr>

          <tr>

            <td>登陆密码</td>

            <td><input name="pws" type="text" id="pws" value="<?php echo $pws?>"/></td>

          </tr>

          <tr>

            <td>问题</td>

            <td><input name="question" type="text" id="question" value="<?php echo $question?>"/></td>

          </tr>

           <tr>

            <td>答案</td>

            <td><input name="answer" type="text" id="answer" value="<?php echo $answer?>"/></td>

          </tr>

            <tr>

              <td colspan="2"><input name="button" type="submit" id="button" value=" 修改 " /></td>

            </tr>



      </table>

    </form>

<?php

          }

      else{

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

          $num = 5;

          $sql="select * from person";

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

          $totalnum = $db->num_rows($query);//记录总数

          $pagenum = ceil($totalnum/$num); //总页数

          $offset = ($page-1) * $num;

          $sql=$sql." limit $offset,$num ";

          $query = $db->query($sql);//取得记录

        ?>

          <table width="639" border="0" cellspacing="0" cellpadding="0"  class="post">

          <tr>

            <td colspan="6">记录总数：<?php echo $totalnum;?>————<a href="personlist.php?action=add">添加人员</a></td></tr>

          <tr><td width="126">登陆账号</td>

          <td width="98">登陆密码</td>

          <td width="115">问题</td>

          <td width="66">答案</td>

          <td width="138">加入时间</td>

          <td width="94">操作</td>

          </tr>

  <?php

        while ($row = $db->fetch_array($query)) {

        ?>

          <tr>

          <td><?php echo $row['login'];?></td>

            <td><?php echo $row['pws'];?></td>

            <td><?php echo $row['question'];?></td>

            <td><?php echo $row['answer'];?></td>

            <td><?php echo $row['addtime'];?></td>

            <td><a href="?action=del&person_id=<?php echo $row['person_id'] ?>&page=<?php echo $page ?>">删除</a>/

            <a href="?action=edit&person_id=<?php echo $row['person_id'] ?>&page=<?php echo $page ?>">修改</a></td>

          </tr>

       <?php

          }

        ?>

    </table>

   </div>

       <div id="pages_btns">

            <div class="pages"><?php showpage($page, $num, $pagenum, $totalnum)?></div>

   </div>

   <?php

   }

   ?>

</div>

</body>

</html>

config.php,代码如下:

<?php

$host = 'localhost';

$user = 'root';

$pass = '123456';

$db = 'rc';

?>

common.php,代码如下:

<?php

  require_once('config.php');

  require_once('mysql.php');

  require_once('function.php');



  $db = new mysql($host, $user, $pass, $db);

?>

function.php,代码如下:

<?php

/**

* 分页函数显示

* date : 2008-12-6

*

* @param $page 当前页数

* @param $num 每页显示数

* @param $pagenum 分页总数

* @param $totalnum 记录总数

* 程序调用：showpage(当前页数,每页显示数,分页总数,记录总数);

*/

function showpage($page, $num, $pagenum, $totalnum) {

        $maxto = 5; //每次显示页数

        $nextpage = $page + 1;

        if ($nextpage > $pagenum) $nextpage = $pagenum;

        $for_end = ($pagenum > ($page + $maxto)) ? ($page +$maxto) : $pagenum;

        $for_begin = (($page - $maxto)>1) ? ($page - $maxto) : 1;

        echo "<em> total: $totalnum </em>";

        for ($i = $for_begin; $i <= $for_end; $i++) {

         if ($i != $page){

          echo "<a href="?page=$i">$i</a> ";

         } else {

          echo "<strong>$i</strong>";

         }

        }

        echo "<a href="?page=$nextpage" class="next">&rsaquo;&rsaquo;</a><a href="?page=$pagenum" class="last">... $pagenum</a>";

        echo "<kbd><input type="text" name="custompage" size=3 onkeydown="if(event.keycode==13) {window.location='list.php?page='+this.value; return false;}" /></kbd>";

}

/*网页特效提示框*/

function forward($msg, $methd='', $url = ''){

     $sstr = "<script language='网页特效' type='text/网页特效'> ";

     if($methd == 'href' && $url == '') die('forward funciton is wrong！');

     $sstr .= "    alert('$msg!'); ";

     switch ($methd){

        case "href":

           $sstr .= " location.href='".$url."'; ";

           break;

        case "close":

           $sstr .= " self.close(); ";

           break;

        default:

           $sstr .= " history.go(-1); ";

     }

     $sstr .= "</script>";

     die($sstr);

}

?>

mysql.php数据库连接类,代码如下:

<?php

/**

* ###################################################

*

* 数据库操作类

* ###################################################

*/

class mysql {

var $user,$pass,$host,$db;

var $id,$data,$fields,$row,$row_num,$insertid,$version,$query_num=0;

function mysql($host,$user,$pass,$db)

{

  $this->host = $host;

  $this->pass = $pass;

  $this->user = $user;

  $this->db = $db;

  $this->dbconnect($this->host, $this->user, $this->pass);

  $this->selectdb($this->db);

  if($this->version() >'4.1')

  mysql_query("set names 'gbk'");

}//初始化对象

function dbconnect($host,$user,$pass)

{

  $this->id = @ mysql_connect($host,$user,$pass) or

  sysmsg("连接数据库失败,可能是mysql数据库用户名或密码错误");

}

function selectdb($db)

{

  @ mysql_select_db($db,$this->id) or sysmsg("未找到指定数据库");

}

function query($sql)

{

  $query = @ mysql_query($sql,$this->id) or sysmsg("sql语句执行错误：$sql <br />".$this->geterror());

  $this->query_num();

  return $query;

}

function fetch_array($query)

{

  $this->data = @mysql_fetch_array($query);

  return $this->data;

}

function query_num()

{

  $this->query_num++;

}

function num_fields($query)

{

  $this->fields = @mysql_num_fields($query);

  return $this->fields;

}

function fetch_row($query)

{

  $this->row = @mysql_fetch_row($query);

  return $this->row;

}

function num_rows($query)

{

  $this->row_num = @mysql_num_rows($query);

  return $this->row_num;

}

function insert_id()

{

  $this->insertid = mysql_insert_id();

  return $this->insertid;

}

function version()

{

  $this->version = mysql_get_server_info();

  return $this->version;

}

function fetch_one_array($sql)

{//开源代码phpfensi.com

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

  $this->data = $this->fetch_array($query);

  return $this->data;

}

function geterror()

{

  return mysql_error();

}

}

?>

sql数据库,代码如下:

-- phpmyadmin sql dump

-- version 3.1.2-rc1

-- http://www.phpmyadmin.net

--

-- 主机: localhost

-- 生成日期: 2009 年 04 月 15 日 09:22

-- 服务器版本: 5.0.67

-- php 版本: 5.2.6

set sql_mode="no_auto_value_on_zero";

/*!40101 set @old_character_set_client=@@character_set_client */;

/*!40101 set @old_character_set_results=@@character_set_results */;

/*!40101 set @old_collation_connection=@@collation_connection */;

/*!40101 set names utf8 */;

--

-- 数据库: `rc`

--

-- --------------------------------------------------------

--

-- 表的结构 `person`

--

create table if not exists `person` (

  `person_id` int(11) unsigned not null auto_increment,

  `login` varchar(25) not null,

  `pws` varchar(25) not null,

  `question` varchar(25) not null,

  `answer` varchar(25) not null,

  `addtime` timestamp not null default current_timestamp,

  primary key  (`person_id`)

) engine=innodb  default charset=gbk comment='人员表' auto_increment=13 ;

--

-- 导出表中的数据 `person`

--

insert into `person` (`person_id`, `login`, `pws`, `question`, `answer`, `addtime`) values

(1, 'huangxulei', '123456', 'whoareyou', 'iami', '2009-04-15 10:06:42'),

(3, 'lihuang', '123456', '123', 'www.111cn.net', '2009-04-15 10:47:26'),

(4, '2222', '333', '3333', '33333', '2009-04-15 14:23:50'),

(8, '张红', '123456', 'whoareyou', '000', '2009-04-15 16:18:09'),

(9, '111', '111', '111', '111', '2009-04-15 16:26:07'),

(10, '111', '111', '111', '111', '2009-04-15 16:26:07'),

(11, '5555555555', '00000', '11100', '33333333', '2009-04-15 16:26:07');