mysql database method

MySQL is an open source relational database management system that is widely used in web development, enterprise applications, e-commerce and other fields. MySQL provides a rich set of methods that allow developers to access and operate the database conveniently and efficiently. This article will introduce common methods of MySQL database and how to use them.

1. Connecting to MySQL database

Connecting to MySQL database is a necessary step before performing any operation. In MySQL, we can use mysqli and PDO to connect to the database. Among them, mysqli is an object-oriented connection method, and PDO is the data object of PHP.

Use mysqli to connect to MySQL:

//Connect to the database
$conn = new mysqli($servername, $username, $password, $dbname);

//Check whether the connection is successful
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>

Use PDO to connect to MySQL:

//Connect to the database
$db = new PDO( 'mysql:host=localhost;dbname=myDB;charset=utf8', 'username', 'password');

//Check whether the connection is successful
if(!$db){
echo "

Failed to connect to database

Connected successfully

2. Execute SQL statements

In MySQL, executing SQL statements is the core method of operating the database. In PHP, we can use mysqli and PDO to execute SQL statements.

Use mysqli to execute SQL statements:

$sql = "SELECT * FROM MyGuests";
$result = $conn->query($sql );

if ($result->num_rows > 0) {
// Output data
while($row = $result->fetch_assoc()) {

echo "id: " . $row["id"]. " - 姓名: " . $row["firstname"]. " " . $row["lastname"]. "<br>";

}
} else {
echo "0 result";
}
$conn->close();
?>

Use PDO to execute SQL statement:

$sql = "SELECT * FROM MyGuests";
$result = $db->query($sql);

foreach ($result as $row){

 echo "id: ".$row['id']." - 姓名: ".$row['firstname']." ".$row['lastname']."<br/>";

}
?>

3. Insert data

In MySQL, inserting data is our Frequently used method. We can use the INSERT INTO statement to insert a piece of data.

Use mysqli to insert data:

$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe' , 'john@example.com')";

if ($conn->query($sql) === TRUE) {
echo "New record inserted successfully";
} else {
echo "Error: " . $sql . "
" . $conn->error;
}

$conn->close();
?>

Insert data using PDO:

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

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

$stmt = $db->prepare($sql);
$stmt->bindParam(':firstname', $firstname);
$stmt->bindParam(':lastname', $lastname);
$stmt->bindParam(':email', $email);

$firstname = "John";
$lastname = "Doe";
$email = "john @example.com";
$stmt->execute();
echo "New record inserted successfully";
?>

4. Update data

Use the UPDATE statement to update data in MySQL. When updating data, we need to use WHERE to limit records.

Use mysqli to update data:

$sql = "UPDATE MyGuests SET lastname='Doe' WHERE id=2";

if ($conn->query($sql) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $conn->error;
}

$conn->close();
?>

Use PDO to update data:

$sql = "UPDATE MyGuests SET lastname=:lastname WHERE id=:id";
$stmt=$db->prepare($sql);
$stmt->bindParam(':lastname',$lastname);
$stmt->bindParam(':id',$id );

$lastname="Doe";
$id=2;
$stmt->execute();
echo "Record updated successfully";
?> ;

5. Delete data

Use the DELETE statement to delete data in MySQL. When deleting data, you also need to use WHERE to limit records.

Use mysqli to delete data:

$sql = "DELETE FROM MyGuests WHERE id=3";

if ($conn-> query($sql) === TRUE) {
echo "Record deleted successfully";
} else {
echo "Error deleting record: " . $conn->error;
}

$conn->close();
?>

Use PDO to delete data:

$sql = "DELETE FROM MyGuests WHERE id=:id";
$stmt=$db->prepare($sql);
$stmt->bindParam(':id',$id);

$id=3;
$stmt->execute();
echo "Record deleted successfully";
?>

6. Query data

In MySQL, we can use the SELECT statement to query data. It should be noted that the query result is a data set, and we need to use a loop to traverse each record.

Use mysqli to query data:

$sql = "SELECT * 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"]. "<br>";

}
} else {
echo "0 结果";
}
$conn->close();
?>

使用PDO查询数据：

$sql = "SELECT * FROM MyGuests";
$stmt=$db->query($sql);

foreach ($stmt as $row) {

echo "id: ".$row["id"]." - Name: ".$row["firstname"]." ".$row["lastname"]."<br/>";

}
?>

总结

本文介绍了使用mysqli和PDO两种方式来连接MySQL数据库，并演示了常见的数据操作方法，如插入数据、更新数据、删除数据和查询数据等。熟练掌握这些方法，将有助于提高MySQL数据库的访问和操作效率，使开发工作更加顺利。

The above is the detailed content of mysql database method. For more information, please follow other related articles on the PHP Chinese website!