Populate DataTable with data from MySQL and PHP

Question

I am trying to populate my Datatable with data from MySQL but can't figure out how to do so. I want to be able to populate the DataTable once directed to the page, then user can click on a row and use that row's data for the next page the user will be redirected to. This is what I have so far:

<table id="example" class="display" cellspacing="0" width="100%">
<thead>
    <tr>

        <th>Name</th>
        <th>Age</th>
        <th>Gender</th>
    </tr>

</thead>
<tfoot>
    <tr>
        <th>Name</th>
        <th>Age</th>
        <th>Gender</th>
    </tr> 
</tfoot>
<tbody>
    <tr>
        <td> Placeholder1</td>
        <td> Placeholder2</td>
        <td> Placeholder3</td>
    </tr>
    <tr>
        <td> Placeholder1</td>
        <td> Placeholder2</td>
        <td> Placeholder3</td>
    </tr>
    <tr>
        <td> Placeholder1</td>
        <td> Placeholder2</td>
        <td> Placeholder3</td>
    </tr>
</tbody>
</table>

<script>


$(document).ready(function() {
var table = $('#example').DataTable();

$('#example tbody').on('click', 'tr', function () {
    var data = table.row( this ).data();
    alert( 'You clicked on '+data[0]+'\'s row' );
} );
} );
</script>

PHP FILE:

<?php

include('connection.php');



 $sql = "SELECT ID, Name, Age, Gender FROM DBTABLE";


$response = mysqli_query($db, $sql);


if($response)
{
    echo '<table>';

   while($row = mysqli_fetch_array($response))
    {

       echo '<tr><td align="left">' .
            $row['Name'] . '</td><td align="left">' .
            $row['Age']  '</td><td align="left">' .
            $row['Gender'] . '</td><td align="left">';


       echo '</tr>';
    }

   echo '</table>';

  }
else
{
    echo "Couldn’t issue database query<br />";
    echo mysqli_error($db);
}






// Close connection to the database
mysqli_close($db);
?>

You initialized the wrong one.

Irvin
– Irvin

2017-04-12 03:26:44 +00:00
Commented Apr 12, 2017 at 3:26 — Irvin
– Irvin, Commented Apr 12, 2017 at 3:26
Sorry, I corrected it.

Carbon
– Carbon

2017-04-12 03:32:08 +00:00
Commented Apr 12, 2017 at 3:32 — Carbon
– Carbon, Commented Apr 12, 2017 at 3:32
Check this: github.com/pandeyz/…

Mayank Pandeyz
– Mayank Pandeyz

2017-04-12 03:49:43 +00:00
Commented Apr 12, 2017 at 3:49 — Mayank Pandeyz
– Mayank Pandeyz, Commented Apr 12, 2017 at 3:49

davidkonrad · Accepted Answer · 2017-04-12 05:49:41Z

1

Use the dataTables API instead

var table = $('#example').DataTable({
  ajax: {
    url: 'phpfile.php'
  },
  columns: [
    { data: 'Name' },
    { data: 'Age' },
    { data: 'Gender' }
  ]
});

phpfile.php

...
$data = array();
if ($response) {
  while($row = mysqli_fetch_assoc($response)) {
    $data[] = $row;
  }
}
echo json_encode(array('data' => $data));

answered Apr 12, 2017 at 5:49

davidkonrad

85.7k17 gold badges211 silver badges273 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Carbon Over a year ago

Thank you for this answer! I'm sure this works too but I think I figured out a solution of just passing the the data from the row by passing it through the URL into a PHP GET variable.

Collectives™ on Stack Overflow

Populate DataTable with data from MySQL and PHP

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related