PHP操作MySQL數據庫的常用方法

2024-05-04 21:49:04

字體：大中小

來源：轉載

供稿：網友

在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>';//開源代碼Vevb.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 (?,?)");//開源代碼Vevb.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>";

}