How can i use Datatables for Php While loop

Question

How can i show Select statement results in the Data-table in PHP. I have used the following code to display record from Mysql table but not working.

I have tried this but no luck

Select Statement

$sql = "SELECT demand.itemid,demand.qty, MIITEM.descr,MIITEM.descr,supplier.suplId,supplier.suplProdCode,supplier.itemId,MIILOC.qStk,MIILOC.qWIP,MIILOC.qRes, MIILOC.qOrd
FROM MIITEM
LEFT JOIN demand
ON MIITEM.itemId=demand.itemId
LEFT OUTER JOIN supplier
ON MIITEM.itemId = supplier.itemId
LEFT OUTER JOIN MIILOC
ON MIITEM.itemId = MIILOC.itemId
WHERE MIITEM.itemId=demand.itemId AND supplier.itemId=demand.itemId";

$result = $conn->query($sql);

Displaying Records

echo"<table id='example' class='display' cellspacing='0' width='100%'>
    <thead>
      <tr style='background:#ccc;'>
<th STYLE='WIDTH:50px; padding:7px'>ID</th>
<th STYLE='WIDTH:250px; padding:7px'>Description</th>

<th STYLE='WIDTH:100px; padding:7px'>Supplier#</th>
<th STYLE='WIDTH:200px; padding:7px'>Supplier </th>
<th STYLE='WIDTH:100px; padding:7px'>ON WO</th>
<th STYLE='WIDTH:100px; padding:7px'>Stock</th>
<th STYLE='WIDTH:100px; padding:7px'>WIP</th>
<th STYLE='WIDTH:100px; padding:7px'>Reserve</th>
<th STYLE='WIDTH:100px; padding:7px'>On Order</th>

