Three ways to execute SQL statements in PDO

three ways to execute SQL statements in PDO

In PDO, we can execute SQL statements in three ways, namely the Exec () method, the Query method, and the preprocessing statement prepare () and Execute () methods ~

In the previous article, "Using PDO constructors to connect to databases and DSN details," We show you how to use constructors to connect to the database and DSN in detail, then we introduce you to the three ways to execute SQL statements in PDO, as we'll cover here!

First method: Exec () method

The Exec () method returns the number of rows affected after executing the SQL statement with the following syntax:

int pdo::exec (string statement)

The parameter satatement is the SQL statement to execute that returns the number of rows affected when the SQL statement is executed, typically used in insert,delete and UPDATE statements. Below we explain with specific code, the code is as follows:

<?phpheader ("content-type:text/html; Charset=utf-8 ");    Set the encoding format of the page $dbms = "MySQL";                                  The type of database $dbname = "PHP_CN";                                The database name used is $user = "root";                                   Database user name used $pwd = "root";                                    The database password used $host = "localhost";                              Host name used $dsn  = "$dbms: host= $host;d bname= $dbName"; try{                                             //catch exception    $pdo = new PDO ($DSN, $user, $pwd);             Instantiate object    $query = "INSERT into user (Username,password) VALUES (' php ', ' 523 ') ';//SQL statement to be executed    $res = $pdo EXEC ($query);//Executes the Add statement and returns the number of affected rows    echo "Data added successfully, the number of rows affected is:". $res; catch (Exception $e) {die    ("error!:". $e->getmessage (). ' <br> ');}? >

The output is:

Second method: Query () method

The query () method is used to return the result set after the query is executed, and the syntax of the function is as follows:

Pdostatement pdo::query (String statement)

The parameter satatement is the SQL statement to execute, and it returns a Podstatement object! See the following sample code for details:

<?phpheader ("content-type:text/html; Charset=utf-8 ");    Set the encoding format of the page $dbms = "MySQL";                                  The type of database $dbname = "PHP_CN";                                The database name used is $user = "root";                                   Database user name used $pwd = "root";                                    The database password used $host = "localhost";                              Host name used $dsn  = "$dbms: host= $host;d bname= $dbName"; try{    $pdo =new PDO ($DSN, $user, $pwd);    $query = "SELECT * from user";    $res = $pdo->query ($query);    Print_r ($res);} catch (Exception $e) {die    ("error!:". $e->getmessage (). ' <br> ');}? >

The output is:

Attention:

1. Both query and exec can execute all SQL statements, but the return value is different.

2. Query can realize all the functions of exec.

3. When the SELECT statement is applied to exec, it always returns 0

4, if you want to see the specific results of the query, you can complete the loop output through the foreach statement

The third method: Preprocessing statements: Prepare () statements and execute () statements

Preprocessing statements include both the prepare () and the Execute () methods. First, the query preparation is done through the prepare () method, then the query is executed through the Execute () method, and the Bindparam () method can be used to bind parameters to the Execute () method, with the following syntax:

Pdostatement PDO::p repare (String Statement[,array driver_options]) bool Pdostatement::execute ([Array input_ Parameters])

Execute SQL query statements in PDO using a preprocessing statement prepare () and execute (), loop through the data, and look at the sample code below:

<?phpheader ("content-type:text/html; Charset=utf-8 ");    Set the encoding format of the page $dbms = "MySQL";                                  The type of database $dbname = "PHP_CN";                                The database name used is $user = "root";                                   Database user name used $pwd = "root";                                    The database password used $host = "localhost";                              Host name used $dsn  = "$dbms: host= $host;d bname= $dbName"; try{    $pdo =new PDO ($DSN, $user, $pwd);//Initialize a PDO object, is to create a database connection object $pdo    $query = "SELECT * from User",//SQL statement to execute    $res = $pdo->prepare ($query);//Prepare query statement    $ Res->execute ();    while ($result = $res->fetch (PDO::FETCH_ASSOC)) {        echo $result [' ID ']. " ". $result [' username ']." ". $result [' Password ']. ' <br> ';    }} catch (Exception $e) {die    ("error!:". $e->getmessage (). ' <br> ');}

The result of the output is as follows:

About PDO's execution of SQL three methods, we introduced here, is not very simple, the small partners can again their own local test, the next we will continue to introduce to you in PDO to obtain the result set method, specifically read in the PDO get result set of Fetch () method detailed!