PHP操作MySQL数据库的常用方法

发布:smiling　来源: PHP粉丝网　添加日期：2014-09-10 09:50:32　浏览: 评论：0　

在php中操作mysql数据库的方法有常用的三种,我们使用最多的估计是mysql或mysqli当然还有一个pdo_mysql了,下面整理了一些例子给各位参考.

一.mysql数据库,代码如下:

$conn=mysql_connect("localhost", "root", "123456") or die("数据库连接失败");

mysql_select_db("test") or die("选择数据库失败");;

$sql="select * from user";

$data=mysql_query($sql);

echo '<table border="1" align="center" width="800">'

while($row=mysql_fetch_assoc($result)){

echo '<tr>';//开源代码phpfensi.com

foreach($row as $col){

echo '<td>'.$col.'</td>';

}

echo '</tr>';

};

echo '</table>';

mysql_close();

补充,代码如下:

// 从表中提取信息的sql语句

$strsql="SELECT * FROM `gbook`";

// 执行sql查询

$result=mysql_db_query($mysql_database, $strsql, $conn);

// 获取查询结果

$row=mysql_fetch_row($result);

echo '<font face="verdana">';

echo '<table border="1" cellpadding="1" cellspacing="2">';

// 显示字段名称

echo "</b><tr></b>";

for ($i=0; $i<mysql_num_fields($result); $i++)

{

  echo '<td bgcolor="#000F00"><b>'.

  mysql_field_name($result, $i);

  echo "</b></td></b>";

}

echo "</tr></b>";

// 定位到第一条记录

mysql_data_seek($result, 0);

// 循环取出记录

while ($row=mysql_fetch_row($result))

{

  echo "<tr></b>";

  for ($i=0; $i<mysql_num_fields($result); $i++ )

  {

echo '<td bgcolor="#00FF00">';

echo $row[$i];

echo '</td>';

  }

  echo "</tr></b>";

}



echo "</table></b>";

echo "</font>";

// 释放资源

mysql_free_result($result);

// 关闭连接

mysql_close($conn);

二.pdo_mysql(推荐),连接代码如下:

$dsn = "mysql:host=localhost;dbname=test";

$username = 'root';

$password = '123456';

$options = array(

PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',

);

$pdo = new PDO($dsn, $username, $password, $options);

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

增:

方法1:绑定关联数组

$str=$pdo->prepare("INSERT INTO `user` (`username`, `password`) VALUES (:username,:password)");

$str->execute(array(":username"=>"test", ":password"=>"passwd"));

方法2:绑定索引数组

$str=$pdo->prepare("INSERT INTO `user` (`username`, `password`) VALUES (?,?)");//开源代码phpfensi.com

$str->execute(array("test", "passwd"));

//删:

$str=$pdo->prepare("delete from user where id > 3");

$str->execute();

//改:

$str=$pdo->prepare("UPDATE `user` SET username=:username,password=:password where id=:id");

$str->execute(array(":username"=>"test", ":password"=>"passwd", ":id"=>"3"));

查:

//方法1: 单个取出,循环遍历,返回到数组

$str=$pdo->prepare("select * from user where id > :id order by id");

$str->execute(array(":id"=>2));

$str->setFetchMode(PDO::FETCH_ASSOC);

//共三种:1.PDO::FETCH_BOTH(默认) 2.FETCH_ASSOC 3.FTECH_NUM

while($data=$str->fetch()){

print_r($data);

echo '<br>';

}

//方法2: 全部取出,返回到二维数组

$str=$pdo->prepare("select * from user order by fid");

$str->execute();

$data=$str->fetchAll(PDO::FETCH_NUM);

print_r($data);

//方法3:单个取出,循环遍历,绑定字段名到变量

$str=$pdo->prepare("select fid,username,password from user order by id");

$str->execute();

$str->bindColumn("id",$id);

$str->bindColumn("username",$username);

$str->bindColumn(3,$password);

while($str->fetch()){

echo "$id | $username | $password <br>";

}

echo "总记录数：".$str->rowCount()."<br>";

echo "总字段数：".$str->columnCount()."<br>";

二.mysqli,代码如下:

用mysqli链接MYSQL数据库

requery_once("config.ini.php");

$mysqliObj = new mysqli($dbhost,$dbuser,$dbpwd,$dbname);

if(mysqli_connect_errno()){

echo "连接失败".mysqli_connect_error();

exit();

}

$mysqliObj->query("set name $charName");

其他操作:

//查询

//-----------------------------------------------------

//(单条查询)

$sql = "drop table  if exists user;";

$mysqliObj->query($sql);

//(多条查询)

$musqliObj->multip_query($sql)

//返回执行$sql受影响的行数（）

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

if($mysqliObj->query($sql))

echo $mysqliObj->affected_rows;

//insert 插入时，返回插入的id (很有用)

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

$num = $mysqliObj->insert_id;

<?php

$mysqli=new mysqli("localhost", "root", "123456", "test");

$sql="select * from user  order by id";

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

echo '<table align="center" border="1" width="800">';

while($row=$result->fetch_assoc()){

echo '<tr>';

foreach($row as $col){

echo '<td>'.$col.'</td>';

}

echo '</tr>';

}

echo '</table>';

$mysqli->close();

?>

三种处理查询结果,代码如下:

$sql = "select * from user";

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

//(1)fetch_row()   返回索引数组

fetch_row()

while(list($id,$name,$pwd)=$result->fetch_row()){

echo "id: ".$id." name:".$name."  pwd:".$pwd."<br>";

}

//(2)fetch_assoc()  返回关联数组

fetch_assoc()

while ($row = $result->fetch_assoc()){

echo "id:".$row["userId"]." name:".$row["userName"]."  pwd:".$row["password"]."<br>";

}

//（3）fetch_object()返回对象

while($row = $result->fetch_object()){

echo "id:".$row->userId." name:".$row->uerName."  pwd:".$row->password."<br>";

}