php connect and operate mysql database

php connects to mysql database

Five steps for PHP to access MYSQL database

1. Connect to MySQL database

Using MySQL_ The connect() function establishes a connection to the MySQL server.

2. Select MySQL database

Using MySQL_ select_ The db() function selects the database of the MySQL database server.

3. Execute SQL statement

Using MySQL in the selection database_ The query() function executes SQL statements. There are five ways to operate data. We will introduce them respectively.

  • Query data: use select statement to query data.

  • Display data: use the select statement to display the query results of the data.

  • Insert data: use the insert into statement to insert data into the database.

  • Update data: use the update statement to update the records in the database.

  • Delete data: use delete statement to delete records in database!

4. Close result set

After the database operation is completed, you need to close the result set to release system resources, and use mysql_free_result($result);

5. Shut down MySQL server

Every time MYSQL is used_ Connect() or MYSQL_ The query() function will consume system resources. If the number of user connections exceeds a certain amount, the system performance will decline, or even crash. To avoid this phenomenon, MYSQL should be used after completing the database operation_ The close() function closes the connection to the MYSQL server to save system resources.

 

skill:

If you need to visit the database frequently in multiple web pages, you can establish a continuous connection with the database server to improve the efficiency, because each connection with the database server takes a long time and a large amount of resource overhead, and the continuous connection is relatively more efficient. The way to establish a continuous connection is to call the function MySQL when the database is indirect_ Pconnect() instead of mysql_connect function. The established continuous connection does not need to call MySQL at the end of this program_ Colse () to close the connection to the database server. Next time the program executes MySQL here_ When pconnect() function is used, the system automatically returns the established continuous connection ID number directly instead of connecting to the database.

 

In PHP, the connection to the database is non persistent, and the system will automatically recycle it. In general, it is not necessary to close it. However, if the result set of one-time visit is relatively large, or the number of site visits is relatively large, MySQL is the best choice_ The close() function releases manually.

 

Using MySQL_ The connect() function connects to the database

//Create connection
$conn = new mysqli('localhost','root','123456','test');
//Test connection
if($conn->connect_error){
    die('Connection failed:'.$conn->connect_error);
}

 

Using mysql_select_db() function select database file

The following example uses MySQL_ select_ The db() function connects to the database. The specific example code is as follows:

$link = mysqli_connect('localhost','root','123456') or die('Unable to connect to database').mysqli_error();
$conn = mysqli_select_db($link,'test');
if($conn){
    echo 'Database connection successful';
}

 

 

The above code $conn = mysqli_select_db($link,'test'); you can use the following code instead:

$conn = mysqli_query($link,'use test');

 

Using mysql_query() function executes SQL statement

The following examples illustrate the usage of common SQL statements.

<?php

$conn = mysqli_connect('localhost','root','123456','test') or die('Unable to connect to database').mysqli_error();

//Add member record
$sql = "INSERT INTO login(username,password,confirm,email) VALUES ('cyy2','123456','123456','965794175@qq.com')";
if(mysqli_query($conn,$sql)){
    echo 'Insert successful';
}else{
    echo 'error:'.$sql.'<br>'.mysqli_error($conn);
}

//Modify member record
$sql = "UPDATE login SET username = 'cyy' where id = '3'";
if(mysqli_query($conn,$sql)){
    echo 'Update successful';
}else{
    echo 'error:'.$sql.'<br>'.mysqli_error($conn);
}

//Delete member record
$sql = "DELETE FROM login WHERE id = '4' ";
if(mysqli_query($conn,$sql)){
    echo 'Delete succeeded';
}else{
    echo 'error:'.$sql.'<br>'.mysqli_error($conn);
}

//Query member records
$sql = "SELECT * FROM login";
if(mysqli_query($conn,$sql)){
    echo 'query was successful';
}else{
    echo 'error:'.$sql.'<br>'.mysqli_error($conn);
}

//Display table structure
$sql = "DESC login";
if(mysqli_query($conn,$sql)){
    echo 'Show success';
}else{
    echo 'error:'.$sql.'<br>'.mysqli_error($conn);
}

 

Using mysql_fetch_array() gets the information in the array result set

mysql_ fetch_ The array() function takes a row from the result set as an associative array, or a number array, or both return an array generated from the row obtained from the result set, or false if there are no more rows.

be careful:

mysql_ fetch_ The field names returned by the array() function are case sensitive.

The specific development steps are as follows:

1. Create a PHP dynamic page and name it index.php , on index.php Add a form, a text box and a submit button in the following code:

<html>

