Ajax and MySQL data interactive message board function (all)

Time:2020-1-19

Recently, I have made a small demo to realize the data interaction between Ajax and mysql. In the JS part, JQ is used, PHP is used in the background, MySQL is used as the database, and a node + mongodb version will be used later.

About the use and installation of MySQL, I won’t talk much about it. Baidu xampp, Apache server and MySQL database integration are very easy to use.

First of all, open the server and database. Here I build a “eleven” database, and then I build a table called microblog (please note: I use a higher version of MySQL, in which the PHP link database method uses mysqli_. If the version is too low, please use the mysql_method to modify the code by yourself)
Here is the code part:

HTML page and JS part:

<!DOCTYPE html> 
<html> 
  <head> 
    <meta charset="UTF-8"> 
    < title > microblog message board < / Title > 
    <style type="text/css"> 
      *{ 
        margin: 0; 
        padding: 0; 
      } 
      #box{ 
        width: 600px; 
        /*height: 500px;*/ 
        border: 2px solid rgb(85,85,85); 
        border-radius: 15px; 
        margin: 50px auto; 
        padding: 20px 10px 15px; 
        background-color: rgb(85,85,85); 
      } 
      #content{ 
        display: block; 
        resize: none; 
        width: 550px; 
        height: 200px; 
        margin: 0 auto; 
        border: 2px solid rgb(225,225,225); 
        border-radius: 10px; 
        text-align: center; 
        font-size: 30px; 
        background-color: rgb(225,225,225); 
      } 
      #content:focus{ 
        outline: none; 
        border: 2px solid rgb(225,225,225); 
        box-shadow: 0 0 15px rgb(225,225,225); 
      } 
      #btn{ 
        border: 2px solid rgb(255,204,0); 
        width: 80px; 
        height: 40px; 
        border-radius: 5px; 
        margin-top: 30px; 
        font-size: 17px; 
        cursor: pointer; 
        outline: none; 
        background-color: rgb(255,204,0); 
      } 
       
      .list{ 
        list-style: none; 
        background-color: rgb(249,249,249); 
        margin-top: 20px; 
      } 
      .list>li{ 
        padding: 20px 10px 10px; 
        border-bottom: 2px solid rgb(68,68,68); 
        font-size: 20px; 
        color: rgb(200,214,225); 
        position: relative; 
        word-break: break-word; 
        word-wrap: break-word; 
        background-color: rgb(85,85,85); 
         
      } 
      .list>li>.control{ 
        position: absolute; 
        bottom: 3px; 
        right: 5px; 
        font-size: 14px; 
      } 
      .list>li>p{ 
        margin-bottom: 25px; 
      } 
      .control span,.control em{ 
        display: inline-block; 
        margin-right: 15px; 
      } 
      .control em{ 
        color: darkblue; 
        cursor: pointer; 
      } 
      a{ 
        text-decoration: none; 
        color: darkred; 
      } 
      #page>a{ 
        display:inline-block; 
        width: 40px; 
        height: 30px; 
        margin-top: 10px; 
        text-align: center; 
        line-height: 30px; 
        font-size: 20px; 
        border-radius: 5px; 
        color: white; 
        background-color: rgb(51,21,70); 
      } 
      #head{ 
        color: rgb(200,214,225); 
        font-size: 30px; 
        height: 50px; 
        border-bottom: 2px solid rgb(68,68,68); 
        margin-bottom: 20px; 
      } 
    </style> 
  </head> 
  <body> 
    <div> 
      <div> 
        Message Board 
      </div> 
      <div> 
        <textarea></textarea> 
        < button > submit message < / button > 
      </div> 
      <! -- message list -- > 
      <div> 
        <ul> 
        </ul> 
      </div> 
      <! -- paging -- > 
      <div> 
        <a href="javasript:void(0)" rel="external nofollow" rel="external nofollow" rel="external nofollow" >1</a> 
        <a href="javasript:void(0)" rel="external nofollow" rel="external nofollow" rel="external nofollow" >2</a> 
      </div> 
    </div> 
  </body> 
  <script src="Jq/jquery-3.1.1.min.js"> 
</html>

The code can’t be displayed completely. Here is the part of PHP code.
Connected to section 01, AJAX request of JQ:

