PHP操作MySQL数据库的常用方法
在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测试数据 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测试数据 $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>" ; }查看更多关于PHP操作MySQL数据库的常用方法 - php高级应用的详细内容...
声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://www.haodehen.cn/did30522