DataTables
Advanced interaction
features for your tables.
Editor
Comprehensive editing
library for DataTables.
Manual
Download
≡ Show site navigation

Showing 1 to 1 of 1 entries when I use php and mysqli to display the table

Showing 1 to 1 of 1 entries when I use php and mysqli to display the table

AjooAjoo Posts: 9Questions: 0Answers: 0
in Free community support

Hi, My question is similar to the one asked by jemz recently. However since there was no solution given I am asking it again.

I am using the simplest mysqli and php code to fetch data from a Mysql database and display it in a table using Data Tables.
The table displays but at the bottom of it , it says Showing 1 to 1 of 1 entries. So while it displays all entries, it only recognizes the first table entry. As such pagination and sorting functions won't and do not work.

If I remove the php bit that fetches the data and echos it out as data to the table and replace that block with the data as shown in the example at DataTables, ( Like below), then the table works correct.

<tr>
<td>Tiger Nixon</td>
<td>System Architect</td>
<td>Edinburgh</td>
<td>61</td>
<td>2011/04/25</td>
<td>$3,120</td>
</tr>
.
.
.

The moment I try and use php and mysqli to churn out the data in a while loop I get the error.

I tried making a test case in Data Tables Live but how & where do I connect to the mysql ? I can append my code here but I am not sure if that is permitted.

Kindly help me on this please

Thank you very much.

Replies

  • AjooAjoo Posts: 9Questions: 0Answers: 0

    Hi Looking for some of the Gurus to have a look at this small issue and provide some clue as to what should I may do to get going with the tables.

    Thanks all for any help or pointers.

  • AjooAjoo Posts: 9Questions: 0Answers: 0

    Hi fellas,

    Was hoping to get some response on this issue. Please help needed!

    Thanks

  • allanallan Posts: 61,732Questions: 1Answers: 10,110 Site admin

    Please link to a test page showing the issue as noted in the forum rules.

    Allan

  • AjooAjoo Posts: 9Questions: 0Answers: 0

    Hi Allan I would like to but I don't know have an active website. I don't know how to attach the code on DataTables,live or JSFiddle to a mysql table.

    I can however paste the code that I was tryiig here if that's permitted. Its very short and sweet. Await your reply and thanks loads for the reply.

  • allanallan Posts: 61,732Questions: 1Answers: 10,110 Site admin

    Sure. Best to post it into paste bin or jsfiddle etc.

    Allan

  • AjooAjoo Posts: 9Questions: 0Answers: 0
    edited November 2014

    Hi Allan,

    Thanks. So I will paste the code here now. It will create the required data table on the first run and show the table as i am receiving it. If it needs to be run again then the block which creates the Table needs to be commented out since the table will be already created after the first run.

    Thanks and please help me solve this. Will await your reply.

    <?php       
$db_host        = 'localhost';
$db_user        = 'root';
$db_pass        = '';
$db_database    = 'test';
            
        $fcon = mysqli_connect($db_host,$db_user,$db_pass,$db_database) or die('Unable to establish a DB connection');
        mysqli_query($fcon,"SET names UTF8");
        
///// CAUTION ////////////////////
//// HAVING ONCE CREATED THE TABLE IN THE DB SUCCESSFULLY //////////////////
//// BE SURE TO COMMENT OUT THIS BLOCK //////////////////////

$sql = "CREATE TABLE testresult (
    resultId INT NOT NULL AUTO_INCREMENT,
    studentId INT,
    subjectId INT,
    testdate DATE NOT NULL DEFAULT 0,
    score INT,
    PRIMARY KEY (resultId)
    )";
    
$result=mysqli_query($fcon,$sql);

//////////////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////////////