<body>

    <!--Upload file form-->

    <form method="post" action="" name = form1>

        <table>

           <tr>

               <td width="605" height="51" bgcolor="#CC99FF">

                   <p align="center">Please enter the query content

                       <input type="text" name="username" id="username" size="25">&nbsp;

                       <input type="submit" name="Submit" value="query">

                   </p>

               </td>

           </tr>

            </table>

        </form>

</body>

</html>

The page is as follows:

 

 

2. Connect to MySQL database server. The specific code is as follows:

<?php

header("Content-Type:text/html; charset=utf-8");

//Connect to database
$link = mysqli_connect("localhost","root","123456","test")or die("Failed to connect to database".mysqli_error());
mysqli_query($link,"set names utf-8");   //Set the code to prevent the occurrence of garbled code

?>

 

3. Use if condition statement to judge whether the user clicks "query" button. If so, use POST method to accept the transferred information and execute sql statement, which is mainly used to realize fuzzy query of information, and the query result is given variable $sql. Then get the information from the array result set. The specific code is as follows:

//Fuzzy query
$sql = mysqli_query($link,"SELECT * FROM login");
$info = mysqli_fetch_array($sql);
if($_POST['submit'] == 'query'){
  $username = $_POST['username'];
  $sql = mysqli_query($link,"SELECT * FROM login WHERE username LIKE '%".trim($username)."%' ");
  $info = mysqli_fetch_array($sql);
}

 

4. Use if condition statement to judge the result set variable $info. If the value is false, the information retrieved by using echo statement output does not exist. The specific code is as follows:

//Judgment result
if(!$info){
  echo "<p align='center' style='color: #FF0000;font-size: 12px'>Sorry, the query information does not exist</p>";
}

 

5. Use the do...while loop statement to output the information in the array result set $info [] in the form of a table. The name of a field is the index. Use the echo statement to output the information of the array $info []. The specific code is as follows:

//Cycle output result
do {      //do...while loop

    ?>

    <table>

        <tr align="left" bgcolor="#FFFFFF">

            <td height="20" align="center"><?php echo $info["id"] ?></td>

            <td height="20" align="center"><?php echo $info["username"] ?></td>

            <td height="20" align="center"><?php echo $info["password"] ?></td>

            <td height="20" align="center"><?php echo $info["confirm"] ?></td>

            <td height="20" align="center"><?php echo $info["email"] ?></td>

        </tr>

    </table>

    <?php

}while($info = mysqli_fetch_array($sql));

 

The output results are as follows:

 

 

Using mysql_fetch_row() function obtains each record in the result set line by line

<html>

<body>

    <!--Upload file form-->

    <form method="post" action="" name = form1>

        <table>

           <tr>

               <td width="605" height="51" bgcolor="#CC99FF">

                   <p align="center">Please enter the query content

                       <input type="text" name="username" id="username" size="25">&nbsp;

                       <input type="submit" name="submit" value="query">

                   </p>

               </td>

           </tr>

            </table>

        </form>

<?php

header("Content-Type:text/html; charset=utf-8");
error_reporting(0);

//Connect to database
$link = mysqli_connect("localhost","root","123456","test")or die("Failed to connect to database".mysqli_error());
mysqli_query($link,"set names utf-8");   //Set the code to prevent the occurrence of garbled code


//Fuzzy query
$sql = mysqli_query($link,"SELECT * FROM login");
$info = mysqli_fetch_row($sql);
if($_POST['submit'] == 'query'){
  $username = $_POST['username'];
  $sql = mysqli_query($link,"SELECT * FROM login WHERE username LIKE '%".trim($username)."%' ");
  $info = mysqli_fetch_row($sql);
}

//Judgment result
if(!$info){
  echo "<p align='center' style='color: #FF0000;font-size: 12px'>Sorry, the query information does not exist</p>";
}

//Cycle output result
do {      //do...while loop

    ?>

    <table>

        <tr align="left" bgcolor="#FFFFFF">

            <td height="20" align="center"><?php echo $info[0] ?></td>

            <td height="20" align="center"><?php echo $info[1] ?></td>

            <td height="20" align="center"><?php echo $info[2] ?></td>

            <td height="20" align="center"><?php echo $info[3] ?></td>

            <td height="20" align="center"><?php echo $info[4] ?></td>

        </tr>

    </table>

    <?php

}while($info = mysqli_fetch_row($sql));

?>

</body>

</html>

Tags: PHP SQL Database MySQL

Posted on Sun, 24 May 2020 23:44:39 -0400 by qbox