How to read query data from mysql database in php

php method to read queried data from the mysql database: 1. Connect to the mysql database through mysqli_connect; 2. Set the character set encoding format; 3. Execute the SQL statement; 4. Process the result set.

The operating environment of this article: windows7 system, PHP7.1 version, DELL G3 computer

How to read php from the mysql database Query data?

PHP MySql implements background data reading:

We use the php_mysqli extension of PHP

First understand some basic usage

1. Connect to the database using

mysqli_connect()
Parameters: ① Host address ② MYSQL user name ③ MYSQL password ④ Select the database to connect to ⑤ Port number
Return: Return if the connection is successful The identifier of the resource type. If it fails, it returns false
If we establish more than one connection with Mysql, then various functions that operate the database in the future must pass in the returned connection symbol
If we establish only one connection with Mysql One, then there is no need to pass in this identifier to various functions that operate the database in the future

2. Set the character set encoding format
mysqli_set_charset();

3. Execute the SQL statement
If it is addition, deletion or modification, it will return the Boolean type whether it is successful or not
If it is a query, it will return the resource result set
$res=mysqli_query();

4. Process the result set

    mysqli_fetch_assoc($res); 　　返回关联数组
    mysqli_fetch_row($res); 　　　返回索引数组
    mysqli_fetch_object($res); 　　返回对象
    mysqli_fetch_field($res); 　　   返回结果集中每一列的字段信息(字段名，表名，数据库名，字段类型)
    mysqli_data_seek($res, 0); 　  设置结果集指针位置，为零，结果集复位到最开始
    mysqli_free_result($res); 　　  释放查询资源结果集
    mysqli_close($conn);  　　　　关闭数据库连接

Let’s implement a simple registration and login function.

First mention the general configuration into a PHP file, and then import it later

<?php
    header("Content-Type:text/html;charset=utf-8");
    
    
    define("HOST", "127.0.0.1");
    define("USERNAME", "root");
    define("PASSWORD", "");
    define("DBNAME", "mydb");
    define("CHARSET", "utf8");
    
    $con=mysqli_connect(HOST, USERNAME, PASSWORD, DBNAME) or die("数据库连接失败，<span style=&#39;color:red;&#39;>".mysqli_connect_error()."</span>");
    mysqli_set_charset($con, CHARSET) or die("字符集编码设置无效");

Create a table in the database to access user information . Here I have created a table named submit in mydb database.

The first thing to do is to register the function. Registration is to save the information entered by the user into the table in the background database

The following is the style of the registration page, there is nothing to say, just remember the name ID

<p></p><div class="panel panel-primary">
<br>            <div class="panel-heading">
<br>                <div class="panel-title">用户注册</div>
<br>            </div>
<br>            <div class="panel-body">
<br>                <form class="form-horizontal">
<br>                    <div class="form-group">
<br>                        <label>用户名</label><br>                        <input type="text" class="form-control" name="userName"><br>                    </div>
<br>                    <div class="form-group">
<br>                        <label>密码</label><br>                        <input type="password" class="form-control" name="pwd"><br>                    </div>
<br>                    <div class="form-group">
<br>                        <label>确认密码</label><br>                        <input type="password" class="form-control" name="rePwd"><br>                    </div>
<br>                    <div class="form-group">
<br>                        <label>真实姓名</label><br>                        <input type="text" class="form-control" name="realName"><br>                    </div>
<br>                    <br>                    <div class="form-group btns">
<br>                        <input type="button" class="btn btn-primary" value="确定注册" id="submit"><br>                            <br>                        <a type="button" class="btn btn-success" href="login.php"></a>返回登录<br>                    </div>
<br>                    <br>                </form>
<br>            </div>
<br>        </div><br>

The key point is to see how to use JQuery to POST data to the background