</tr>   </thead></table>";
if ($result->num_rows > 0) {

    while($row = $result->fetch_assoc()) {
      echo"<table id='example' class='display' cellspacing='0' width='100%'><tbody>
<tr>
 <th STYLE='WIDTH:50px; padding:7px'>"; echo$row["itemid"];echo"</th>
 <th STYLE='WIDTH:250px; padding:7px'>"; echo$row["descr"];echo"</th>
 <th STYLE='WIDTH:100px; padding:7px'>"; echo$row["suplId"];echo"</th>
 <th STYLE='WIDTH:200px; padding:7px'>"; echo$row["suplProdCode"];echo"    </th>";
echo"<th STYLE='WIDTH:100px; padding:7px'>";echo$row["qty"];echo"</th>";
echo"<th STYLE='WIDTH:100px; padding:7px'>";echo$row["qStk"];echo"</th>";
echo"<th STYLE='WIDTH:100px; padding:7px'>";echo$row["qWIP"];echo"</th>";

echo"<th STYLE='WIDTH:100px; padding:7px'>";echo$row["qRes"];echo"</th>";

echo"<th STYLE='WIDTH:100px; padding:7px'>";echo$row["qOrd"];echo"</th>";


echo"</tr></tbody></table>";

Here is the Javascript code and the CSS file from CDN but nothing is changing.

<script type="text/javascript" src="//code.jquery.com/jquery-1.12.3.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/select/1.2.0/js/dataTables.select.min.js"></script>


  <script type="text/javascript">
$(document).ready(function() {
    $('#example').DataTable( {
        select: true
    } );
} );

  </script>

Where is your query? do have execute query in php myadmin?

paranoid
– paranoid

2016-05-29 05:33:54 +00:00
Commented May 29, 2016 at 5:33 — paranoid
– paranoid, Commented May 29, 2016 at 5:33
@paranoid I added the Query at the beginning of my question

Kin
– Kin

2016-05-29 05:42:10 +00:00
Commented May 29, 2016 at 5:42 — Kin
– Kin, Commented May 29, 2016 at 5:42
copy this code in php myadmin and do you have result?

paranoid
– paranoid

2016-05-29 05:47:34 +00:00
Commented May 29, 2016 at 5:47 — paranoid
– paranoid, Commented May 29, 2016 at 5:47
@paranoid yes it gave me the right result in php myadmin

Kin
– Kin

2016-05-29 05:54:54 +00:00
Commented May 29, 2016 at 5:54 — Kin
– Kin, Commented May 29, 2016 at 5:54
Is anything being displayed correctly or at all?

bwalshy
– bwalshy

2016-05-29 06:23:39 +00:00
Commented May 29, 2016 at 6:23 — bwalshy
– bwalshy, Commented May 29, 2016 at 6:23

annoyingmouse · Accepted Answer · 2016-05-29 06:30:25Z

Your markup was very odd, it looked like you were inserting duplicate tables, with the same ID, for each $row. Did anything go into the UI at all? It's always worthwhile to check both the source of the page and also the validity of the markup using an online HTML Validity checker. From what I understand of your needs I've created this snippet:

if ($result->num_rows > 0) {
    echo "
        <style>
            th, td {
                width: 100px;
                padding: 7px;
            }
            .fifty {
                width: 50px;
            }
            .twohundred {
                width: 200px;
            }
            .twohundredfifty {
                width: 250px;
            }
        </style>
        <table id='example' class='display' cellspacing='0' width='100%'>
            <thead>
                <tr style='background:#ccc;'>
                    <th class='fifty'>ID</th>
                    <th class='twohundredfifty'>Description</th>
                    <th>Supplier#</th>
                    <th class='twohundred'>Supplier </th>
                    <th>ON WO</th>
                    <th>Stock</th>
                    <th>WIP</th>
                    <th>Reserve</th>
                    <th>On Order</th>
                </tr>   
            </thead>
            <tbody>
    ";
    while($row = $result->fetch_assoc()) {
        echo "<tr>";
        echo "    <td class='fifty'>".$row["itemid"]."</td>";
        echo "    <td class='twohundredfifty'>".$row["descr"]."</td>";
        echo "    <td>".$row["suplId"]."</td>";
        echo "    <td class='twohundred'>".$row["suplProdCode"]."</td>";
        echo "    <td>".$row["qty"]."</td>";
        echo "    <td>".$row["qStk"]."</td>";
        echo "    <td>".$row["qWIP"]."</td>";
        echo "    <td>".$row["qRes"]."</td>";
        echo "    <td>".$row["qOrd"]."</td>";
        echo "</tr>";
    }
    echo "
            </tbody>
        </table>
    ";
}

It's also always a really good idea to check your code using indents so that you can ensure that the flow of the markup is correct.

Hope that helps.

chudasamachirag · Accepted Answer · 2016-05-29 06:28:33Z

Please try this code

<?php
//you dont need where condition Left join already filter data over all of your tables demand,supplier, MIILOC
$sql = "SELECT demand.itemid,demand.qty, MIITEM.descr,MIITEM.descr,supplier.suplId,supplier.suplProdCode,supplier.itemId,MIILOC.qStk,MIILOC.qWIP,MIILOC.qRes, MIILOC.qOrd
FROM MIITEM
LEFT JOIN demand
ON MIITEM.itemId=demand.itemId
LEFT OUTER JOIN supplier
ON MIITEM.itemId = supplier.itemId
LEFT OUTER JOIN MIILOC
ON MIITEM.itemId = MIILOC.itemId";

$result = $conn->query($sql);
if (!$result) {
 printf("Errormessage: %s\n", $mysqli->error);
 die;
}

$table = "<table id='example' class='display' cellspacing='0' width='100%'>
<thead>
  <tr style='background:#ccc;'>
    <th STYLE='WIDTH:50px; padding:7px'>ID</th>
    <th STYLE='WIDTH:250px; padding:7px'>Description</th>

    <th STYLE='WIDTH:100px; padding:7px'>Supplier#</th>
    <th STYLE='WIDTH:200px; padding:7px'>Supplier </th>
    <th STYLE='WIDTH:100px; padding:7px'>ON WO</th>
    <th STYLE='WIDTH:100px; padding:7px'>Stock</th>
    <th STYLE='WIDTH:100px; padding:7px'>WIP</th>
    <th STYLE='WIDTH:100px; padding:7px'>Reserve</th>
    <th STYLE='WIDTH:100px; padding:7px'>On Order</th>
</tr>   </thead>";

if ($result->num_rows > 0) {
    $table .= "<tbody>";
    while($row = $result->fetch_assoc()) {
      $table .= "
      <tr>
        <th STYLE='WIDTH:50px; padding:7px'>{$row["itemid"]}</th>
        <th STYLE='WIDTH:250px; padding:7px'>{$row["descr"]}</th>
        <th STYLE='WIDTH:100px; padding:7px'>{$row["suplId"]}</th>
        <th STYLE='WIDTH:200px; padding:7px'>{$row["suplProdCode"]}</th>
        <th STYLE='WIDTH:100px; padding:7px'>{$row["qty"]}</th>
        <th STYLE='WIDTH:100px; padding:7px'>{$row["qStk"]}</th>
        <th STYLE='WIDTH:100px; padding:7px'>{$row["qWIP"]}</th>
        <th STYLE='WIDTH:100px; padding:7px'>{$row["qRes"]}</th>
        <th STYLE='WIDTH:100px; padding:7px'>{$row["qOrd"]}</th>
    </tr>";
}
$table .= "</tbody>"
}
$table .= "</table>";

echo $table;

?>
<script type="text/javascript" src="//code.jquery.com/jquery-1.12.3.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/select/1.2.0/js/dataTables.select.min.js"></script>


<script type="text/javascript">
    $(document).ready(function() {
        $('#example').DataTable( {
            select: true
        } );
    } );

</script>

I've answered as well ;-) though I wrapped the whole table printing in the if and also changed the th in the body to td. Nice approach though, the adding to a string variable and then doing one echo at the end.

Collectives™ on Stack Overflow

How can i use Datatables for Php While loop

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related