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

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.

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


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

Insert a piece of information

INSERT INTO syntax

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

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

2. Need to specify the column name and inserted value

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

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

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

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

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

Query statement

SQL SELECT statement

SELECT column_name,column_name FROM table_name;

SELECT * FROM table_name;

Query id column

select id from stu;

Query the statement when the id is 1

select * from stu where id = 1;

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

select * from stu where id = 1 limit 1;

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;

Modify the name when the id is 1

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

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;

Delete the student information with ID 2

delete from stu where id = 2;

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 id="连接数据库失败">连接数据库失败</h1>');
}
// 每次只能查询一条数据
$query = mysqli_query($connection, 'select * from stu;');
// 查询所有的数据
while ($row = mysqli_fetch_assoc($query)) {
    var_dump($row);
}

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

1. 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.渲染数据
?>

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

<link>

3. 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 id="首页">首页</h1>
    

                                                                                                                                                                                                                                

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

    添加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))

1. We still use the mixed mode, form data Submit to this page, use $_SERVER['PHP_SELF'] to make the code more robust
2. Use post to submit data, remember to prompt information errors on the page
3. 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();}?>

4. Interface

<p>
    </p><h4 id="添加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)信息</h4>
    

">         

            姓名             

                             

                 

            性别             

                             

                 

            年龄             

                             

                                   

                     

                 保存     

5. 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)

1. We have already written it on the main page and passed in the id
2. We Use the sql statement to delete according to the incoming id
3. 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 id="查询数据失败">查询数据失败</h1>');
    }// 5. 受影响的行数
    $affected_rows = mysqli_affected_rows($link);// 6. 删除失败
    if ($affected_rows 删除失败');
    }
    header('Location: index.php');?>

Modify operation

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

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

4. Interface data rendering

<p>
    </p><h4 id="添加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)信息</h4>
    

        

            id             

                ">             

                 

            姓名             

                ">             

                 

            性别             

                ">             

                 

            年龄             

                ">             

                 保存     

5. Result (id should be hidden in production environment)
   
6. 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 id="查询数据失败">查询数据失败</h1>');
}
$affected = mysqli_affected_rows($link);
if($affected!==1){
    exit('<h1 id="找不到你要编辑的数据">找不到你要编辑的数据</h1>');
}
header('Location:index.php');
?>

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!