<p><script src="../../js/jquery-1.10.2.js"></script><br>    <script type="text/javascript"><br/>        $(function(){<br/>            $("#submit").on("click",function(){                var userName = $("input[name=&#39;userName&#39;]").val();                var pwd = $("input[name=&#39;pwd&#39;]").val();                var rePwd = $("input[name=&#39;rePwd&#39;]").val();                var realName = $("input[name=&#39;realName&#39;]").val();                if(userName==""||pwd==""||rePwd==""||realName==""){<br/>                    alert("所有信息不可为空，请确认！");                    return;<br/>                }else if(pwd!=rePwd){<br/>                    alert("两次密码输入不一致！");                    return;<br/>                }<br/>                <br/>                $.post("doReg.php",{                    "userName":userName,                    "pwd":pwd,                    "realName":realName<br/>                },function(data){<br/>                    alert(data);                    <br/>                    if(data=="注册成功"){<br/>                        location = "login.php";<br/>                    }<br/>                })<br/>                <br/>            });<br/>        });    </script><br></p>

The php in the background After the file receives the data, it will use the SQL statement to operate the database and store the data in the table

<p><?php    header("Content-Type:text/html;charset=utf-8");    $str=$_POST["formData"];    list($username)=explode("&", $str);    list(,$pwd)=explode("&", $str);    list(,,,$realname)=explode("&", $str);    list(,$username)=explode("=", $username);    list(,$pwd)=explode("=", $pwd);    list(,$realname)=explode("=", $realname);    include_once("mysql.php");    $sql=<<<sql<br/>    insert into submit (username,pwd,realname) values ("$username","$pwd","$realname");<br>sql;    $sql2=    SELECT username FROM submit WHERE username="$username";<br>sql2;    $res2=mysqli_query($con, $sql2);    $res=mysqli_query($con, $sql);    if(mysqli_num_rows($res2)>0){        die("用户名已经存在！");  <br>    }    elseif($res){        echo 'true';<br>    }else{        die();<br>    }    <br></p>

In this way, click the registration button , the entered information can be stored in the table. After success, jump to the login page

The next thing is the login page. The login function needs to read the user name and password information stored in the table

The login page style is not much to say. Also remember the required name and ID

<div class="panel panel-primary">
            <div class="panel-heading">
                <div class="panel-title">用户登录</div>
            </div>
            <div class="panel-body">
                <form class="form-horizontal">
                    <div class="form-group">
                        <label>用户名</label>
                        <input type="text" class="form-control" name="userName"/>
                    </div>
                    <div class="form-group">
                        <label>密码</label>
                        <input type="password" class="form-control" name="pwd"/>
                    </div>
                    
                    <div class="form-group btns">
                        <input type="button" class="btn btn-primary" value="登录系统" id="submit"/>
                            
                        <a type="button" class="btn btn-success" href="reg.php"/>注册账号</a>
                    </div>
                    
                </form>
            </div>
        </div>

The focus is still on the JQ code

<p><script src="../../js/jquery-1.10.2.js"></script><br/>    <script type="text/javascript"><br/>        $(function(){<br/>            $("#submit").on("click",function(){                var userName = $("input[name=&#39;userName&#39;]").val();                var pwd = $("input[name=&#39;pwd&#39;]").val();<br/>                <br/>                $.post("doLogin.php",{                    "userName":userName,                    "pwd":pwd<br/>                },function(data){<br/>                    alert(data);                    if(data=="登录成功"){<br/>                        location = "index.php";<br/>                    }else{<br/>                        alert("用户名或密码有误！");<br/>                    }<br/>                });<br/>            });<br/>        });    </script><br/></p>

What the JQ code of the above landing page does is, take Go to the backend to log in to the PHP file. Compare the username and password information read from the database with the new one entered by the user. If true, the login is successful.

So how to write the backend login page? It is very simple. From the SQL statement After reading the information from the table, return to the front desk login page

<p><?php    <br/>header("Content-Type:text/html;charset=utf-8");include_once("../mysql/mysql.php");    <br/>    $userName = $_POST["userName"];    $pwd = $_POST["pwd"];    <br/>    $loginSql = <<<login<br/>    select * from submit where username="{$userName}" and pwd = "{$pwd}";<br/>login;    $res = mysqli_query($con, $loginSql);    <br/>    if($row = mysqli_fetch_row($res)){        $_SESSION["user"] = $row;        echo "登录成功";<br/>    }else{        echo "登录失败";<br/>    }    <br/>    mysqli_free_result($res);    mysqli_close($con);<br/></p>

After successful login, it will prompt that the login is successful and jump to the home page (index.html)

Recommended learning: "PHP video tutorial》

The above is the detailed content of How to read query data from mysql database in php. For more information, please follow other related articles on the PHP Chinese website!