Analysis of Method to Execute SQL Statements Using PDO in php

This article instance describes the method of executing SQL statements using PDO in PHP. Shared with everyone for reference, as follows:

exec() method

The exec() method returns the number of rows affected after execution, the syntax is as follows:

int PDO::exec(string statement)

The parameter statement is the SQL statement to be executed. This method returns the number of rows affected by the execution of the query, which is usually used in INSERT, DELETE, and UPDATE statements.

For example:

$dbms='mysql';//Database type
$dbName='admin';//Database used
$user='root';//Database connection username
$pwd='password';//Database connection password
$host='localhost';//Database hostname
$dsn="$dbms:host=$host;port=3306;dbname=$dbName";
try{
$pdo=new PDO($dsn,$user,$pwd);//Initialize a PDO object, which is to create a database connection object $pdo
$query="insert into user(username,password) values('admin','123456')";//The SQL statement to be executed
$res=$pdo->exec($query);//Execute the insert statement and return the number of affected rows
echo "Data added successfully, the number of affected rows is: ". $res;
}
die("Error!":$e->getMessage().'<br>');
}

The running result is:

Data added successfully, the number of affected rows is: 1

query() method

The query() method is used to return the result set after executing the query, the syntax is as follows

PDOStatement PDO::query(string statement)

The parameter statement is the SQL statement to be executed. It returns a PDOStatement object

For example:

$dbms='mysql';
$dbName='admin';
$user='root';
$pwd='905407339';
$host='localhost';
$dsn="$dbms:host=$host;port=3306;dbname=$dbName";
try{
$pdo=new PDO($dsn,$user,$pwd);
$query="select * from user";
$res=$pdo->query($query);
print_r($res);
}
die("Error!":$e->getMessage().'<br>');
}

The running result is:

PDOStatement Object ([queryString] => select) * from user )

If you want to view the specific query results, you can complete the loop output through the foreach statement

For example:

foreach($res as $val){
echo $val['username']."----".$val['password'].'<br>';
}

The running result is:

107lab----e10adc3949ba59abbe56e057f20f883e
admin----123456

Description: If you want to see the detailed usage of foreach, please see: https://www.oldtoolbag.com/article/68786.htm

query() vs exec()

query can implement all the functions of exec

For example:

$dbms='mysql';//Database type
$dbName='admin';//Database used
$user='root';//Database connection username
$pwd='905407339';//Database connection password
$host='localhost';//Database hostname
$dsn="$dbms:host=$host;port=3306;dbname=$dbName";
try{
$pdo=new PDO($dsn,$user,$pwd);//Initialize a PDO object, which is to create a database connection object $pdo
$query="insert into user(username,password) values('admin','123456')";//The SQL statement to be executed
$res=$pdo->query($query);//Execute the insert statement and return the number of affected rows
echo "Data added successfully, the number of affected rows is: ",$res->rowCount();
}
die("Error!":$e->getMessage().'<br>');
}

The running result is:

Data added successfully, the number of affected rows is: 1

Note:

1、query and exec can execute all sql statements, just the return value is different.
2、query can implement all the functions of exec.
3、When applying select statement to exec, it always returns 0

Prepared statement----prepare() statement and execute() statement

Prepared statements include prepare() and execute() methods. First, prepare the query through the prepare() method, then execute the query through the execute() method, and you can also bind parameters to the execute() method using bindParam() method, syntax as follows:

PDOStatement PDO::prepare(string statement[,array driver_options])
bool PDOStatement::execute([array input_parameters])

For example:

Execute SQL query statements through prepare() and execute() in PDO, and use while() statements and fetch() methods to complete the cyclic output of data

$dbms='mysql';//Database type
$dbName='admin';//Database used
$user='root';//Database connection username
$pwd='905407339';//Database connection password
$host='localhost';//Database hostname
$dsn="$dbms:host=$host;port=3306;dbname=$dbName";
try{
  $pdo=new PDO($dsn,$user,$pwd);//Initialize a PDO object, which is to create a database connection object $pdo
  $query="select * from user";//The SQL statement to be executed
  $res=$pdo->prepare($query);//Prepare the query statement
  $res->execute();
  while($result=$res->fetch(PDO::FETCH_ASSOC)){
    echo $result['id']." ".$result['username']." ".$result['password'].'<br>';
  }
}
  die("Error!":$e->getMessage().'<br>');
}

The running result is:

1 107lab e10adc3949ba59abbe56e057f20f883e
4 admin 123456
5 admin 123456

Readers who are interested in more about PHP-related content can view the special topic of this site: 《Summary of PHP Database Operation Skills Based on PDO》、《PHP+Oracle Database Program Design Skills Summary》、《PHP+MongoDB Database Operation Skills大全》、《PHP Object-Oriented Program Design Tutorial for Beginners》、《Summary of PHP String (string) Usage》、《PHP+MySQL Database Operation Tutorial》and《Summary of Common PHP Database Operation Skills》

I hope the content of this article will be helpful to everyone in PHP program design.

Statement: The content of this article is from the Internet, the copyright belongs to the original author. The content is contributed and uploaded by Internet users spontaneously. This website does not own the copyright, has not been edited by humans, and does not assume relevant legal liabilities. If you find any content suspected of copyright infringement, please send an email to: notice#w3Please send an email to notice#w when reporting, replacing # with @, and providing relevant evidence. Once verified, this site will immediately delete the content suspected of infringement.