How to create a user login table using MySQL

How to create a user login table using MySQL

When developing a website or application, the user login function is an essential part. In order to implement the user login function, we usually need to create a user login table in the database. This article will introduce in detail how to use MySQL to create a user login table and provide corresponding code examples.

To create a user login table in MySQL, you first need to create a database. Assume that we have created a database named "mydb", below we will create a user login table in the database.

The user login table needs to contain the following fields:

• id: User ID, as the primary key, used to uniquely identify each user.
• username: Username, used for user login.
• password: User password, used to verify the user's identity.

The following is the SQL statement to create a user login table:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL
);

The above SQL statement creates a table named "users", which contains three fields: id, username and password. The id field is an auto-incrementing primary key, and the username and password fields are string types and cannot be empty.

Next, we can insert some test data into the user login table for subsequent verification. The following is an example of a SQL statement to insert data:

INSERT INTO users (username, password) VALUES
    ('user1', 'password1'),
    ('user2', 'password2'),
    ('user3', 'password3');

After executing the above SQL statement, three user records will be inserted into the user login table, with the user names "user1", "user2" and "user3". , the passwords are "password1", "password2" and "password3" respectively.

Now, we have successfully created the user login table and inserted test data into the table. Next, we can perform user login verification. The following is an example of using PHP code for user login verification:

<?php
// 获取用户提交的表单数据
$username = $_POST['username'];
$password = $_POST['password'];

// 连接数据库
$servername = 'localhost';
$dbname = 'mydb';
$username_db = 'root';
$password_db = '';

$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username_db, $password_db);

// 查询用户登录信息
$query = "SELECT * FROM users WHERE username = :username AND password = :password";
$stmt = $conn->prepare($query);
$stmt->bindParam(':username', $username);
$stmt->bindParam(':password', $password);
$stmt->execute();

// 验证用户登录信息
if ($stmt->rowCount() > 0) {
    echo "登录成功！";
} else {
    echo "用户名或密码错误！";
}

// 关闭数据库连接
$conn = null;
?>

The above PHP code uses the PDO extension to connect to the MySQL database and executes a query statement to determine whether the user login information matches. If there is a match, "Login successful!" is output; if not, "Username or password is wrong!" is output.

Through the above steps, we successfully created a user login table and implemented the basic user login verification function. Of course, actual development usually involves other related functions, such as registration, password encryption, etc. But these are beyond the scope of this article.

In short, through the introduction of this article, you should already know how to use MySQL to create a user login table and use PHP code for user login verification. Hope this article is helpful to you!

The above is the detailed content of How to create a user login table using MySQL. For more information, please follow other related articles on the PHP Chinese website!