个人中心
gpt4 book ai didi

php - 使用 "Grouped by"语句在数据表(服务器端)sql 查询中搜索

转载 作者：行者123 更新时间：2023-11-29 21:40:44 25 4
gpt4 key购买 nike

我正在使用datatable jQuery 插件，用于呈现 MySQL 数据库中的多个连接表。我能够根据需要呈现表格。但搜索不起作用。

观察:
当我尝试使用“WHERE”子句在 dbms 中运行查询时，我发现“WHERE”子句只有在“GROUP BY”语句之前才会运行。因此，在我的代码中，它还必须位于“GROUP BY”语句之前，而 $sql 在“GROUP BY”语句之后连接“WHERE”子句。我不知道如何在运行时在“GROUP BY”语句之前插入 WHERE 子句(带有搜索值)。

我正在提供两个文件的代码，(1) Index.php(包含表的文件和将 ajax 请求发送到 response.php 文件的 js)。 (2) response.php(包含sql和搜索代码的文件，将json编码数据发送到index.php)。我正在使用数据表版本 1.10.10。我正在关注这个tutorial

以下是我的代码:

index.php

<head>
...
    <link href="css/bootstrap.css" rel="stylesheet" type="text/css">
    <link href="css/customize.css" rel="stylesheet" type="text/css" media="screen">
    <link rel="stylesheet" type="text/css" href="css/font-awesome.css">
    <link rel="stylesheet" type="text/css" href="css/dataTables.bootstrap.css">
    <link rel="stylesheet" type="text/css" href="css/jquery.dataTables.css">
...
</head>
<body>
...
  <div class="row">
    <div id="" class="col-lg-12 col-md-12 col-sm-12 col-xs-12">
      <table id="employee_grid" class="display table-bordered">
        <thead>
          <tr>
             <th class="col-lg-2 col-md-2 col-sm-2 col-xs-2">Student Name</th>
             <th class="col-lg-1 col-md-1 col-sm-1 col-xs-1">Gender</th>
             <th class="col-lg-1 col-md-1 col-sm-1 col-xs-1">City</th>
             <th class="col-lg-3 col-md-3 col-sm-3 col-xs-3">Course Description</th>
             <th class="col-lg-2 col-md-2 col-sm-2 col-xs-2">Subject</th>
             <th class="col-lg-1 col-md-1 col-sm-1 col-xs-1 text-right">Scholarship</th>
             <th class="col-lg-2 col-md-2 col-sm-2 col-xs-2">View Details</th>
          </tr>
        </thead>
       </table>
     </div>
   </div>
...
<script>
$( document ).ready(function() {
    $('#employee_grid').DataTable({
        "bProcessing": true,
        "serverSide": true,
        "autoWidth": true,
        "stateSave": true,
        "lengthMenu": [ 10, 25, 50, 100 ],
        "ajax":{
                url :"response_b.php", // json datasource
                type: "post",  // type of method,GET/POST/DELETE
                error: function(){
                $("#employee_grid_processing").css("display","none");
                }
            },
        "columnDefs": [ {
        "targets": 6,
        "data": "StudentID",
        "render": function ( data, type, full, meta ) {
        return '<a href="beneficiary.php?StudentID="'+data+'">'+data+'</a>';
        }
     }]
  });   
});
</script>
</body>

response.php

<?php
    //include connection file 
    include_once("connection.php");

    // initilize all variable
    $params = $columns = $totalRecords = $data = array();

    $params = $_REQUEST;

    //define index of column
    $columns = array( 
        0 => '`Full Name`',
        1 => 'Gender', 
        2 => 'CityName',
        3 => 'CourseDescriptionLong',
        4 => '`Subject`',
        5 => 'ScholarshipAwarded',
        6 => 'StudentID'
    );

    $where = $sqlTot = $sqlRec = "";

    // check search value exist
    if( !empty($params['search']['value']) ) {   
        $where .=" WHERE ";
        $where .=" (`Full Name` LIKE '".$params['search']['value']."%' ";    
        $where .=" OR CityName LIKE '".$params['search']['value']."%' ";

        $where .=" OR CourseDescriptionLong LIKE '".$params['search']['value']."%' )";
    }

    // getting total number records without any search
     $sql = "
        SELECT fullnames.`full name`, 
           studentdetails.gender, 
           lt_cities.cityname, 
           lt_coursedescription.coursedescriptionlong, 
           lt_coursesubject.`subject`, 
           Sum(scholarshipdetails.scholarshipawarded), 
           studentdetails.studentid, 
           coursedetails.coursetype, 
           lt_coursedescription.coursedescriptionshort, 
           scholarshipdetails.scholarshipyear 
    FROM   studentdetails 
           INNER JOIN scholarshipdetails 
                   ON studentdetails.studentid = scholarshipdetails.studentid 
           INNER JOIN coursedetails 
                   ON studentdetails.studentid = coursedetails.studentid 
                      AND scholarshipdetails.scholarshipyear = 
                          coursedetails.scholarshipyear 
           LEFT JOIN lt_coursedescription 
                  ON coursedetails.courseid = lt_coursedescription.courseid 
           INNER JOIN tuitionfeedetails 
                   ON studentdetails.studentid = tuitionfeedetails.studentid 
                      AND scholarshipdetails.scholarshipyear = 
                          tuitionfeedetails.scholarshipyear 
           INNER JOIN fullnames 
                   ON studentdetails.studentid = fullnames.studentid 
           INNER JOIN lt_cities 
                   ON lt_cities.cityid = studentdetails.city 
           LEFT JOIN lt_coursesubject 
                  ON lt_coursesubject.courseid = lt_coursedescription.courseid 
                     AND lt_coursesubject.subjectid = coursedetails.coursesubject 
    GROUP  BY studentdetails.studentid";
    $sqlTot .= $sql;
    $sqlRec .= $sql;
    //concatenate search sql if value exist
    if(isset($where) && $where != '') {

        $sqlTot .= $where;
        $sqlRec .= $where;
    }


    $sqlRec .=  " ORDER BY ". $columns[$params['order'][0]['column']]."   ".$params['order'][0]['dir']."  LIMIT ".$params['start']." ,".$params['length']." ";

    $queryTot = mysqli_query($conn, $sqlTot) or die("database error:". mysqli_error($conn));


    $totalRecords = mysqli_num_rows($queryTot);

    $queryRecords = mysqli_query($conn, $sqlRec) or die("error to fetch employees data");

    //iterate on results row and create new index array of data
    while( $row = mysqli_fetch_row($queryRecords) ) { 
        $data[] = $row;
    }   

    $json_data = array(
            "draw"            => intval( $params['draw'] ),   
            "recordsTotal"    => intval( $totalRecords ),  
            "recordsFiltered" => intval($totalRecords),
            "data"            => $data   // total data array
            );

    echo json_encode($json_data);  // send data as json format
