CODEWITHSUNDEEP
phpjqueryjsondatatables
Reminent
Reminent

Reputation: 141

Using DataTable with a json

I'm trying to make a website that can display some data from a mysql database using jquery and i cant figure out how to populate my table with data. my PHP file looks like this:

<?php
$servername = "localhost";
$database = "testdatabase";
$username = "root";
$password = "";

// Create connection
$conn = new mysqli($servername, $username, $password, $database);
// Check connection

if ($conn->connect_error) 
{
  die("Connection failed: " . $conn->connect_error);
}
$getinfo = "SELECT * FROM greenhouse";
$names = $conn->query($getinfo);
$str = array(); 
if ($names->num_rows > 0) {
    // output data of each row
    while($res = $names->fetch_array(MYSQL_ASSOC)) {
        $str[] = $res;
    }
}

echo json_encode($str);
?>

And this is my script:

<script>


function get_greenhouses(){
    if (window.XMLHttpRequest) {
        // code for IE7+, Firefox, Chrome, Opera, Safari
        xmlhttp = new XMLHttpRequest();
    } else {
        // code for IE6, IE5
        xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
    }
    xmlhttp.onreadystatechange = function() {
        if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {
            //var returnval = JSON.parse(xmlhttp.responseText);
            //document.getElementById("main_text").innerHTML = dataSet[0];
            start_table(JSON.parse(xmlhttp.responseText));
            //document.getElementById("main_text").innerHTML = returnval[0].Name;
        }
    }
    xmlhttp.open("GET","get_greenhouses.php", true);
    xmlhttp.send();
  }

  function start_table(greenhousedata){
    console.log(greenhousedata);
    //document.getElementById("main_text").innerHTML = greenhousedata[0].Adress;
       $('#example1').DataTable( {
        "ajax": greenhousedata,
        columns: [
            { title: "Name" },
            { title: "ID" },
            { title: "plant type" },
            { title: "install date" },
            { title: "adress" }
        ]
    } );
  }
$(function () {
    get_greenhouses();
  });
</script>

So i'm trying to have a function get_greenhouses() that starts when the document is loaded. In it i make a request to the PHP file that gets the data, encodes it to json, and sends it back. When i get the data i want to pass it to the start_table() function and then use it to fill the table. Right now i'm getting an error that says: DataTables warning: table id=example1 - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1 but when i follow the link and check what data i get from the php file it says json and looks correct as far as i can tell.

Can someone tell me what i'm doing wrong or if there is a beter way of doing it?

Upvotes: 1

Views: 141

Answers (3)

Reminent
Reminent

Reputation: 141

I tried charlietfls solution first and got another error but after 15 min of googling and trying i found out i hade to say what column should use what part of the data, so instead of just having { title: "Name" }, i had to use { title: "Name", "data": "Name" } and after that it worked like a charm.

After that i read Gyrocode.coms answer and tried that aswell and it also worked like a charm if i changed the data: fields to match my incomming data. since this solution let me scrap the get_greenhouses function i chose it instead.

Thanks everyone for the help :)

Upvotes: 0

charlietfl
charlietfl

Reputation: 171679

The ajax option is for passing a url so that the plugin makes the ajax request. In your case you are making that request yourself and passing data directly to the plugin

You can continue with your approach by passing your data to the data property of the plugin options . See example 

$('#example1').DataTable( {
        "data": greenhousedata,

Or let the plugin make the request itself instead

Upvotes: 1

Gyrocode.com
Gyrocode.com

Reputation: 58860

Use the code shown below instead.

Just put correct field names of your greenhouse table instead of dummy ones specified by data option.

function get_greenhouses(){
   $('#example1').DataTable( {
      ajax: {
         url: "get_greenhouses.php",
         dataSrc: ""
      },
      columns: [
          { data: "name",       title: "Name" },
          { data: "id",         title: "ID" },
          { data: "plant",      title: "Plant type" },
          { data: "dt_install", title: "Install date" },
          { data: "address",    title: "Address" }
      ]
   });
}

Upvotes: 2

Related Questions