Use PHP+Mysql to implement addition, deletion, modification and query in ten minutes (detailed example)-PHP Tutorial-php.cn

This article brings you an example of how PHP uses mysql to implement the add, delete, modify, and query functions. I hope it will be helpful to you.

Use PHP+Mysql to implement addition, deletion, modification and query in ten minutes (detailed example)

PHP Mysql implements addition, deletion, modification and query

PHP is a way to create dynamic interactivity A powerful server-side scripting language for your site.

Database is a warehouse that organizes, stores and manages data according to data structure. Each database has one or more different APIs for creating, accessing, managing, searching, and copying saved data.

MySQL is a database system used on the Web and running on the server; MySQL is very fast, reliable, easy to use, and supports standard SQL.

Mysql statement

Open our wampserver server Mysql visualization tool (I use Navicat here), or other integrated tools (Apache PHP Mysql). Link to our server
Use PHP+Mysql to implement addition, deletion, modification and query in ten minutes (detailed example)

We create a new query to operate the database, first configure the basic file

Insert a piece of information

INSERT INTO syntax

You need to specify the column name to insert data, just provide the inserted value

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

Copy after login

Need to specify the column name and inserted value

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

Copy after login

Insert a piece of data into the table without specifying the column name

INSERT INTO stu VALUES (null,'提莫', 1,30);

Copy after login

We use the second syntax to insert a piece of data into the table

INSERT INTO stu (name, gender, age) VALUES ('Faker', 0,24);

Copy after login

Use PHP+Mysql to implement addition, deletion, modification and query in ten minutes (detailed example)

Query statement

SQL SELECT statement

SELECT column_name,column_name FROM table_name;

Copy after login

SELECT * FROM table_name;

Copy after login

Query id column

select id from stu;

Copy after login

Query the statement when the id is 1

select * from stu where id = 1;

Copy after login

Because the id is unique, there is no need to continue if the data is found

select * from stu where id = 1 limit 1;

Copy after login

Modify the statement

SQL UPDATE Statement You need to add a where statement, otherwise the entire table will be updated

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

Copy after login

Modify the name when the id is 1

update stu set name='the shy' where id=1;

Copy after login

Delete statement

SQL DELETE Syntax The WHERE clause specifies which record or records need to be deleted. If you omit the WHERE clause, all records will be deleted!

DELETE FROM table_name WHERE some_column=some_value;

Copy after login

Delete the student information with ID 2

delete from stu where id = 2;

Copy after login

Use PHP to operate Mysql

How to link to the database

header("Content-Type:text/html;charset=utf-8");
// 1. 使用mysqli链接数据库（这里使用wampserver默认的）
$connection = mysqli_connect('127.0.0.1', 'root', '', 'students');
// 2. 解决识别不了数据库文件的中文
mysqli_query($connection,"set names 'utf8';");
if (!$connection) {
    // 连接数据库失败
    exit('<h1>连接数据库失败</h1>');
}
// 每次只能查询一条数据
$query = mysqli_query($connection, 'select * from stu;');
// 查询所有的数据
while ($row = mysqli_fetch_assoc($query)) {
    var_dump($row);
}

Copy after login

Query the database to render the main page (index .php)

Adopt the mixed method to link the database in the head