?>

我在 Stack Overflow 上研究过这类问题，有很多问题都有答案，但似乎没有一个对我有用。

有人可以指导我吗？

最佳答案

我已更新了 WHERE 条件后 GROUP BY 查询的代码。 

<?php
    //include connection file 
    include_once("connection.php");

    // initilize all variable
    $params = $columns = $totalRecords = $data = array();

    $params = $_REQUEST;

    //define index of column
    $columns = array( 
        0 => '`Full Name`',
        1 => 'Gender', 
        2 => 'CityName',
        3 => 'CourseDescriptionLong',
        4 => '`Subject`',
        5 => 'ScholarshipAwarded',
        6 => 'StudentID'
    );

    $where = $sqlTot = $sqlRec = "";

    // check search value exist
    if( !empty($params['search']['value']) ) {   
        $where .=" WHERE ";
        $where .=" (`Full Name` LIKE '".$params['search']['value']."%' ";    
        $where .=" OR CityName LIKE '".$params['search']['value']."%' ";

        $where .=" OR CourseDescriptionLong LIKE '".$params['search']['value']."%' )";
    }

    // getting total number records without any search
    $sql = "SELECT fullnames.`Full Name`, studentdetails.Gender, lt_cities.CityName, lt_coursedescription.CourseDescriptionLong, lt_coursesubject.`Subject`, Sum(scholarshipdetails.ScholarshipAwarded), studentdetails.StudentID, coursedetails.CourseType, lt_coursedescription.CourseDescriptionShort, scholarshipdetails.ScholarshipYear FROM studentdetails INNER JOIN scholarshipdetails ON studentdetails.StudentID = scholarshipdetails.StudentID INNER JOIN coursedetails ON studentdetails.StudentID = coursedetails.StudentID AND scholarshipdetails.ScholarshipYear = coursedetails.Scholarshipyear LEFT JOIN lt_coursedescription ON coursedetails.CourseID = lt_coursedescription.CourseID INNER JOIN tuitionfeedetails ON studentdetails.StudentID = tuitionfeedetails.StudentID AND scholarshipdetails.ScholarshipYear = tuitionfeedetails.ScholarshipYear INNER JOIN fullnames ON studentdetails.StudentID = fullnames.StudentID INNER JOIN lt_cities ON lt_cities.CityID = studentdetails.City LEFT JOIN lt_coursesubject ON lt_coursesubject.CourseID = lt_coursedescription.CourseID AND lt_coursesubject.SubjectID = coursedetails.CourseSubject ";
    $sqlTot .= $sql;
    $sqlRec .= $sql;
    //concatenate search sql if value exist
    if(isset($where) && $where != '') {

        $sqlTot .= $where;
        $sqlRec .= $where;
    }

   $sqlRec .=  "  GROUP BY studentdetails.StudentID ";
    $sqlRec .=  " ORDER BY ". $columns[$params['order'][0]['column']]."   ".$params['order'][0]['dir']."  LIMIT ".$params['start']." ,".$params['length']." ";

    $queryTot = mysqli_query($conn, $sqlTot) or die("database error:". mysqli_error($conn));


    $totalRecords = mysqli_num_rows($queryTot);

    $queryRecords = mysqli_query($conn, $sqlRec) or die("error to fetch employees data");

    //iterate on results row and create new index array of data
    while( $row = mysqli_fetch_row($queryRecords) ) { 
        $data[] = $row;
    }   

    $json_data = array(
            "draw"            => intval( $params['draw'] ),   
            "recordsTotal"    => intval( $totalRecords ),  
            "recordsFiltered" => intval($totalRecords),
            "data"            => $data   // total data array
            );

    echo json_encode($json_data);  // send data as json format
?>

关于php - 使用 "Grouped by"语句在数据表(服务器端)sql 查询中搜索，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/34549455/

25 4 0
文章推荐： mysql - 是否可以使用azure在线发布本地数据库？
文章推荐： mysql - 如何获取具有不同id的特定记录数的随机数据
文章推荐： android - 为什么我在 this.getWritableDatabase(); 上出现空指针异常，android？
文章推荐： android - UnsatisfiedLinkError 噩梦
行者123
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
全站热门文章
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com