First introduction to general database operation classes - front-end easyui-datagrid, form (php)

First introduction to the general database operation class - front-end easyui-datagrid, form (php), the implementation code is relatively simple, please see below for the specific implementation steps.

Implementation function:

The datagrid on the left displays brief information, and the right displays detailed information on the selected row. Database additions, deletions, and modifications

(1) Click on the selected row, and detailed information is displayed on the right , where the [Add], [Modify] and [Delete] buttons are available, the [Save] button is disabled

(2) Click the [Add] button, the [Modify] and [Delete] buttons are disabled, and the [Save] button 】 button is enabled

(3) Click the [Modify] button, [Add], and [Delete] buttons are disabled

Difficulty: insert method and update method in the general database operation class

Final rendering:

The front-end function is not very complete, and there are still some problems with the logic between buttons. Finally, the front-end code is added

Formain.php judges the value passed to the front-end and calls actSQL. class.php gets the result

The code is relatively simple

is as follows:

<?php
  require(&#39;include/mysql_connect/actSQL.class.php&#39;);
  $key=$_REQUEST[&#39;key&#39;];
  $a=new actSQL(&#39;localhost&#39;,&#39;root&#39;,&#39;1234&#39;,&#39;tpss&#39;);
  //获取信息
  if($key==&#39;1&#39;)
  {
   $a->getAllData(&#39;t_prekeychart&#39;);
  }
  if($key==&#39;2&#39;)
  { 
   $objectstr=$_REQUEST[&#39;object&#39;];   
   if($a->insertData($objectstr,&#39;t_prekeychart&#39;))
   {
    echo json_encode("true");
   }else{
    echo json_encode("false");
   }
   //test
   //$test=&#39;{"keychartid":"2","keyid":"2","keychartname":"2","level":"2","showtype":"2","helptips":"2","keylevel":"2","ishmap":"2"}&#39;;
   //$a->insertData($test,&#39;t_prekeychart&#39;);
  }
  if($key==&#39;3&#39;)
  { 
   $prekey=$_REQUEST[&#39;keychartid&#39;];
   $prekeyname=&#39;keychartname&#39;;
   if($a->delData($prekey,$prekeyname,&#39;t_prekeychart&#39;))
   {
    echo json_encode("true");
   }else{
    echo json_encode("false");
   }
  }
  if($key==&#39;4&#39;)
  {
   $objectstr=$_REQUEST[&#39;object&#39;];
   $prekeyname=&#39;keychartid&#39;;
   if($a->updData($objectstr,$prekeyname,&#39;t_prekeychart&#39;))
   {
    echo json_encode("true");
   }else{
    echo json_encode("false");
   }
  }
?>

Look at the various methods of the class that appear in Formain.php and think about it briefly

Among them,

getAllData($tablename) gets all the information of the table. This method is relatively simple. It can be done with a simple sql statement. Finally, the result can be returned in json format.

delData($prekey ,$prekeyname,$tablename) deletes the specified information. This is simpler, so I won’t go into details.

insertData($objectstr,$tablename), where $objectstr is a string in json format, $tablename table name,

The difficulty is to piece it together into a statement like insert into $tablename (...) values(...)

Solution:

(1 ) Get all the column names based on $tablename, and convert the column name array into a string to prepare for the piecing together of the final SQL statement. In addition to getting the column names, there is another purpose. See below

(2 ) Convert the string $objectstr in json format into an associative array and call the json_decode() method

Supplementary json_decode() method

mixed json_decode ( string $json [, bool $assoc = false [ , int $depth = 512 [, int $options = 0 ]]] )
Accepts a JSON format string and converts it into a PHP variable, where assoc, when the parameter is TRUE , will return an associative array .

(3) Query the data in the order of the queried column names. When the data is empty, assign the value to NULL and store the result in the array to prevent misaligned values ​​inserted into the database (another reason for obtaining column names)

(4) Convert the result of (3) into a string and call the implode() method

Supplement the implode() method:

string implode(string glue, array pieces) ;
This function combines the contents of the array into a string, and the parameter glue is the delimiter symbol between words

(5) Piece together the sql statement string, and then insert it into the database

The difficulty of the updData($objstr,$prekeyname,$tablename) method is also the string assembly of the sql statement. The assembly format should be as follows

update $tablename set ..... where $prekeyname=$data[$prekeyname ]

The first two steps are the same as insertData()

(3) Traverse the column name character array, obtain the column name value of the non-primary key name, and follow the string of "column name = column name value" The format is stored in the array, here is the incomplete string after the set

(4) Convert the result of (3) into a string, separate the array elements with ',' , this is the last part after the set The string format is "xx=xx,xx=xx"

(5) Piece together the sql string, and then update the database

insertData() and updData() functions are as follows

/*
   * 添加信息
   * @param:$objstr:json风格的数据库插入信息字符串
   *   $tablename:表名
   */
  function insertData($objstr,$tablename)
  {
    $dbc=$this->conData();
    if($dbc)
    { 
     $columnname=array();
     $columnname=$this->getColumns($tablename);
     //echo $columnname[0];
     $clos=implode(',',$columnname); //将列名数组转换为字符串
     //echo $clos;
     $data=json_decode($objstr,true); //将json格式的字符串转换为关联数组
     //echo $value['keychartname'];
     $values=array();
     foreach($columnname as $value)
     {
       //按照查询到的列名查询数据，数据为空的，赋值为NULL，防止数据库插入数值错位
       //echo $data[$value]."<br>";
       if(isset($data[$value]))
       {
         array_push($values,$data[$value]);
       }else{
        $data[$value]=NULL;
        array_push($value,$data[$value]);
       }
     }
     $strvalue=implode(&#39;,&#39;,$values);
     //echo $strvalue;
     /*
     * SQL： insert into $tablename($clos) values(...)
     */
     $sql=<<<SQL
     insert into $tablename($clos) values($strvalue);
SQL;
     //echo $sql;
     $res=mysqli_query($dbc,$sql);
     if($res)
     {
      return true;
     }else{
       return false;
     }
    }else{
      echo "连接错误!";
    } 
  }
/*
   *更新信息 
   *@param: $objstr:json风格的数据库更新信息字符串
   *   $tablename:表名
   *   $prekeyname:主键名  
   * */
  function updData($objstr,$prekeyname,$tablename)
  {
    $dbc=$this->conData();
    if($dbc)
    {
     $columnname=array();
     $columnname=$this->getColumns($tablename); 
     //$clos=implode(&#39;,&#39;,$columnname); //将列名数组转换为字符串
     $data=json_decode($objstr,true); //将json格式的字符串转换为关联数组
     $sets=array();
     foreach($columnname as $value)
     { 
      //列名不等于主键名获取值
      if($prekeyname!=$value)
      {
        //set $value=$data[$value];
        array_push($sets,"$value=$data[$value]");//接好的set语句部分
      }
     }
     //$sets数组转化为字符串
     $stringsets=implode(&#39;,&#39;,$sets);
     //echo $stringsets;
     /*
     * SQL:update $tablename set ..... where $prekeyname=$data[$prekeyname]; 
     * */
     $sql=<<<SQL
      update $tablename set $stringsets where $prekeyname=$data[$prekeyname];
SQL;
     $res=mysqli_query($dbc,$sql);
     if($res)
     {
      return true;
     }else{
      return false;
     }
    }else{
      echo "连接错误";
    }
  }
/*
   *获取表的所有列名
   *@param:$tablename:表名
   */
  function getColumns($tablename)
  { 
   $dbc=@mysqli_connect(&#39;localhost&#39;,&#39;root&#39;,&#39;1234&#39;,&#39;information_schema&#39;);
   if(!$dbc)
   {
    echo "Connect Error".mysqli_connect_error($dbc);
   }else
   {
    //连接成功,从表COLUMNS获取表的所有列名
    $sql="select COLUMN_NAME from columns where TABLE_NAME=&#39;$tablename&#39;";
    $res=@mysqli_query($dbc,$sql);
    $items=array();
    if($res)
    {
     while($row=mysqli_fetch_array($res,MYSQLI_ASSOC))
     {
      $columnname=$row[&#39;COLUMN_NAME&#39;];
      array_push($items,$columnname);
     }
    return $items;
    mysqli_close($dbc);
    }
    else{
     echo "查询失败，请检查SQL语句!";
     }
   }
  }

The above is the entire content of this article, I hope you all like it.

For more articles related to the general database operation class - front-end easyui-datagrid, form (php), please pay attention to the PHP Chinese website!