JSON data to display in datatables

Question

I have tried sql results to display in a datatables, but when I try to execute the code the JSON result of the data array is displayed as a NULL. I check the SQL and it is correct but not able to display in JSON format. Here is the code :

    <?php 
// { initialise variables 
  $amt=100; 
  $start=0; 
// } 
// { connect to database 
  function dbRow($sql){ 
    $q=mysql_query($sql); 
    $r=mysql_fetch_array($q); 
    return $r; 
  } 
  function dbAll($sql){ 
    $q=mysql_query($sql); 
    while($r=mysql_fetch_array($q))$rs[]=$r; 
    return $rs; 
  } 
  mysql_connect('localhost','root','unu@12'); 
  mysql_select_db('GRBS_SITE_DB'); 
// } 
// { count existing records 
  $r=dbRow('select count(University_Name) as c 
            from grbs_country join grbs_rating 
            join grbs_university on grbs_country.Country_Code=grbs_university.Country_Code 
            and grbs_rating.University_id=grbs_university.University_id '); 
  $total_records=$r['c']; 
// } 
// { start displaying records 
  echo '{"iTotalRecords":'.$total_records.',
         "iTotalDisplayRecords":'.$total_records.',
         "aaData":['; 
  $rs=  dbAll("select University_Name,Country_Name,Total_Pubs,Per_Pubs_A1,Per_Pub_A1_A,
               Total_Cite,Per_Cite_A1,Per_Cite_A1_A,H_Index 
               from grbs_country join grbs_rating join grbs_university 
               on grbs_country.Country_Code=grbs_university.Country_Code 
               and grbs_rating.University_id=grbs_university.University_id 
               $start,
               $amt"); 
  $f=0; 
 foreach($rs as $r){ 
    if($f++) echo ',';  
    echo '["',$r['University_Name'],'",
            "',$r['Country_Name'],'",
            "',$r['Total_Pubs'],'",
            "',$r['Per_Pubs_A1'],'",
            "',$r['Per_Pub_A1_A'],'",
            "',$r['Total_Cite'],'",
            "',$r['Per_Cite_A1'],'",
            "',$r['Per_Cite_A1'],'",
            "',$r['H_Index'],'"]'; 
  } 
echo ']}'; 


// }

The result from the firebug is like this:

   {"iTotalRecords":189540,
         "iTotalDisplayRecords":189540,
         "aaData":[]}

score 1 · Answer 1 · edited May 23 '17 at 10:24

Firstly, use json_encode() instead of building JSON strings.

Secondly, you don't appear to have a WHERE or LIMIT clause for your $start and $amt variables in your second query (I can't tell what you're attempting to do with them).

Thirdly, the MySQL extension has been deprecated. You should not be using it to write new code. Instead, use PDO (highly recommended) or MySQLi.

See https://stackoverflow.com/a/12860046/283366

JSON data to display in datatables

1 Answers1