Display pending days on the table

When I go to the index page, I want it to show how many Pending days are there from the Required date to the Current date, on the table. This is my index.php and it's giving an error "Fatal error: Call to a member function fetch_assoc() on boolean in C:\xampp\htdocs\Material\index.php on line 93"

line 93 -> while ($row = $result->fetch_assoc()) {

<?php

/* showing table */

                $sql = "SELECT * FROM main SET PendDays = CURRENT_DATE - RequiredDate ORDER BY ReqstDate DESC"
                    or die("Failed to query database" .mysqli_error());
                $result = $link->query($sql);    

                while ($row = $result->fetch_assoc()) {

                    print "<tr>"; 
                    print "<td >" . $row['ID'] . "</td>";
                    print "<td >" . $row['ReqstDate'] . "</td>";
                    print "<td ><a href=\"ReqOrdNum.php?ReqOrdNum={$row['ReqOrdNum']}\" target=\"_blank\">"  . $row['ReqOrdNum'] . "</a></td>";                    
                    print "<td ><a href=\"Location.php?Location={$row['Location']}\" target=\"_blank\">"  . $row['Location'] . "</a></td>";
                    print "<td ><a href=\"Description.php?Description={$row['Description']}\" target=\"_blank\">"  . $row['Description'] . "</a></td>"; 
                    print "<td >" . $row['Unit'] . "</td>";
                    print "<th >" . $row['Quantity'] . "</th>";
                    print "<th >" . $row['SupQty'] . "</th>";
                    print "<td >" . $row['RequiredDate'] . "</td>";
                    print "<td >" . $row['PendDays'] . "</td>";
                    print "<th >" . $row['BalanceQty'] . "</th>";
                    print "<th >" . $row['Unitprice'] . "</th>";
                    print "<th >" . $row['Totalamount'] . "</th>";
                    print "<td >" . $row['InvcNum'] . "</td>";
                    print "<td >" . $row['GRNNum'] . "</td>";
                    print "<td >" . $row['Remarks'] . "</td>";
                    print "<th ><a href=\"Status.php?Status={$row['Status']}\" target=\"_blank\">"  . $row['Status'] . "</a></th>";
                    print "</tr>"; 

                    }

?> 

3 answers

  • answered 2018-05-16 06:23 Goranov

    You've got an error in your SQL query. That's the reason the following statement to return false:

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

    I think you should rework this:

    $sql = "SELECT * FROM main SET PendDays = CURRENT_DATE - RequiredDate ORDER BY ReqstDate DESC"
    

  • answered 2018-05-16 06:23 Kishen Nagaraju

    Try this,

    $today = new DateTime();
    $sql = "SELECT * FROM main ORDER BY ReqstDate DESC"
            or die("Failed to query database" .mysqli_error());
    
    $result = $link->query($sql);    
    
    while ($row = $result->fetch_assoc()) {
        $date2 = new DateTime($row['RequiredDate']);
        print "<tr>"; 
        print "<td >" . $row['ID'] . "</td>";
        print "<td >" . $row['ReqstDate'] . "</td>";
        print "<td ><a href=\"ReqOrdNum.php?ReqOrdNum={$row['ReqOrdNum']}\" target=\"_blank\">"  . $row['ReqOrdNum'] . "</a></td>";                    
        print "<td ><a href=\"Location.php?Location={$row['Location']}\" target=\"_blank\">"  . $row['Location'] . "</a></td>";
        print "<td ><a href=\"Description.php?Description={$row['Description']}\" target=\"_blank\">"  . $row['Description'] . "</a></td>"; 
        print "<td >" . $row['Unit'] . "</td>";
        print "<th >" . $row['Quantity'] . "</th>";
        print "<th >" . $row['SupQty'] . "</th>";
        print "<td >" . $row['RequiredDate'] . "</td>";
        print "<td >" . $date2->diff($today)->format("%a"); . "</td>";
        print "<th >" . $row['BalanceQty'] . "</th>";
        print "<th >" . $row['Unitprice'] . "</th>";
        print "<th >" . $row['Totalamount'] . "</th>";
        print "<td >" . $row['InvcNum'] . "</td>";
        print "<td >" . $row['GRNNum'] . "</td>";
        print "<td >" . $row['Remarks'] . "</td>";
        print "<th ><a href=\"Status.php?Status={$row['Status']}\" target=\"_blank\">"  . $row['Status'] . "</a></th>";
        print "</tr>"; 
    }
    

  • answered 2018-05-16 06:27 Nick

    So there are a couple of problems: firstly in the PHP code you are checking if "SELECT ... " is equivalent to true (it is) with the or die... clause, which should actually be on the $link->query($sql) statement. i.e.

    $sql = "SELECT ... ";
    $result = $link->query($sql) or die("Failed to query database" .mysqli_error());
    

    Once you get this the right way around you'll see the reason for the error you are seeing is that the query failed, and so $result is false, not a result set.

    That leads to the other problem, which is your query. SET has no place in a SELECT. What you probably want is:

    $sql = "SELECT *, DATEDIFF(CURDATE(), RequiredDate) AS PendDays FROM main ORDER BY ReqstDate DESC";
    

    So in summary, change

    $sql = "SELECT * FROM main SET PendDays = CURRENT_DATE - RequiredDate ORDER BY ReqstDate DESC" or die("Failed to query database" .mysqli_error());
    $result = $link->query($sql);    
    

    to this:

    $sql = "SELECT *, DATEDIFF(CURDATE(), RequiredDate) AS PendDays FROM main ORDER BY ReqstDate DESC";
    $result = $link->query($sql) or die("Failed to query database" .mysqli_error());