<?php $dbms = 'mysql'; $dbname = 'test'; $user = 'root'; $pwd = '123456'; $host = 'localhost'; $dsn = "$dbms:host=$host;dbName=$dbname"; try{ $pdo = new PDO($dsn,$user,$pwd); echo 'pdo Successfully connected to database'; }catch(Exception $e){ echo $e->getMessage().'<br>'; }
The output result of the page is as follows:
Because the database server only listens for connection requests on specific ports. Each database server has a default port number (MySQL is 3306), but the database administrator can modify the port number, so it is possible that PHP cannot find the port number of the database, and then it can include the port number in the DSN. For example:
$dsn="mysql:host=127.0.0.1;port=3306;dbname=admin";
Detailed explanation of fetch() method for getting result set in PDO
First, create a php file, connect to the MySQL database through PDO, then define the SELECT query statement, apply the prepare() and execute() methods to perform the query operation, then return the next row of data in the result set through the fetch() method without setting the result set to return in the form of association array, and finally complete the data circular output through the while statement. The specific code is as follows:
$dbms = 'mysql'; $dbname = 'test'; $user = 'root'; $pwd = '123456'; $host = 'localhost'; $dsn = "$dbms:host=$host;dbname=$dbname"; try{ $pdo = new PDO($dsn,$user,$pwd); $query = "SELECT * FROM user"; $res = $pdo->prepare($query); $res->execute(); while($result = $res->fetch(PDO::FETCH_ASSOC)){ echo $result['username'].' '.$result['password'].' '.$result['email'].'<br>'; } }catch(Exception $e){ echo $e->getMessage().'<br>'; }
Detailed explanation of fetchAll() method for getting result set in PDO
$dbms = 'mysql'; $dbname = 'test'; $user = 'root'; $pwd = '123456'; $host = 'localhost'; $dsn = "$dbms:host=$host;dbname=$dbname"; try{ $pdo = new PDO($dsn,$user,$pwd); $query = "SELECT * FROM user"; $res = $pdo->prepare($query); $res->execute(); $result = $res->fetchAll(PDO::FETCH_ASSOC); for($i=0;$i<count($result);$i++){ echo $result[$i]['username'].' '.$result[$i]['password'].' '.$result[$i]['email'].'<br>'; } }catch(Exception $e){ echo $e->getMessage().'<br>'; }
Detailed explanation of fetchColumn() method to get result set in PDO
$dbms = 'mysql'; $dbname = 'test'; $user = 'root'; $pwd = '123456'; $host = 'localhost'; $dsn = "$dbms:host=$host;dbname=$dbname"; try{ $pdo = new PDO($dsn,$user,$pwd); $query = "select * from user"; $res = $pdo->prepare($query); $res->execute(); echo $res->fetchColumn(0).'<br>';//return id echo $res->fetchColumn(0).'<br>'; echo $res->fetchColumn(0).'<br>'; }catch(Exception $e){ echo $e->getMessage().'<br>'; }
Three methods of executing SQL statement in PDO
The first method: exec() method
This method returns the number of affected rows when executing the SQL statement, which is usually used in INSERT, DELETE and UPDATE statements.
<?php $dbms = 'mysql'; $dbname = 'test'; $user = 'root'; $pwd = '123456'; $host = 'localhost'; $dsn = "$dbms:host=$host;dbname=$dbname"; try{ $pdo = new PDO($dsn,$user,$pwd); $query = "INSERT INTO user(username,password,confirm,email) VALUES('cyy01','123','123','[email protected]')"; $res = $pdo->exec($query); echo 'Insert successful, number of rows affected:'.$res; }catch(Exception $e){ echo $e->getMessage().'<br>'; }
The second method: query() method
The query() method is used to return the result set after executing the query
<?php $dbms = 'mysql'; $dbname = 'test'; $user = 'root'; $pwd = '123456'; $host = 'localhost'; $dsn = "$dbms:host=$host;dbname=$dbname"; try{ $pdo = new PDO($dsn,$user,$pwd); $query = "SELECT * FROM user"; $res = $pdo->query($query); print_r($res); }catch(Exception $e){ echo $e->getMessage().'<br>'; }
be careful:
1. query and exec can execute all sql statements, but the return value is different.
2. query can implement all the functions of exec.
3. Always return 0 when the select statement is applied to exec
4. If you want to see the specific results of the query, you can complete the circular output through the foreach statement
The third method: preprocessing statements: prepare() statement and execute() statement
You can also bind parameters to the execute() method through the bindParam() method
$dbms = 'mysql'; $dbname = 'test'; $user = 'root'; $pwd = '123456'; $host = 'localhost'; $dsn = "$dbms:host=$host;dbname=$dbname"; try{ $pdo = new PDO($dsn,$user,$pwd); $query = "SELECT * FROM user"; $res = $pdo->prepare($query); $res->execute(); while($result = $res->fetch(PDO::FETCH_ASSOC)){ echo $result['id']." ".$result['username']." ".$result['password'].'<br>'; } }catch(Exception $e){ echo $e->getMessage().'<br>'; }
Use default mode - PDO:: errmode_ Silent (error method 1 in capturing SQL statement in PDO)
$dbms = 'mysql'; $dbname = 'test'; $user = 'root'; $pwd = '123456'; $host = 'localhost'; $dsn = "$dbms:host=$host;dbname=$dbname"; try{ $pdo = new PDO($dsn,$user,$pwd); $query = "insert into `user_12`(username,password) VALUES ('cyy02','123')"; $res = $pdo->prepare($query); $res->execute(); $code = $res->errorCode(); if(empty($code)){ echo 'Insert successful'; }else{ var_dump($res->errorInfo()); } }catch(Exception $e){ echo $e->getMessage().'<br>'; }
be careful:
In the above code, when defining the INSERT add statement, the wrong data table name user is intentionally used_ 12 (the correct data table name is: user), which is written for testing!
The error output results are as follows:
Use warning mode - PDO:: errmode_ Warning (error method 2 in capturing SQL statement in PDO)
$dbms = 'mysql'; $dbname = 'test'; $user = 'root'; $pwd = '123456'; $host = 'localhost'; $dsn = "$dbms:host=$host;dbname=$dbname"; try{ $pdo = new PDO($dsn,$user,$pwd); //Set to warning mode $pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_WARNING); $query = "select * from user_12"; $res = $pdo->prepare($query); $res->execute(); while($result = $res->fetch(PDO::FETCH_ASSOC)){ echo $result['username']; }
echo 'The program can continue to execute~'; }catch(Exception $e){ echo $e->getMessage().'<br>'; }
be careful:
In the above code, when defining the SELECT query statement, we deliberately used the wrong data table name user_ 12 (the correct data table name is: user), which is written for testing!
After the warning mode is set, a prompt will be given if there is an error in the SQL statement, but the program can still continue to execute. The results of the above example are as follows:
Use exception mode - PDO:: errmode_ Exception (error method 3 in capturing SQL statement in PDO)
$dbms = 'mysql'; $dbname = 'test'; $user = 'root'; $pwd = '123456'; $host = 'localhost'; $dsn = "$dbms:host=$host;dbname=$dbname"; try{ $pdo = new PDO($dsn,$user,$pwd); //Set to exception mode $pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION); $query = "delete * from user_12 where id = :id"; $res = $pdo->prepare($query); $id = 5; $res->bindParam(':id',$id); $res->execute(); }catch(PDOException $e){ echo 'error:'.$e->getMessage().'<br>'; echo 'code:'.$e->getCode().'<br>'; echo 'file:'.$e->getFile().'<br>'; echo 'line:'.$e->getLine().'<br>'; echo 'trace:'.$e->getTraceAsString().'<br>'; }
be careful:
In the above code, when defining the DELETE statement, we deliberately used the wrong data table name user_ 12 (the correct data table name is: user), which is written for testing!
After the exception mode is set, execute the wrong SQL statement. The output is as follows:
In PDO, there are two methods to get the error information in the program: the errorCode() method and the errorInfo() method!
$dbms = 'mysql'; $dbname = 'test'; $user = 'root'; $pwd = '123456'; $host = 'localhost'; $dsn = "$dbms:host=$host;dbname=$dbname"; try{ $pdo = new PDO($dsn,$user,$pwd); $query = "DELETE FROM user_12"; $res = $pdo->query($query); echo $pdo->errorCode().'<br>'; }catch(PDOException $e){ echo $pdo->errorCode().'<br>'; echo $e->getMessage().'<br>'; }
The operation results are as follows:
Method 2 of error handling in PDO - errorInfo() method
<?php $dbms = 'mysql'; $dbname = 'test'; $user = 'root'; $pwd = '123456'; $host = 'localhost'; $dsn = "$dbms:host=$host;dbname=$dbname"; try{ $pdo = new PDO($dsn,$user,$pwd); $query = "DELETE FROM user_12"; $res = $pdo->query($query); print_r($pdo->errorInfo()); }catch(PDOException $e){ echo $pdo->errorCode().'<br>'; echo $e->getMessage().'<br>'; }
The output results are as follows:
Transactions in PDO
(1) Open transaction -- beginTransaction() method.
The beginTransaction() method turns off autocommit mode and does not resume until the transaction is committed or rolled back.
(2) Commit transaction -- commit() method
The commit() method completes the commit operation of the transaction, and returns true successfully. Otherwise, it returns false.
(3) Transaction rollback -- rollBack() method
The rollBack() method performs a rollback of the transaction.
<?php $dbms = 'mysql'; $dbname = 'test'; $user = 'root'; $pwd = '123456'; $host = 'localhost'; $dsn = "$dbms:host=$host;dbname=$dbname"; try{ $pdo = new PDO($dsn,$user,$pwd); // Open transaction $pdo->beginTransaction(); $query = "insert into user(username,password,confirm,email) VALUES ('cyy03','333','333','[email protected]')"; $res = $pdo->prepare($query); $res->execute(); if($res->errorCode()){ echo 'Data added successfully'; }else{ echo 'Data add failed'; } //Transaction commit $pdo->commit(); }catch(PDOException $e){ die($e->getMessage().'<br>'); //Transaction rollback $pdo->rollBack(); }
The final output is as follows: the data is added successfully