1

Im using a datatable to show records from a DB, using PHP.

See my code below;

<table class="table datatable-basic">
  <thead>
    <tr>
      <th> ID </th>
      <th> Name</th>
      <th>Address </th>
      <th class="text-center">Actions</th>
    </tr>
  </thead>
  <tbody>

  <?
  $result9023=mysql_query("SELECT * FROM hr_locations")or die('ERROR 315' );
  $num_rows = mysql_num_rows($result9023);

  for ($i = 1; $i <= mysql_num_rows($result9023); $i++) {
    $row = mysql_fetch_array($result9023);
    $location_id = $row ['id'];
    $name = $row ['location'];
    $address = $row ['address'];

    echo "         <tr>
        <td>$location_id</td>
        <td>$name</td>
        <td>$address</td>
        <td class='text-center'>Edit</td>
      </tr>
    "; } ?>
  </tbody>  
</table>

The table is showing correctly, with the data populated as it should however I am getting the below error when the page loads.

Error

I have looked at https://datatables.net/tn/4 however it's not making much sense?

Offir
  • 3,252
  • 3
  • 41
  • 73
Shane
  • 753
  • 3
  • 8
  • 21
  • ***Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php).*** [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). – Jay Blanchard Dec 07 '16 at 13:56
  • *"DataTables has requested data for a given row, of the {parameter} provided and **there is no data there, or it is null or undefined**."* – Jay Blanchard Dec 07 '16 at 13:58
  • @JayBlanchard - Aware of this, but looking for an answer to the above question first – Shane Dec 07 '16 at 13:58
  • Not sure what this means @JayBlanchard? – Shane Dec 07 '16 at 13:59
  • Which "above question"? The "making not sense" question? There is no other question in your question. You might be retrieving a row that isn't there. Try setting `$i = 0` in your for loop - most arrays begin counting at 0. – Jay Blanchard Dec 07 '16 at 13:59
  • That was a response to the PDO part, sorry – Shane Dec 07 '16 at 14:00
  • @JayBlanchard - By setting `$i = 0` just adds a blank row? and the error still shows? – Shane Dec 07 '16 at 14:02
  • it is good you have shown the php, in this case, we now also must see the resulting html source from your browser. we're guessing there is no data in the table due to some kind of error in logic or query. – WEBjuju Dec 07 '16 at 14:03
  • @WEBjuju - Sorry what HTML source do you need to see? – Shane Dec 07 '16 at 14:03
  • how does the clientside dataTables initialisation look like? – davidkonrad Dec 09 '16 at 09:20

1 Answers1

2

Since most MySQL array start with 0 do this:

for ($i = 0; $i < $num_rows; $i++) {

Set $i = 0 then state that $i must be less than the number of rows (if you have 4 you will get 0,1,2,3 (4 rows, properly indexed). Instead of counting the rows again, just use the variable you already set for the count.

You're actually probably doing a little too much with your code. Instead of using an unnecessary for loop, just use a while:

while($row = mysql_fetch_array($result9023)){ 
    $location_id = $row ['id'];
    $name = $row ['location'];
    $address = $row ['address'];

    echo "         <tr>
        <td>$location_id</td>
        <td>$name</td>
        <td>$address</td>
        <td class='text-center'>Edit</td>
      </tr>
    "; } ?>

This way you're making sure to catch each row returned from your query without possibly duplicating or skipping rows as you might do when using a for loop along with mysql_fetch_array() as you're trying to do.

Jay Blanchard
  • 34,243
  • 16
  • 77
  • 119