IT技术学习网
IT技术学习网
您的位置: 主页 > 编程技术 > php >

php 查询插入更新删除mysql数据库实例

2014-11-08 09:44 来源: IT技术学习网原创 阅读:
本文通过php操作mysql实例,向大家演示了php对mysql数据库的最简单的,连接,查询,插入,更新,删除操作。
建立mysql数据库的sql
CREATE TABLE user(  id int(5) DEFAULT '0' NOT NULL AUTO_INCREMENT,  name varchar(20),  city varchar(20),  postcode varchar(10),address varchar(100),  password varchar(10), level tinyint(2) DEFAULT '0',right tinyint(2),email varchar(40), PRIMARY KEY (id),  UNIQUE id (id));
INSERT INTO myuser VALUES (1,'王友','北京','100083','北京市海淀区学院路',111,0,0,'wangyou000@263.net');
INSERT INTO myuser VALUES (2,'金风','武汉','430071','武汉市武昌区水果湖',111,0,0,'jinfeng000@263.net');
INSERT INTO myuser VALUES (3,'常丽','襄樊','441021','襄樊市襄城区南街',111,0,0,'changli000@263.net');
1.查询全部
<html>                                                              
                                                                    
 <head>                                                             
  <title>查询全部</title>                                       
 </head>                                                            
                                                                    
<body>                                                              
                                                                    
  <h2 align=center>                                                 
  下面是用户数据库的所有内容:                                      
  </h2>                                                             
                                                                    
<?                                                                  
   $db = MySQL_connect("localhost", "root");                        
   MySQL_select_db("mysite",$db);                                   
   $result = MySQL_query("SELECT * FROM user",$db);               
                                                                    
   echo "<table border=1>/n";                                       
   echo "<tr><td>id</td><td>姓名</td>";                             
   echo "<td>城市</td><td>邮编</td>";                               
   echo "<td>住    址</td><td>等级</td>";                           
   echo "<td>权限</td><td>邮件地址</td>";                           
   echo "</tr>/n";                                                  
                                                                    
while ($myrow = MySQL_fetch_row($result))                           
   {                                                                
      printf("<tr><td>%s</td><td>%s</td>", $myrow[0], $myrow[1]);
     printf("<td>%s</td><td>%s</td>", $myrow[2], $myrow[3]);        
     printf("<td>%s</td><td>%s</td>", $myrow[4], $myrow[6]);        
     printf("<td>%s</td><td>%s</td></tr>/n", $myrow[7], $myrow[8]);
   }                                                                
echo "</table>/n";                                                  
?>                                                                  
                                                                    
</body>                                                             
</html>                      
2.根据id查询
<html>                                                            
                                                                  
<body>                                                            
                                                                  
  <title>                                                         
     根据id查询                                               
  </title>                                                        
                                                                  
<?                                                                
$db = MySQL_connect("localhost", "root");                         
MySQL_select_db("mysite",$db);                                    
                                                                  
if ($id) {                                                        
   $result = MySQL_query("SELECT * FROM user WHERE id=$id",$db);
   $myrow = MySQL_fetch_array($result);                           
                                                                  
   printf("<h2 align=left>用户的情况</h2>");                      
   printf("1.ID号 %s/n<br>", $myrow["id"]);                       
   printf("2.姓名: %s/n<br>", $myrow["name"]);                    
   printf("3.城市: %s/n<br>", $myrow["city"]);                    
   printf("4.邮编: %s/n<br>", $myrow["postcode"]);                
   printf("5.住址: %s/n<br>", $myrow["address"]);                 
   printf("6.等级:%s/n<br>", $myrow["level"]);                   
   printf("7.邮件地址: %s/n<br>", $myrow["email"]);               
   printf("<a href=/"%s/"> 继续查询 </a>", $PATH_INFO);           
                                                                  
}                                                                 
else                                                              
  {                                                               
                                                                  
   $result = MySQL_query("SELECT * FROM user",$db);             
    if ($myrow = MySQL_fetch_array($result))                      
    {                                                             
      printf("<h2 align=left>请选择要查询的用户:</h2>");         
      do {                                                        
        printf("%s<a href=/"%s?id=%s/">%s </a><br>/n",            
        $myrow["id"],$PATH_INFO,$myrow["id"], $myrow["name"]);    
          }                                                       
      while ($myrow = MySQL_fetch_array($result));                
    }                                                             
    else                                                          
    {                                                             
      echo "对不起,没有数据!";                               
    }                                                             
  }                                                               
?>                                                                
                                                                  
</body>                                                           
                                                                  
</html>                                                          
3.更新
<html>                                                           
                                                                 
<body>                                                           
                                                                 
  <title>                                                        
     更新                                               
  </title>                                                       
                                                                 
                                                                 
<?                                                               
//PHP程序的开始                                                  
$db = MySQL_connect("localhost", "root");                        
MySQL_select_db("mysite",$db);                                   
                                                                 
