php mysql exec_php ，MySQL操作的两种方式

本文省略php对MySQL的面向过程的操作方式，介绍最常用的，面向对象的操作方式和PDO方式

面向对象的操作方式：

$servername = "localhost";

$username = "username";

$password =

"password";

// 创建连接

$conn =

new mysqli($servername, $username, $password);

// 确定数据库，创建连接

//$conn =

new mysqli($servername, $username, $password, $dbname);

// 检测连接

if ($conn->connect_error) {

die("连接失败: " . $conn->connect_error);

}

// 创建数据库

$sql = "CREATE DATABASE myDB";

if ($conn->query($sql) === TRUE) {

echo "数据库创建成功";

} else {

echo

"Error creating database: " . $conn->error;

}

//选择数据库

mysql_select_db('MyDB');

// 使用 sql 创建数据表

$sql

= "CREATE TABLE MyGuests (

id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY

KEY,

firstname VARCHAR(30) NOT NULL,

lastname VARCHAR(30) NOT NULL,

email

VARCHAR(50),

reg_date TIMESTAMP

)";

if ($conn->query($sql) === TRUE) {

echo "Table

MyGuests created successfully";

} else {

echo

"创建数据表错误: " . $conn->error;

}

//插入数据

$sql = "INSERT INTO MyGuests (firstname, lastname, email)

VALUES ('John', 'Doe', 'john@example.com')";

if ($conn->query($sql) === TRUE) {

echo "新记录插入成功";

} else {

echo

"Error: " . $sql . "
" . $conn->error;

}

//插入多条数据

$sql = "INSERT INTO MyGuests (firstname, lastname, email)

VALUES ('John', 'Doe', 'john@example.com');";

$sql .= "INSERT INTO

MyGuests (firstname, lastname, email)

VALUES ('Mary', 'Moe', 'mary@example.com');";

$sql .= "INSERT INTO

MyGuests (firstname, lastname, email)

VALUES ('Julie', 'Dooley', 'julie@example.com')";

if ($conn->multi_query($sql) === TRUE) {

echo "新记录插入成功";

} else {

echo

"Error: " . $sql . "
" . $conn->error;

}

// 预处理及绑定

$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email)

VALUES(?, ?, ?)");

$stmt->bind_param("sss", $firstname, $lastname,

$email);

// 设置参数并执行

$firstname = "John";

$lastname

= "Doe";

$email = "john@example.com";

$stmt->execute();

$firstname

= "Mary";

$lastname = "Moe";

$email = "mary@example.com";

$stmt->execute();

$firstname = "Julie";

$lastname = "Dooley";

$email = "julie@example.com";

$stmt->execute();

echo "新记录插入成功";

$stmt->close();

//读取数据

$sql = "SELECT id, firstname, lastname FROM MyGuests";

$result

= $conn->query($sql);

if ($result->num_rows > 0) {

// 输出每行数据

while($row = $result->fetch_assoc())

{

echo "
id: ".

$row["id"]. " - Name: ". $row["firstname"]. " " . $row["lastname"];

}

} else {

echo "0 个结果";

}

$conn->close();

?>

使用pdo方式

$servername = "localhost";

$username = "username";

$password = "password";

try {

$conn = new PDO("mysql:host=$servername;dbname=myDB", $username, $password);

// 设置 PDO 错误模式为异常

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$sql = "CREATE DATABASE myDBPDO";

// 使用 exec() ，因为没有结果返回

$conn->exec($sql);

echo "数据库创建成功
";

// 使用 sql 创建数据表

$sql

= "CREATE TABLE MyGuests (

id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY

KEY,

firstname VARCHAR(30) NOT NULL,

lastname VARCHAR(30) NOT NULL,

email

VARCHAR(50),

reg_date TIMESTAMP

)";

// 使用 exec() ，没有结果返回

$conn->exec($sql);

echo "数据表 MyGuests 创建成功";

//插入数据

$sql = "INSERT INTO MyGuests (firstname, lastname, email)

VALUES ('John', 'Doe', 'john@example.com')";

// 使用 exec() ，没有结果返回

$conn->exec($sql);

echo "新记录插入成功";

//插入多条数据

// 开始事务

$conn->beginTransaction();

// SQL 语句

$conn->exec("INSERT INTO MyGuests (firstname, lastname, email)

VALUES ('John', 'Doe', 'john@example.com')");

$conn->exec("INSERT INTO MyGuests (firstname, lastname, email)

VALUES ('Mary', 'Moe', 'mary@example.com')");

$conn->exec("INSERT INTO MyGuests (firstname, lastname, email)

VALUES ('Julie', 'Dooley', 'julie@example.com')");

// 提交事务

$conn->commit();

echo "新记录插入成功";

// 预处理 SQL 并绑定参数

$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email)

VALUES (:firstname, :lastname, :email)");

$stmt->bindParam(':firstname', $firstname);

$stmt->bindParam(':lastname',

$lastname);

$stmt->bindParam(':email', $email);

// 插入行

$firstname =

"John";

$lastname = "Doe";

$email = "john@example.com";

$stmt->execute();

// 插入其他行

$firstname = "Mary";

$lastname = "Moe";

$email = "mary@example.com";

$stmt->execute();

// 插入其他行

$firstname = "Julie";

$lastname = "Dooley";

$email = "julie@example.com";

$stmt->execute();

echo "新记录插入成功";

//查询数据

$stmt = $conn->prepare("SELECT * FROM MyGuests");

$stmt->execute();

// 设置结果集为关联数组

$result = $stmt->setFetchMode(PDO::FETCH_ASSOC);

foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as

$k=>$v) {

echo $v;

}

$stmt = null;

}

catch(PDOException $e)

{

echo $sql . "
" . $e->getMessage();

}

$conn = null;

?>