PHP 操作MySQL方法.docx
- 文档编号:4706030
- 上传时间:2022-12-07
- 格式:DOCX
- 页数:11
- 大小:17.91KB
PHP 操作MySQL方法.docx
《PHP 操作MySQL方法.docx》由会员分享,可在线阅读,更多相关《PHP 操作MySQL方法.docx(11页珍藏版)》请在冰豆网上搜索。
PHP操作MySQL方法
PHP 连接MySQL
PHP5及以上版本建议使用以下方式连接MySQL:
●MySQLiextension ("i"意为improved)
●PDO(PHPDataObjects)
在PHPz早期版本中使用MySQL扩展。
但该扩展在2012年开始不建议使用
MySQLi和PDO有它们自己的优势:
●PDO应用在12种不同数据库中,MySQLi只针对MySQL数据库。
所以,如果项目需要在多种数据库中切换,建议使用PDO,这样只需要修改连接字符串和部分查询语句即可。
使用MySQLi,如果不同数据库,你需要重新所有代码,包括查询。
●两者都是面向对象,但MySQLi还提供了API接口。
●两者都支持预处理语句。
预处理语句可以防止SQL注入,对于web项目的安全性是非常重要的。
一、连接服务器
1.实例(MySQLi-面向对象)
php
$servername="localhost";
$username="root";
$password="654321";
//创建连接
$conn=newmysqli($servername,$username,$password);
//检测连接
if($conn->connect_error) {
die("Connectionfailed:
".$conn->connect_error);
}
echo"Connectedsuccessfully";
?
>
2.实例(MySQLi-面向过程)
php
$servername="localhost";
$username="root";
$password="654321";
//创建连接
$conn=mysqli_connect($servername,$username,$password);
//检测连接
if(!
$conn) {
die("Connectionfailed:
".mysqli_connect_error());
}
echo"Connectedsuccessfully";
?
>
3.实例(PDO)
php
$servername="localhost";
$username="root";
$password="654321";
try{
$conn=newPDO("mysql:
host=$servername;dbname=flower",$username,$password);
echo"Connectedsuccessfully";
}
catch(PDOException$e)
{ echo$e->getMessage(); }
?
>
4.连接超时设置
二、关闭连接
连接在脚本执行完后会自动关闭。
你也可以使用以下代码来关闭连接:
1.实例(MySQLi-面向对象)
$conn->close();
2.实例(MySQLi-面向过程)
mysqli_close($conn);
3.实例(PDO)
$conn=null;
三、使用MySQLi和PDO创建MySQL数据库
1.实例(MySQLi-面向对象)
php
$servername="localhost";
$username="root";
$password="654321";
//创建连接
$conn=newmysqli($servername,$username,$password);
//检测连接
if($conn->connect_error) {
die("Connectionfailed:
".$conn->connect_error);
}
//Createdatabase
$sql="CREATEDATABASEmyTestDB";
if($conn->query($sql)===TRUE){
echo"Databasecreatedsuccessfully";
}else{
echo"Errorcreatingdatabase:
".$conn->error;
}
$conn->close();
?
>
2.实例(MySQLi-面向过程)
php
$servername="localhost";
$username="root";
$password="654321";
//创建连接
$conn=mysqli_connect($servername,$username,$password);
//检测连接
if(!
$conn) {
die("Connectionfailed:
".mysqli_connect_error());
}
//Createdatabase
$sql="CREATEDATABASEmyTestDB";
if(mysqli_query($conn,$sql)){
echo"Databasecreatedsuccessfully";
}else{
echo"Errorcreatingdatabase:
".mysqli_error($conn);
}
mysqli_close($conn);
?
>
3.实例(PDO)
php
$servername="localhost";
$username="root";
$password="654321";
try{
$conn=newPDO("mysql:
host=$servername;dbname=myTestDB",$username,$password);
//设置PDO错误模式为异常
$conn->setAttribute(PDO:
:
ATTR_ERRMODE,PDO:
:
ERRMODE_EXCEPTION);
$sql="CREATEDATABASEmyPDODB";
//使用exec(),因为没有结果返回
$conn->exec($sql);
echo"Databasecreatedsuccessfully
";
}
catch(PDOException$e)
{
echo$sql."
".$e->getMessage();
}
$conn=null;
?
>
四、使用MySQLi和PDO创建MySQL表
CREATETABLE语句用于创建MySQL表。
创建一个名为"member"的表
CREATETABLEmember(
emailchar(30)PRIMARYKEY,
passwordchar(30),
mnamechar(10),
sexchar
(2),
mobilechar(11),
addresschar(200),
jifenint,
yeDecimal(10,2)
)ENGINE=MyISAMDEFAULTCHARSET=utf8;
1.实例(MySQLi-面向对象)
php
$servername="localhost";
$username="root";
$password="654321";
$dbname="myTestDB";
//创建连接
$conn=newmysqli($servername,$username,$password,$dbname);
//检测连接
if($conn->connect_error) {
die("Connectionfailed:
".$conn->connect_error);
}
//sqltocreatetable
$sql="CREATETABLEmember(
emailchar(30)PRIMARYKEY,
passwordchar(30),
mnamechar(10),
sexchar
(2),
mobilechar(11),
addresschar(200),
jifenint,
yeDecimal(10,2)
)ENGINE=MyISAMDEFAULTCHARSET=utf8;
";
if($conn->query($sql)===TRUE){
echo"Tablemembercreatedsuccessfully";
}else{
echo"Errorcreatingtable:
".$conn->error;
}
$conn->close();
?
>
2.实例(MySQLi-面向过程)
php
$servername="localhost";
$username="root";
$password="654321";
$dbname="myTestDB";
//创建连接
$conn=mysqli_connect($servername,$username,$password,$dbname);
//检测连接
if(!
$conn) {
die("Connectionfailed:
".mysqli_connect_error());
}
//sqltocreatetable
$sql="CREATETABLEmember(
emailchar(30)PRIMARYKEY,
passwordchar(30),
mnamechar(10),
sexchar
(2),
mobilechar(11),
addresschar(200),
jifenint,
yeDecimal(10,2)
)ENGINE=MyISAMDEFAULTCHARSET=utf8;
";
if(mysqli_query($conn,$sql)){
echo"Tablemembercreatedsuccessfully";
}else{
echo"Errorcreatingtable:
".mysqli_error($conn);
}
mysqli_close($conn);
?
>
3.实例(PDO)
php
$servername="localhost";
$username="root";
$password="654321";
$dbname="myPDODB";
try{
$conn=newPDO("mysql:
host=$servername;dbname=$dbname",$username,$password);
//setthePDOerrormodetoexception
$conn->setAttribute(PDO:
:
ATTR_ERRMODE,PDO:
:
ERRMODE_EXCEPTION)
//sqltocreatetable
$sql="CREATETABLEmember(
emailchar(30)PRIMARYKEY,
passwordchar(30),
mnamechar(10),
sexchar
(2),
mobilechar(11),
addresschar(200),
jifenint,
yeDecimal(10,2)
)ENGINE=MyISAMDEFAULTCHARSET=utf8;
//useexec()becausenoresultsarereturned
$conn->exec($sql);
echo"Tablemembercreatedsuccessfully";
}
catch(PDOException$e)
{
echo$sql."
".$e->getMessage();
}
?
>
五、使用MySQLi和PDO向MySQL插入数据
1.实例(MySQLi-面向对象)
php
$servername="localhost";
$username="root";
$password="654321";
$dbname="myTestDB";
$conn=newmysqli($servername,$username,$password,$dbname);
if($conn->connect_error){
die("Connectionfailed:
".$conn->connect_error);
}
$sql="INSERTINTOmember(email,password,jifen,ye)
VALUES('a@','a',0,0.0)";
if($conn->query($sql)===TRUE){
echo"Newrecordcreatedsuccessfully";
}else{
echo"Error:
".$sql."
".$conn->error;
}
$conn->close();
?
>
2.实例(MySQLi-面向过程)
php
$servername="localhost";
$username="root";
$password="654321";
$dbname="myTestDB";
//创建连接
$conn=mysqli_connect($servername,$username,$password,$dbname);
//检测连接
if(!
$conn){
die("Connectionfailed:
".mysqli_connect_error());
}
$sql="INSERTINTOmember(email,password,jifen,ye)
VALUES('b@','b',0,0.0)";
if(mysqli_query($conn,$sql)){
echo"Newrecordcreatedsuccessfully";
}else{
echo"Error:
".$sql."
".mysqli_error($conn);
}
mysqli_close($conn);
?
>
3.实例(PDO)
php
$servername="localhost";
$username="root";
$password="654321";
$dbname="myDBPDO";
try{
$conn=newPDO("mysql:
host=$servername;dbname=$dbname",$username,$password);
$conn->setAttribute(PDO:
:
ATTR_ERRMODE,PDO:
:
ERRMODE_EXCEPTION);
$sql="INSERTINTOmember(email,password,jifen,ye)
VALUES('a@','a',0,0.0)";
$conn->exec($sql);
echo"Newrecordcreatedsuccessfully";
}
catch(PDOException$e){
echo$sql."
".$e->getMessage();
}
$conn=null;
?
>
六、使用MySQLi和PDO向MySQL插入多条数据
1.实例(MySQLi-面向对象)
php
$servername="localhost";
$username="root";
$password="654321";
$dbname="myTestDB";
$conn=newmysqli($servername,$username,$password,$dbname);
if($conn->connect_error){
die("Connectionfailed:
".$conn->connect_error);
}
$sql="INSERTINTOmember(email,password,jifen,ye)
VALUES('c@','c',0,0.0);";
$sql.="INSERTINTOmember(email,password,jifen,ye)
VALUES('d@','d',0,0.0);";
$sql.="INSERTINTOmember(email,password,jifen,ye)
VALUES('e@','e',0,0.0);";
if($conn->multi_query($sql)===TRUE){
echo"Newrecordscreatedsuccessfully";
}else{
echo"Error:
".$sql."
".$conn->error;
}
$conn->close();
?
>
2.实例(MySQLi-面向过程)
php
$servername="localhost";
$username="root";
$password="654321";
$dbname="myTestDB";
$conn=mysqli_connect($servername,$username,$password,$dbname);
if(!
$conn){
die("Connectionfailed:
".mysqli_connect_error());
}
$sql="INSERTINTOmember(email,password,jifen,ye)
VALUES('aa@','aa',0,0.0);";
$sql.="INSERTINTOmember(email,password,jifen,ye)
VALUES('bb@','bb',0,0.0);";
$sql.="INSERTINTOmember(email,password,jifen,ye)
VALUES('cc@','cc',0,0.0);";
if(mysqli_multi_query($conn,$sql)){
echo"Newrecordscreatedsuccessfully";
}else{
echo"Error:
".$sql."
".mysqli_error($conn);
}
mysqli_close($conn);
?
>
3.实例(PDO)
php
$servername="localhost";
$username="username";
$password="password";
$dbname="myDBPDO";
try{
$conn=newPDO("mysql:
host=$servername;dbname=$dbname",$username,$password);
//setthePDOerrormodetoexception
$conn->setAttribute(PDO:
:
ATTR_ERRMODE,PDO:
:
ERRMODE_EXCEPTION);
//开始事务
$conn->beginTransaction();
//SQL语句
$conn->exec("INSERTINTOmember(email,password,jifen,ye)
VALUES('b@','b',0,0.0)");
$conn->exec("INSERTINTOmember(email,password,jifen,ye)
VALUES('c@','c',0,0.0)");
$conn->exec("INSERTINTOmember(email,password,jifen,ye)
VALUES('d@','d',0,0.0)");
//committhetransaction
$conn->commit();
echo"Newrecordscreatedsuccessfully";
}
catch(PDOException$e)
{
//rollbackthetransactionifsomethingfailed
$conn->rollback();
echo$sql."
".$e->getMessage();
}
$conn=null;
?
>
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- PHP 操作MySQL方法 操作 MySQL 方法
![提示](https://static.bdocx.com/images/bang_tan.gif)