php connection database (1)

1. php linked database:


1. Linked database

2. Judge whether the connection is successful

3. Set character set

4. Select database

5. Prepare SQL statement

6. Send SQL statement

7. Process result set

8. Free resources (close database)

$result = mysqli [query ($link, $SQL) / / returns an object

Mysqli? Fetch? Assoc ($result) read down one by one, and return a one-dimensional Association array

Mysqli? Fetch? Row ($result) returns an index array

Mysqli? Fetch? Array ($result) returns an array with index and Association

Mysqli ﹣ num ﹣ rows ($result) returns the total number of result sets at the time of query

Mysqli ﹣ affected ﹣ rows ($link) returns the number of rows affected when you modify, delete, or add

Mysqli? Insert? id ($link) returns the self increasing id of the current data you inserted

<?php
    $link = mysqli_connect('localhost','root','');
    var_dump($link);
    //1,Connect to database
    if (!$link) {
    exit('Failed to connect to database');}
    //2,Determine whether the database connection is successful
    mysqli_set_charset($link,'utf8');
    //3,Set character set
    mysqli_select_db($link,'bbs');
    //4,Select database
    $sql = "select * from bbs_user";
    //5,Get ready sql Sentence
    $res = mysqli_query($link,$sql);
    //6,Send out sql Sentence
    $result = mysqli_fetch_assoc($res);
    $result = mysqli_fetch_assoc($res);
    //7,Process result set
    mysqli_close($link);
    //8,close database
?>

This returns an associated array.

 

 

Output all arrays: (loop)

 1 <?php
 2     $link = mysqli_connect('localhost','root','');
 3     if (!$link) {
 4     exit($'Failed to connect to database');}
 5     mysqli_set_charset($link,'utf8');
 6     mysqli_select_db($link,'bbs');
 7     $sql = "select * from bbs_user";
 8     $res = mysqli_query($link,$sql);
 9     while ($result = mysqli_fetch_assoc($res)) {
10     var_dump($result);}
11     mysqli_close($link);
12 ?>

 

 

Output an array of indexes:

 1 <?php
 2     $link = mysqli_connect('localhost','root','');
 3     if (!$link) {
 4     exit('Failed to connect to database');}
 5     mysqli_set_charset($link,'utf8');
 6     mysqli_select_db($link,'bbs');
 7     $sql = "select * from bbs_user";
 8     $res = mysqli_query($link,$sql);
 9     $result = mysqli_fetch_row($res);
     var_dump($result);
10 mysqli_close($link); 11 ?>

 

Output associative array and index array:

 1 <?php
 2     $link = mysqli_connect('localhost','root','');
 3     if (!$link){
 4     exit('Failed to connect to database');}
 5     mysqli_set_charset($link,'utf8');
 6     mysqli_select_db($link,'bbs');
 7     $sql = "select * from bbs_user";
 8     $res = mysqli_query($link,$sql);
 9     $result = mysqli_fetch_array($res);
     var_dump($result);
10 mysqli_close($link); 11 ?>

 

Total query data:

 1 <?php
 2     $link = mysqli_connect('localhost','root','');
 3     if (!$link) {
 4     exit('Failed to connect to database');}
 5     mysqli_set_charset($link,'utf8');
 6     mysqli_select_db($link,'bbs');
 7     $sql = "select * from bbs_user";
 8     $obj = mysqli_query($link,$sql);
 9     $res = mysqli_num_rows($obj);
10     var_dump($res);
11     mysqli_close($link);
12 
13 ?>

 

Insert new data in php:

 1 <?php
 2     $link = mysqli_connect('localhost','root','');
 3     if (!$link) {
 4     exit('Failed to connect to database');}
 5     mysqli_set_charset($link,'utf8');
 6     mysqli_select_db($link,'bbs');
 7     $sql = "insert into bbs_user values(9,'kkk','789789','nanjian',2,15)";
 8     $obj = mysqli_query($link,$sql);
 9     $res = mysqli_insert_id($link);
10     var_dump($res);
11     mysqli_close($link);
12 ?>

 

 1 <?php
 2     $link = mysqli_connect('lcoalhost','root','');
 3     if (!$link) {
 4     exit('Failed to link database');}
 5     mysqli_set_charset($link,'utf8';)
 6     mysqli_select_db($link,'bbs');
 7     $sql = "select * from bbs_user";
 8     $obj = mysqli_query($link,$sql);
 9     echo '<th>number</th><th>User name</th><th>address</th><th>Gender</th><th>Age</th>';
10     while ($res = mysqli_fetch_assoc($obj)) {
11     echo '<tr>';
12        echo '<td>'.$res['id'].'</td>';   
13        echo '<td>'.$res['username'].'</td>';
14        echo '<td>'.$res['address'].'</td>';
15        echo '<td>'.$res['sex'].'</td>';
16        echo '<td>'.$res['age'].'</td>';
17        echo '<td><a href="del.php?id='.$res['id'].'">delete</a>/<a href="update.php?id='.$res['id'].'">modify</a></td>';
18     echo '</tr>';}        
19 ?>

 

Compile the deleted php file: (del.php)

 1 <?php
 2     $id=$_GET['id'];
 3     $link = mysqli_connect('localhost','root','');
 4     if (!$link) {
 5     exit('Failed to connect to database');}
 6     mysqli_set_charset($link,'utf8');
 7     mysqli_select_db($link,'bbs');
 8     $sql = "delete from bbs_user where id=$id";
 9     $boolearn = mysqli_query($link,$sql);
10     if ($boolearn && msyqli_affected_rows($link)) {
11     echo 'Delete successful';} else {
12     echo 'Delete failed';}
13     mysqli_close($link);
14 ?>

Compile the modified php file: (update.php)

 1 <?php
 2     $id = $_GET['id'];
 3     $link = mysqli_connect('localhost','root','');
 4     if (!$link) {
 5     exit('Failed to connect to database');}
 6     mysqli_set_charset($link,'utf8');
 7     msyqli_select_db($link,'bbs');
 8     $sql = "select * from bbs_user where id=$id";
 9     $obj = mysqli_query($link,$sql);
10     $rows = mysqli_fetch_assoc($obj);
11 ?>
12 <html>
13     <form action =" doupdate.php">
14             <input type="hidden" value="<?php echo $id;?>" name="id" />
15             User name:<input type="text" value="<?php $rows=['username'] ?>" name="username"/><br />
16             address:<input type="text" value="<?php $rows=['address'] ?>" name="address" /><br />
17             Gender:<input type="text" value="<?php $rows=['sex'] ?>" name="sex" />
18 <br />
19             Age:<input type="text" value="<?php $row=['age']>" name="age" />
20             <input type="submit" value="Execution modification" /> 
21     </form>
22 </html>        

doupdate.php:

1 <?php
2     var_dump($_GRT);
3 ?>

doupadate.php

 1 <?php
 2     $id = $_GET['id'];
 3     $username = $_GET['username'];
 4     $address = $_GET['adress'];
 5     $sex = $_GET['sex'];
 6     $age = $_GET['age'];
 7     $link = mysqli_connect('lcoalhost','root','');
 8     if (!$link) {
 9     exit('Database connection failed');}
10     mysqli_set_charset($link,'utf8');
11     mysqli_select_db($link,'bbs');
12     $sql = "update bbs_user set username='$username', address='$address', 
13     sex='$sex', age='$age' where id='$id'";
14     $res = mysqli_query($link,$sql);
15     if ($res && mysqli_affected_rows($link)) {
16     echo 'Modified success<a href="update.php">Return</a>';}
17     else {
18     echo 'Modification failed';}
19     mysqli_close($link);
20 ?> 

Tags: PHP SQL Database

Posted on Sun, 09 Feb 2020 07:11:41 -0800 by markhard