How to write query statements in php database

In the process of developing web applications, dealing with databases is inevitable. As a general-purpose programming language, PHP also has the ability to interact with databases. Developers can use it to perform various types of database query operations. However, it is not easy for beginners to correctly use PHP to interact with databases. This article aims to introduce you to how to correctly write PHP database query statements.

1. Connecting to the database in PHP

Before performing database query in PHP, we need to establish a connection with the database first. You can use PHP's own PDO (PHP Data Object) to connect to the database. Using PDO to connect to the database has the following benefits:

1. Cross-platform: PDO supports many different databases, which can run in different operating systems.
2. Prevent SQL injection: PDO automatically processes user-entered data to prevent SQL injection attacks.

Let’s take the MySQL database as an example to introduce how to use PDO to connect to the database.

• Create a database connection

To use PDO to connect to the database, we need to provide information such as database type, host name, port, database name, user name and password. The following is an example of creating a connection:

try {
    $pdo = new PDO('mysql:host=localhost;port=3306;dbname=my_database', 'my_username', 'my_password');
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

If the connection is successful, the $pdo object will represent a connection established with the MySQL database instance, otherwise an exception will be thrown.

• Execute SQL query

After connecting to the database, we can start executing SQL query. PDO provides a method to execute SQL queries (you can also perform SQL update and delete operations): exec(). This method receives a SQL statement as a parameter and returns an integer value indicating the number of affected rows or execution failure. For example, we can use the following code to create a table named "users":

$sql = 'CREATE TABLE users (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    firstname VARCHAR(30) NOT NULL,
    lastname VARCHAR(30) NOT NULL,
    email VARCHAR(50),
    reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)';

if ($pdo->exec($sql) !== false) {
    echo "Table users created successfully";
} else {
    echo "Error creating table: " . $pdo->errorInfo()[2];
}

• Querying data

Querying data is the most commonly used in PHP and database interaction operation. We can use PDO to execute SELECT statements to query data. Here is an example of querying data:

$sql = "SELECT * FROM users WHERE id = ? OR email = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$id, $email]);

$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

if (!empty($results)) {
    foreach ($results as $row) {
        echo $row['id'] . "\t" . $row['firstname'] . "\t" . $row['lastname'] . "\t" . $row['email'] . "\n";
    }
} else {
    echo "No results found";
}

Note that we use PDO's prepare() method to preprocess the SQL statement and bind the parameters in the query to placeholders. When executing a SQL query statement, use the execute() method to pass parameters and perform query operations. Finally, use the fetchAll() method to obtain the query results. The fetchAll() method returns an array, with each element representing a row of data in the query results.

2. Common SQL query statements

Using SQL query statements to interact with the database is a common operation in PHP. The following are common query statements.

• SELECT

The SELECT statement is used to select to retrieve data from one or more tables. The syntax is as follows:

SELECT column1, column2, ... FROM table_name WHERE condition(s);

Among them, column1, column2 are the names of the columns to be retrieved, table_name is the name of the data source table to be retrieved, and the conditions after the WHERE keyword specify the conditions for filtering data. Here is a simple example:

SELECT * FROM users;

The above code will retrieve all the data in the "users" table.

• UPDATE

The UPDATE statement is used to update data in an existing table. The syntax is as follows:

UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

Among them, value1, value2 are the values ​​to be updated, and condition specifies the condition of the row to be updated. The following is a simple example:

UPDATE users SET firstname='John', lastname='Doe' WHERE email='john.doe@example.com';

The above code will update the qualified data in the "users" table.

• DELETE

The DELETE statement is used to delete data from an existing table. The syntax is as follows:

DELETE FROM table_name WHERE condition;

Among them, condition specifies the condition of the row to be deleted. The following is a simple example:

DELETE FROM users WHERE email='john.doe@example.com';

The above code will delete qualifying rows from the "users" table.

• INSERT INTO

The INSERT INTO statement is used to insert new data into an existing table. The syntax is as follows:

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

Among them, value1, value2, value3 are the values ​​to be inserted. Here is a simple example:

INSERT INTO users (firstname, lastname, email) VALUES ('John', 'Doe', 'john.doe@example.com');

The above code will insert a new row into the "users" table.

3. Summary

In this article, we introduced the use of PDO to connect to the database and common SQL query statements, hoping to help readers better understand and master the interaction between PHP and the database.

When performing database query operations, you need to pay attention to the following points:

1. Try to use PDO preprocessing and bind variables to prevent SQL injection attacks.
2. Avoid using SELECT *, and try to manually specify the name of the column to be queried.
3. In PHP, before using check functions (such as isset() and empty()) to check a variable, you must first check whether the variable exists.

Hope this information will help you better use PHP to interact with databases, making it easier to build web applications.

The above is the detailed content of How to write query statements in php database. For more information, please follow other related articles on the PHP Chinese website!