<?php // 1.链接我们的数据库
$link = mysqli_connect(&#39;127.0.0.1&#39;, &#39;root&#39;, &#39;&#39;, &#39;students&#39;);
// 2.设置中文编码
mysqli_query($link,"set names &#39;utf8&#39;;");
// 3.检测链接
if ($link->connect_error) {
    die("连接失败: " . $link->connect_error);
}
// 4.查询数据
$query = mysqli_query($link, 'select * from stu;');
// 5.渲染数据
?>

Copy after login

Introduce bootstrap@4 (download bootstrap official website and introduce bootstrap.css)

<link>

Copy after login

Use mysqli_fetch_assoc($query) to render data, because subsequent operations need to be added (Use PHP+Mysql to implement addition, deletion, modification and query in ten minutes (detailed example)), deleted (del.php), and modified (edit) So add here first

<p>
    </p><h1>首页</h1>

Copy after login

学号	姓名	性别	年龄	操作
				" class="btn btn-primary">删除 " class="btn btn-danger">修改

添加Use PHP+Mysql to implement addition, deletion, modification and query in ten minutes (detailed example)信息

Use PHP+Mysql to implement addition, deletion, modification and query in ten minutes (detailed example)

Add a piece of data (Use PHP+Mysql to implement addition, deletion, modification and query in ten minutes (detailed example))

We still use the mixed mode, form data Submit to this page, use $_SERVER['PHP_SELF'] to make the code more robust
Use post to submit data, remember to prompt information errors on the page
Header link to the database and insert a piece of data

<?php // 1. 判断是否是post提交// 2. 处理表单传递过来的数据（不能为空!empty;这里我就先不做处理了）// 3. 连接数据库并插入一条数据// 4. 开始查询（insert into）// 5. 判断是否查询Use PHP+Mysql to implement addition, deletion, modification and query in ten minutes (detailed example)// 6. 判断是否插入Use PHP+Mysql to implement addition, deletion, modification and query in ten minutes (detailed example)`mysqli_affected_rows()`// 7. 重定向function add_user(){
    $name = $_POST[&#39;name&#39;];
    $age = $_POST[&#39;age&#39;];
    $gender = $_POST[&#39;gender&#39;];
    $link = mysqli_connect(&#39;127.0.0.1&#39;, &#39;root&#39;, &#39;&#39;, &#39;students&#39;);
    mysqli_query($link,"set names &#39;utf8&#39;;");
    if(!link){
        $GLOBALS[&#39;msg&#39;] = &#39;连接数据库失败&#39;;
        return;
    }
    $query = mysqli_query($link,"INSERT INTO stu (name, gender, age) VALUES (&#39;{$name}&#39;,{$gender},{$age});");
    if (!$query) {
        $GLOBALS[&#39;msg&#39;] = &#39;查询过程失败&#39;;
        return;
    }
    $affected = mysqli_affected_rows($link);
    if($affected!==1){
        $GLOBALS[&#39;error_message&#39;] = &#39;添加数据失败&#39;;
        return;
    }
    header(&#39;Location:index.php&#39;);}if($_SERVER[&#39;REQUEST_METHOD&#39;]===&#39;POST&#39;){
    add_user();}?>

Copy after login

Interface

<p>
    </p><h4>添加Use PHP+Mysql to implement addition, deletion, modification and query in ten minutes (detailed example)信息</h4>

Copy after login

Click to add student information and jump to Use PHP+Mysql to implement addition, deletion, modification and query in ten minutes (detailed example)

Delete a piece of data (del.php)

We have already written it on the main page and passed in the id
We Use the sql statement to delete according to the incoming id
Delete complete redirection

<?php // 1. 接收传递过来的id
    if(empty($_GET[&#39;id&#39;])){
        exit(&#39;<h1>连接数据库失败');
    }
    $id = $_GET['id'];// 2. 连接数据库
    $link = mysqli_connect('127.0.0.1', 'root', '', 'students');
    mysqli_query($link,"set names 'utf8';");// 3. 删除该条数据
    $query = mysqli_query($link,"delete from stu where id = {$id}");// 4. 查询失败的处理
    if (!$query) {
        exit('<h1>查询数据失败</h1>');
    }// 5. 受影响的行数
    $affected_rows = mysqli_affected_rows($link);// 6. 删除失败
    if ($affected_rows 删除失败');
    }
    header('Location: index.php');?>

Copy after login

Modify operation

Receive the index.php passed id, and then query the data based on the id (the id is unique)
Render the data to the interface
Query the data through the id link database

    if(empty($_GET['id'])){
        exit('<h1>必须传入指定参数</h1>');
        return;
    }
    $id = $_GET['id'];
    $link = mysqli_connect('127.0.0.1', 'root', '', 'students');
    mysqli_query($link,"set names 'utf8';");
    if(!$link){
        exit('<h1>连接数据库失败</h1>');
    }
    $query = mysqli_query($link,"select * from stu where id = {$id} limit 1");
    if(!$query){
        exit('<h1>查询数据失败</h1>');
    }
    $user = mysqli_fetch_assoc($query);
    if(!$user){
        exit('<h1>找不到你要编辑的数据</h1>');
    }

Copy after login

Interface data rendering

<p>
    </p><h4>添加Use PHP+Mysql to implement addition, deletion, modification and query in ten minutes (detailed example)信息</h4>

Copy after login

Result (id should be hidden in production environment)
post submit data and modify data according to id

<?php var_dump($_POST);
$id = $_POST["id"];
$name = $_POST[&#39;name&#39;];
$age = $_POST[&#39;age&#39;];
$gender = $_POST[&#39;gender&#39;];
$link = mysqli_connect(&#39;127.0.0.1&#39;, &#39;root&#39;, &#39;&#39;, &#39;students&#39;);
mysqli_query($link,"set names &#39;utf8&#39;;");
if(!$link){
    exit(&#39;<h1>连接数据库失败');
}
//$query = mysqli_query($link,"update stu set name={$name},age={$age},gender={$gender} where id = {$id};");
var_dump("UPDATE stu SET gender={$gender},age={$age},name='{$name}' WHERE id={$id}");
$query = mysqli_query($link,"UPDATE stu SET gender={$gender},age={$age},name='{$name}' WHERE id={$id}");
if (!$query) {
    exit('<h1>查询数据失败</h1>');
}
$affected = mysqli_affected_rows($link);
if($affected!==1){
    exit('<h1>找不到你要编辑的数据</h1>');
}
header('Location:index.php');
?>

Copy after login

If you are interested, you can click on "PHP Video Tutorial" to learn more about PHP knowledge.

The above is the detailed content of Use PHP+Mysql to implement addition, deletion, modification and query in ten minutes (detailed example). For more information, please follow other related articles on the PHP Chinese website!