if (!$id)                                                        
  {                                                              
                                                                 
   $result = MySQL_query("SELECT * FROM user",$db);            
    if ($myrow = MySQL_fetch_array($result))                     
    {                                                            
      printf("<h2 align=left>请选择要修改的用户名字:</h2>");    
      do {                                                       
        printf("%s<a href=/"%s?id=%s/">%s </a><br>/n",           
        $myrow["id"],$PATH_INFO,$myrow["id"], $myrow["name"]);   
          }                                                      
      while ($myrow = MySQL_fetch_array($result));               
    }                                                            
    else                                                         
    {                                                            
      echo "对不起!数据库中没有记录";                      
    }                                                            
  }                                                              
else                                                             
{                                                                
  if ($submit)                                                   
   {                                                             
    $sql = "UPDATE user SET name='$name',city='$city',         
    address='$address',email='$email' WHERE id=$id";             
    $result = MySQL_query($sql);                                 
    echo "谢谢!数据更改完成/n";                                 
   }                                                             
  else                                                           
   {                                                             
    $sql = "SELECT * FROM user WHERE id=$id";                  
    $result = MySQL_query($sql);                              
    $myrow = MySQL_fetch_array($result);                         
//以下不是PHP的部分                                              
?>                                                               
    <h2 align=left>请对用户的信息进行修改:</h2>                 
    <form method="post" action="<? echo $PATH_INFO ?>">          
    <input  type=hidden name="id"                                
    value="<?php echo $myrow["id"] ?>">                          
    姓名:<input type="Text" name="first" value="<?php   
   echo $myrow["name"] ?>"><br>                                  
    城市:<input type="Text" name="city" value="<?php echo       
    $myrow["city"] ?>"><br>                                      
    住址:<input type="Text" name="address" value="<?php echo    
    $myrow["address"] ?>"><br>                                   
     电子邮件:<input type="Text" name="email" value="<?php echo
    $myrow["email"] ?>"><br>                                     
     <input type="Submit" name="submit" value="修改完毕">        
    </form>                                                      
<?                                                               
//PHP 的结束                                                     
65:}                                                        
}                                                                
?>                                                               
</body>                                                          
</html>                                                  
4.增加删除记录
<html>                                                           
                                                                     
<body>                                                               
<title>                                                              
   增加删除记录                                                    
</title>                                                             
                                                                     
<?                                                                   
$db = MySQL_connect("localhost", "root");                            
MySQL_select_db("mysite",$db);                                       
                                                                     
if ($submit)                                                         
   {                                                                 
    $sql = "INSERT INTO myuser (name,city,address,email)             
    VALUES ('$name','$city','$address','$email')";                   
    $result = MySQL_query($sql);                                     
     echo "记录添加成功!<p>";                                       
   }                                                                 
else                                                                 
   if ($delete)                                                      
     {                                                               
      // 删除一条记录                                                
      $sql = "DELETE FROM myuser WHERE id=$id";                  
      $result = MySQL_query($sql);                                   
      echo "记录删除成功!<p>";                                      
     }                                                               
   else                                                              
     {                                                               
    printf("<h2 align=left>删除一个用户的所有信息:</h2>");          
    $result = MySQL_query("SELECT * FROM myuser",$db);               
    while ($myrow = MySQL_fetch_array($result))                      
       {                                                             
          printf("%s:<a href=/"%s?id=%s&delete=yes/">%s</a> <br>/n",
          $myrow["id"],$PATH_INFO, $myrow["id"], $myrow["name"]);    
       }                                                             
  ?>                                                                 
                                                                     
  <P>                                                                
                                                                     
  <h2 align=left>                                                    
      添加一个新的用户:                                             
  </h2>                                                              
                                                                     
  <form method="post" action="<?php echo $PATH_INFO?>">              
 姓名:<input type="Text" name="name" value="<?php                
   echo $myrow["name"] ?>"><br>                                      
    城市:<input type="Text" name="city" value="<?php echo           
    $myrow["city"] ?>"><br>                                          
    住址:<input type="Text" name="address" value="<?php echo        
    $myrow["address"] ?>"><br>                                       
     电子邮件:<input type="Text" name="email" value="<?php echo     
    $myrow["email"] ?>"><br>                                         
    <input type="Submit" name="submit" value="添加用户">    
    </form>                                                          
                                                                     
<?php                                                                
}                                                                    
?>                                                                   
                                                                     
</body>                                                              
                                                                     
</html>                              

php 查询插入更新删除mysql数据库实例
转载需注明出处:IT技术学习网 http://www.itjsxx.com/php/php_select_update_delete_mysql.html

评论文章 php 查询插入更新删除mysql数据库实例
评论列表(网友评论仅供网友表达个人看法,并不表明本站同意其观点或证实其描述)
IT技术学习网 - 关于我们 - 联系我们 - 版权声明 - 网站地图