if($result)
{   
    $subjects = array (1,2,3);
    $data = array();
    $di = dateinterval::createFromDateString('next weekday');
    $startDate = '2013-06-03';
    
    $dt = new DateTime($startDate);
    $dp = new DatePeriod($dt, $di, 14); // 15 day deriod
    foreach ($dp as $day) {
        foreach ($subjects as $sub) {
            $data[] = sprintf("(null, 1, %d, '%s', %d)", $sub, $day->format('Y-m-d'), rand(20,100));
        }
    }
    $sql = "INSERT INTO testresult VALUES " . join(',', $data);
    $result=mysqli_query($fcon,$sql);
    if($result) echo "Data Inserted";
    else echo"Error inserting data";
    
} else echo"Error with DB";

$sql= "Select * from testresult";
$result = mysqli_query($fcon,$sql);
    
?>

<!DOCTYPE html>
<html>
<head>
    <meta http-equiv="Content-type" content="text/html; charset=uft-8">
<!--    <meta name="viewport" content="width=device-width,initial-scale=1">   -->
    
    <title>DataTables example - Zero configuration</title>

    <link rel="stylesheet" type="text/css" href="//cdn.datatables.net/1.10.3/css/jquery.dataTables.css">
    <script type="text/javascript" charset="utf8" src="//code.jquery.com/jquery-1.10.2.min.js"></script>
    <script type="text/javascript" charset="utf8" src="//cdn.datatables.net/1.10.3/js/jquery.dataTables.js"></script>

    <script type="text/javascript" class="init">

        $(document).ready(function() {
            $('#example').DataTable();
        } );

    </script>

</head>

<body>
<?php
    echo "<table id='example' class = 'display' cellspacing ='0' width = '100%' >";

        echo "<thead>";
            echo"<tr>"; 
                echo "<th> ResultID </th>";
                echo "<th> StudentID </th>";
                echo "<th> SubjectID </th>";
                echo "<th> Testdate </th>";
                echo "<th> Score </th>";    
            echo"</tr>";    
        echo"</thead>";
        
        $cnt = 1; 
        while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC))
        {
            $resultId = htmlspecialchars($row['resultId']);
            $studentId = htmlspecialchars($row['studentId']);
            $subjectId = htmlspecialchars($row['subjectId']);
            $testdate = htmlspecialchars($row['testdate']);
            $score = htmlspecialchars($row['score']);

        echo"<tbody>";      
            echo "<tr>";
                echo "<td>".$resultId."</td>";
                echo "<td>".$studentId. "</td>";
                echo "<td>".$subjectId. "</td>";
                echo "<td>".$testdate. "</td>";
                echo "<td>".$score. "</td>";
                echo "</tr>";
            echo"</tr>";    
        }
        echo"</tbody>";
    echo "</table>";    
?>

<!--    <script type="text/javascript" class="init">

        $(document).ready(function() {
            $('#example').DataTable();
        } );

    </script>
-->
</body>
</html>
  • AjooAjoo Posts: 9Questions: 0Answers: 0

    Hi Allan,

    I have also created a new pastebin account. I have pasted the code there under the name dataTables Issue. Looking forward to a reply.

    Thanks loads

  • allanallan Posts: 61,732Questions: 1Answers: 10,110 Site admin

    Your created HTML is not valid. You are creating a new tbody for every row, and only closing one. Move the tbody tag out of the loop.

    Allan

  • AjooAjoo Posts: 9Questions: 0Answers: 0
    edited November 2014

    Damn ! What an oversight. Thanks Allan, That Solved it !

    Thank you very Much.

  • AjooAjoo Posts: 9Questions: 0Answers: 0
    edited November 2014

    Hi Can someone please tell me how to mark this thread as Answered. I can't find any option that would allow me to do so.

    Thanks.

  • allanallan Posts: 61,732Questions: 1Answers: 10,110 Site admin

    This thread wasn't opened as a question so it can't be marked as answered.

    Good to hear it works for you though.

    Allan

  • AjooAjoo Posts: 9Questions: 0Answers: 0

    Thanks Allan

This discussion has been closed.

DataTables

DataTables designed and created by SpryMedia Ltd.
© 2007-2023 MIT licensed. Privacy policy. Supporters.
SpryMedia Ltd is registered in Scotland, company no. SC456502.