<script type="text/javascript"> 
    $(function(){ 
      $("#btn").on("click",function(){ 
        if ($("#content").val() == "") { 
          Alert ("~ ~ my guest, just say a word and go again);"; 
          return; 
        }  
        else{ 
          $.ajax({ 
            type:"get", 
            url:"http://localhost/phpStudy/ajax03/message.php", 
            async:true, 
            dataType:"json", 
            data:{ 
              content:$("#content").val(), 
              act:"add" 
            }, 
            success:function(data){ 
//             var result = JSON.parse(data); 
              if (data.error==0) { 
                createLi(data.id,$("#content").val(),data.time); 
              } else{ 
                alert(data.msg); 
              } 
            } 
          }); 
        } 
         
      }); 
       
      //Create node 
      function createLi(id,content,time){ 
        Var HTML = $('< li > < p >' + content + '< / P > < div > < span > time:' + time + '< / span > Top: < EM > 0 < / EM > step on: < EM > 0 < / EM > < a href = "javasript: void (0)" rel = "external nofollow" rel = "external nofollow" rel = "external nofollow" > delete < / a > < div > < li >'; 
          $(".list").prepend(html); 
          var h = html.height(); 
          html.height(0); 
          html.stop().animate({ 
            height:h 
          },300); 
          // delete 
          html.find(".remove").on("click",function(){ 
            html.stop().animate({ 
              height:0 
            },300,function(){ 
              html.remove(); 
            }) 
          }); 
      } 
    }) 
  </script>

This part is the PHP code part:

<?php 
  header("Content-type:text/html;charset=utf8"); 
   date_default_timezone_set("PRC"); 
   //Linked database 
   $link = mysqli_connect("localhost", "root", "", "eleven"); 
   //Format database encoding 
   mysqli_query($link, "set names utf8"); 
?>

Note: I wrote this part as public code, because I called it when I learned to do other things, so the following code will have


include_once "comment.php"; 

This line refers to other code

<?php 
  /* 
   *Development documents 
   *1. Store the submitted message 
   * url:http://localhost/phpStudy/ajax03/message.php 
   *Submit by: get 
   *Submit Parameter Description: 
   *Content must be the message board content 
   *Act must be identified as an excuse 
   *Return parameter description 
   *Success: {"error": "0", "Id": 1, "time": "2016-11-30"} 
   *Failed: {"error": 1, "MSG": "message failed, please try again"} 
   * 
   * 2.up 
   *Id the ID number of the required message 
   *Act must identify up 
   *Return statement 
   *Success: {"error": "0"} 
   *Failed: {"error": "1", "MSG": "like failed"} 
   * 
   *3. Paging (interface to get page number); 
   *Act must have interface ID count 
   *Return parameter description: 
   *Success: {"error": "0", "countpage": "3"}; 
   *Failed: {"error": "1", "MSG": "request data failed, please try again! "} 
   * 
   *4. Page (click page to jump) 
   * url:http://localhost/phpStudy/ajax03/message.php 
   *Submit by: get 
   *Submit Parameter Description: 
   *Starpage must parameter page index value 
   *Act must interface to identify page 
   *Return parameter description 
   *Success: {"error": "0", "data": [{}, {}, {}, {}, {}, {}]} 
   *Failed: {"error": "1", "MSG": "data query failed, please try again! "} 
   * 
   */ 
   include_once "comment.php"; 
   $Act = $_get ["act"]; // interface request ID 
   switch ($act) { 
    Case 'Add': // submit message 
      $content = $_GET["content"]; 
      $time = time(); 
      $times = date("Y-m-d H:i:s",$time); 
      $query = "INSERT INTO microblog(id,content,time) VALUES(null,'{$content}','{$times}')"; 
      mysqli_query($link,$query); 
      $insertId = mysqli_insert_id($link); 
      if($insertId>0){ 
        $arr = ["error"=>0,"id"=>$insertId,"time"=>$times]; 
        Echo json_encode ($ARR); // convert the array to JSON for the convenience of the front end 
      } 
      else{ 
        $arr = ["error" = > 1, "MSG" = > message failed, please try again! "]; 
        Echo json_encode ($ARR); // convert the array to JSON for the convenience of the front end 
      } 
      break; 
       
    case 'up': 
      $id = $_GET['id']; 
      $search = "SELECT up FROM microblog WHERE id = $id"; 
      $result = mysqli_query($link, $search); 
      $upNum = mysqli_fetch_row($result)[0]; 
      $upNum++; 
      $query = "UPDATE microblog SET up='{$upNum}' WHERE id = '{$id}'"; 
      mysqli_query($link,$query); 
      If (mysqli_affected_rows ($link)) {// update data succeeded 
        echo '{"error":"0"}'; 
      } 
      Else {// update failed 
        Echo '{"error": "1", "MSG": "like failed! ">"; 
      } 
      break; 
       
    case 'down': 
      $id = $_GET['id']; 
      $search = "SELECT down FROM microblog WHERE id = $id"; 
      $result = mysqli_query($link, $search); 
      $downNum = mysqli_fetch_row($result)[0]; 
      $downNum++; 
      $query = "UPDATE microblog SET down='{$downNum}' WHERE id = '{$id}'"; 
      mysqli_query($link,$query); 
      If (mysqli_affected_rows ($link)) {// update data succeeded 
        echo '{"error":"0"}'; 
      } 
      Else {// update failed 
        Echo '{"error": "1", "MSG": "failed to step on! ">"; 
      } 
      break;  
    case 'remove': 
      $id = $_GET['id']; 
      $query ="DELETE FROM microblog WHERE id='{$id}'"; 
      mysqli_query($link,$query); 
      If (mysqli_affected_rows ($link) > 0) {// data deletion succeeded 
        echo '{"error":"0"}'; 
      } 
      else{ 
        Echo '{"error": "1", "MSG": "deletion failed! ">"; 
      } 
      break; 
    Case 'count': // returns the total page number 
      $query = "SELECT count(id) FROM   microblog"; 
      $result = mysqli_query($link, $query); 
      $count = mysqli? Fetch? Row ($result) [0]; // returns the query result as an index array 
      $countPage = ceil($count/5); 
      echo '{"error":"0","countPage":"'.$countPage.'"}'; 
      break;  
    Case 'page': // click pagination or page first load 
      $index = $_GET["num"]*5; 
      $search = "select * from microblog order by ID desc limit {$Index}, 5"; // reverse query message 
      $result = mysqli_query($link, $search); 
      $arr = []; // save the queried data 
      while($row = mysqli_fetch_assoc($result)){ 
        array_unshift($arr,$row); 
      } 
//     print_r($arr); 
//     {"error":"0","info":[{},{},{},{},{}]} 
      $resultArr = ["error"=>"0","info"=>$arr]; 
      echo json_encode($resultArr); 
      break; 
     
   } 
?>

The above is the whole content of this article. I hope it will help you in your study, and I hope you can support developepaer more.

Recommended Today

Vs code usage

Common shortcut keys: Shift + Alt + F on Windows format code Shift + option + F on MAC format code Shift + Alt + a block comment Common plug-ins: [plug in installation method] Markdown Preview Enhanced Preview markdown in real time, which is necessary for users Path Intellisense Automatically prompt file path, support various […]