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:
- Cross-platform: PDO supports many different databases, which can run in different operating systems.
- 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:
- Try to use PDO preprocessing and bind variables to prevent SQL injection attacks.
- Avoid using SELECT *, and try to manually specify the name of the column to be queried.
- 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!

This article explores efficient PHP array deduplication. It compares built-in functions like array_unique() with custom hashmap approaches, highlighting performance trade-offs based on array size and data type. The optimal method depends on profili

This article explores PHP array deduplication using key uniqueness. While not a direct duplicate removal method, leveraging key uniqueness allows for creating a new array with unique values by mapping values to keys, overwriting duplicates. This ap

This article analyzes PHP array deduplication, highlighting performance bottlenecks of naive approaches (O(n²)). It explores efficient alternatives using array_unique() with custom functions, SplObjectStorage, and HashSet implementations, achieving

This article details implementing message queues in PHP using RabbitMQ and Redis. It compares their architectures (AMQP vs. in-memory), features, and reliability mechanisms (confirmations, transactions, persistence). Best practices for design, error

This article examines current PHP coding standards and best practices, focusing on PSR recommendations (PSR-1, PSR-2, PSR-4, PSR-12). It emphasizes improving code readability and maintainability through consistent styling, meaningful naming, and eff

This article explores optimizing PHP array deduplication for large datasets. It examines techniques like array_unique(), array_flip(), SplObjectStorage, and pre-sorting, comparing their efficiency. For massive datasets, it suggests chunking, datab

This article details installing and troubleshooting PHP extensions, focusing on PECL. It covers installation steps (finding, downloading/compiling, enabling, restarting the server), troubleshooting techniques (checking logs, verifying installation,

This article explains PHP's Reflection API, enabling runtime inspection and manipulation of classes, methods, and properties. It details common use cases (documentation generation, ORMs, dependency injection) and cautions against performance overhea


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

PhpStorm Mac version
The latest (2018.2.1) professional PHP integrated development tool

SublimeText3 Mac version
God-level code editing software (SublimeText3)

mPDF
mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

Notepad++7.3.1
Easy-to-use and free code editor

Safe Exam Browser
Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.
