CODEWITHSUNDEEP
phpjquerymysqlajaxdatatables
Kamlesh Panchal
Kamlesh Panchal

Reputation: 192

Adding hyperlink with URL variables to datatable (jQuery) + Search not working

I am using datatable jQuery plugin to render multiple joined tables from MySQL database. I am able to get the table rendered as desired. But there are 2 problems.

1st : I want to have a field (StudentID) to have hyperlink and on clicking it I want to pass that value as URL variable to the other page (like ViewDetails.php?StudentID=21). Currently I am able to add the hyperlink to the field (StudentID) but the text of the field is displaying "Undefined" instead of the StudentID and on clicking on it the Variable (StudentID) is not passed to the URL.

2nd : The search is not functioning.

I am providing the code for both of my files, (1) Index.php (the file with the table and the js which sends ajax request to response.php file). (2) response.php (the file containing the sql and search code which sends json encoded data to index.php). I am using the datatable version 1.10.10. I am following this tutorial

Following is my code:

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
?>

I have researched for this type questions on the Stack Overflow, and there are many questions with answers, but none of them seems to work for me.

Can any one guide me?

Upvotes: 2

Views: 2382

Answers (1)

Gyrocode.com
Gyrocode.com

Reputation: 58900

Change your DataTables initalization code to:

$('#employee_grid').DataTable({
    "stateSave": true,
    "ajax": {
       "url": "response_b.php",
       "type": "POST"
    },
    "columnDefs": [ {
       "targets": 6,
       "render": function ( data, type, full, meta ) {
          return '<a href="beneficiary.php?StudentID='+data+'">'+data+'</a>';
        }
    }]
});

Upvotes: 1

Related Questions