PHP operates MySQL database
PHP connection to MySQL
Before we access the MySQL database, we need to connect to the database server first. To connect to the server, we use the mysqli_connect() function.
Before using this function, let’s first take a look at the syntax of this function:
mysqli_connect(host,username,password,dbname,port,socket);
Parameter description
Return value
If the connection is successful Returns an object representing a connection to the MySQL server.
Here, I am using the wamp integrated mysql database. We use the above method to connect to our database. (The default user name is root, and the password is empty);
$conn=mysqli_connect("localhost","root","");
if(!$conn){
die("Connection failed: " . mysqli_connect_error());//如果连接失败输出一条消息，并退出当前脚本}Create database
mysqli_query()
In php, execute the mysql statement, The mysqli_query() method must be used. So before creating a database, let's first take a look at the usage of mysqli_query():
mysqli_query(connection,query,resultmode);
Parameter description
Return value
For successful SELECT, SHOW, DESCRIBE or EXPLAIN query, a mysqli_result object will be returned. For other successful queries, TRUE will be returned. On failure, returns FALSE.
Create database
Create a database using the CREATE DATABASE statement, and this statement needs to be executed through the mysqli_query() method to take effect. (Note: In PHP, all mysql statements need to be executed through this method to take effect, so they will not be explained again below)
Next we will do this in our local database, Create a database named test01
= ('localhost','root','' = "CREATE DATABASE test01"(,After executing the above statement, we can use the show databases statement to check whether the database has been created successfully. ()
Open cmd, enter mysql -u username -p and press Enter to enter the password according to the prompts. At this time, you can enter the console of the mysql database. If after typing, it prompts that mysql is not an internal or external command, then we only need to find the installation directory (bin directory) of mysql and copy it, and then use this path as a variable value to configure the environment variable. You can
Enter the show databases statement. At this time, you can see that the test01 database we just created already exists, as shown below
Create data table
Select database
After the database is created, we will start to create the data table. Before creating the table, we must first select the method to create the table. Database, we use the mysqli_select_db() method to select the database. Similarly, before using this method, let’s first take a look at the usage of this method:
mysqli_select_db(connection,dbname);
Parameter description:
Returns TRUE if successful and FALSE if failed. Now let's use this method and select the table we just created.
mysqli_select_db($conn,'test01');//选择数据库
Create data table
Create a table using the CREATE TABLE table name statement. Next we use this statement to create an admin table
$sql="CREATE TABLE admin ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(20) NOT NULL, password CHAR(6) NOT NULL, email VARCHAR(50) NOT NULL )";mysqli_query($conn,$sql); //创建数据库
NOT NULL - Each row must contain a value (cannot be empty), null values are not allowed.
UNSIGNED - Use unsigned numeric types, 0 and positive numbers
AUTO INCREMENT - Set the value of the MySQL field every time when adding a record Automatically increase by 1
PRIMARY KEY - Set the unique identifier of each record in the data table. Usually the PRIMARY KEY of the column is set to the ID value, used with AUTO_INCREMENT.
Insert data
After creating the database and table, we can add data to the table.
INSERT INTO statement is usually used to add new records to the MySQL table:
INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...)
Example:
$sql="INSERT INTO admin(username,password,email) VALUES('admin','123456','123456789@qq.com')";mysqli_query($conn,$sql);Complete execution After that, we can check whether the newly created piece of data exists in the database. As you can see from the picture on the right, this piece of data has been successfully created in our table.
Deleting data
Use the DELETE FROM statement to delete records from the database table.
DELETE FROM table_name WHERE some_column = some_value
$sql="DELETE FROM admin WHERE username='admin'";mysqli_query($conn,$sql);
Update data
The UPDATE statement is used to update existing records in the database table.
UPDATE table_name SET column1=value, column2=value2,... WHERE some_column=some_value
实例：
$sql="UPDATE admin SET email='309123793@qq.com'"; mysqli_query($conn,$sql);
读取数据
SELECT 语句用于从数据表中读取数据:
SELECT column_name(s) FROM table_name
实例：
$sql = "SELECT id, username, email FROM admin"; mysqli_query($conn,$sql);
ORDER BY 关键词
SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC|DESC
说明：默认为升序排列，如果需要降序排列，请使用 DESC 关键字。
$sql="SELECT * FROM admin ORDER BY username";
mysqli_query($conn,$sql);
The above is the detailed content of PHP operates MySQL database. For more information, please follow other related articles on the PHP Chinese website!
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
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
PHP and Python: Different Paradigms Explained
Apr 18, 2025 am 12:26 AM
PHP is mainly procedural programming, but also supports object-oriented programming (OOP); Python supports a variety of paradigms, including OOP, functional and procedural programming. PHP is suitable for web development, and Python is suitable for a variety of applications such as data analysis and machine learning.
Choosing Between PHP and Python: A Guide
Apr 18, 2025 am 12:24 AM
PHP is suitable for web development and rapid prototyping, and Python is suitable for data science and machine learning. 1.PHP is used for dynamic web development, with simple syntax and suitable for rapid development. 2. Python has concise syntax, is suitable for multiple fields, and has a strong library ecosystem.
Why Use PHP? Advantages and Benefits Explained
Apr 16, 2025 am 12:16 AM
The core benefits of PHP include ease of learning, strong web development support, rich libraries and frameworks, high performance and scalability, cross-platform compatibility, and cost-effectiveness. 1) Easy to learn and use, suitable for beginners; 2) Good integration with web servers and supports multiple databases; 3) Have powerful frameworks such as Laravel; 4) High performance can be achieved through optimization; 5) Support multiple operating systems; 6) Open source to reduce development costs.
MySQL's Role: Databases in Web Applications
Apr 17, 2025 am 12:23 AM
The main role of MySQL in web applications is to store and manage data. 1.MySQL efficiently processes user information, product catalogs, transaction records and other data. 2. Through SQL query, developers can extract information from the database to generate dynamic content. 3.MySQL works based on the client-server model to ensure acceptable query speed.
Laravel Introduction Example
Apr 18, 2025 pm 12:45 PM
Laravel is a PHP framework for easy building of web applications. It provides a range of powerful features including: Installation: Install the Laravel CLI globally with Composer and create applications in the project directory. Routing: Define the relationship between the URL and the handler in routes/web.php. View: Create a view in resources/views to render the application's interface. Database Integration: Provides out-of-the-box integration with databases such as MySQL and uses migration to create and modify tables. Model and Controller: The model represents the database entity and the controller processes HTTP requests.
PHP and Python: A Deep Dive into Their History
Apr 18, 2025 am 12:25 AM
PHP originated in 1994 and was developed by RasmusLerdorf. It was originally used to track website visitors and gradually evolved into a server-side scripting language and was widely used in web development. Python was developed by Guidovan Rossum in the late 1980s and was first released in 1991. It emphasizes code readability and simplicity, and is suitable for scientific computing, data analysis and other fields.
PHP: An Introduction to the Server-Side Scripting Language
Apr 16, 2025 am 12:18 AM
PHP is a server-side scripting language used for dynamic web development and server-side applications. 1.PHP is an interpreted language that does not require compilation and is suitable for rapid development. 2. PHP code is embedded in HTML, making it easy to develop web pages. 3. PHP processes server-side logic, generates HTML output, and supports user interaction and data processing. 4. PHP can interact with the database, process form submission, and execute server-side tasks.
PHP and the Web: Exploring its Long-Term Impact
Apr 16, 2025 am 12:17 AM
PHP has shaped the network over the past few decades and will continue to play an important role in web development. 1) PHP originated in 1994 and has become the first choice for developers due to its ease of use and seamless integration with MySQL. 2) Its core functions include generating dynamic content and integrating with the database, allowing the website to be updated in real time and displayed in personalized manner. 3) The wide application and ecosystem of PHP have driven its long-term impact, but it also faces version updates and security challenges. 4) Performance improvements in recent years, such as the release of PHP7, enable it to compete with modern languages. 5) In the future, PHP needs to deal with new challenges such as containerization and microservices, but its flexibility and active community make it adaptable.
