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

<?php
$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>';
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); 

$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 (?,?)");
$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链接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();

<form name="del_form" action="del.php" method="post">
<?php
$result=mysql_query("select * from news");
while($rs=mysql_fetch_array($result)){
 ?>
<input name="del_id[]" type="checkbox" id="del_id[]" value="<?=$rs[id]?>" /><?=$rs[title]?>
 <?php
}
 ?>
 </form>

2、处理页面（del.php）：

<?php
 if($del_id!=""){
 $del_num=count($del_id);
 for($i=0;$i<$del_num;$i++){
 mysql_query("Delete from news where id='$del_id[$i]'");
 }
 echo("<script type='text/javascript'>alert('删除成功！');history.back();</script>");
}else{
 echo("<script type='text/javascript'>alert('请先选择项目！');history.back();</script>");
}
?>

for($i=0;$i<$del_num;$i++){
 mysql_query("Delete from news where id='$del_id[$i]'");
}

$ids = implode(',',$_POST['del_id']);
$sql ="delete from aaaa where id in($ids)";
mysql_query($Sql);

<?php
 if(is_array($del_id)){
 
$ids = implode(',',$_POST['del_id']);
$sql ="delete from aaaa where id in($ids)";
 if( mysql_query($Sql))
{
echo '删除成功';
}
else
{
echo '删除失败';
}
}

 
}
?>

<?php
$redis = new Redis;
$redis->connect('127.0.0.1',6379,0);

//数据库
$host='********';
$user='root';
$psd='****';
$dbname='ask';

$link = mysql_connect($host,$user,$psd);
mysql_select_db($dbname,$link);
mysql_query("set names utf8");

$SQL = "SELECT * FROM question LIMIT 100";
$query = mysql_query($SQL);
$questionName = 'questionName';
while($info = mysql_fetch_array($query)){
$list[] = $info;
$redisName = 'Question_id_'.$info['id'];
$redis->hMset($redisName, $info);
$redis->zAdd($questionName,$info['id'],$info['id']);
$i++;
}
$range = $redis->zRevRange($questionName,0,10); //分页用到的主要函数
foreach($range as $qid){
$pageList[] = $redis->hGetAll('Question_id_'.$qid);
}
print_r($pageList);
?>

1.$conn = oci_connect('username','password',"(DEscriptION=(ADDRESS=(PROTOCOL =TCP)(HOST=192.168.1.100)(PORT = 1521))(CONNECT_DATA =(SID=test)))");

2.$conn = oci_connect('username','password','192.168.1.100/test');

3.Oracle 连接方法:

set adocon=Server.Createobject("adodb.connection")
adocon.open"Driver={microsoft odbc for oracle};server=oraclesever.world;uid=admin;pwd=pass;"

4.Oracle OLE DB 连接方法:

set adocon=Server.Createobject("adodb.connection")
adocon.open"Provider=OraOLEDB.Oracle;data source=dbname;user id=admin;password=pass;"

$sql = "select * from table_exmaple"
$ora_test = oci_parse($conn,$sql);  //编译sql语句
oci_execute($ora_test,OCI_DEFAULT);  //执行
while($r=oci_fetch_row($ora_test))  //取回结果
{
echo $ora_test[0];
echo "<BR>"; 
}

<?php

$conn = oci_connect('hr', 'hr', 'orcl'); // 建立连接

if (!$conn) {

$e = oci_error();

print htmlentities($e['message']);

exit;

}

$query = 'SELECT * FROM DEPARTMENTS'; // 查询语句

$stid = oci_parse($conn, $query); // 配置SQL语句，准备执行

if (!$stid) {

$e = oci_error($conn);

print htmlentities($e['message']);

exit;

}

$r = oci_execute($stid, OCI_DEFAULT); // 执行SQL。OCI_DEFAULT表示不要自动commit

if(!$r) {

$e = oci_error($stid);

echo htmlentities($e['message']);

exit;

}

// 打印执行结果

print '<table border="1">';

while($row = oci_fetch_array($stid, OCI_RETURN_NULLS)) {

print '<tr>';

foreach($row as $item) {

print '<td>'.($item?htmlentities($item):' ').'</td>';

}

print '</tr>';

}

print '</table>';

oci_close($conn);

?>