prob w/datediff

Discussion in 'PHP' started by pshaw, Jun 9, 2021.

0
  1. #1
    Hi guys, please advise me as to what I'm doing wrong. My test database table fields have
    "payrec" has 'p', "amtdue" is blank, recur has 'N' and "duedate" is past.
    ---------------------------------
    the code:

    <html><head>
<style>
  [USER=6127]@page[/USER] { size 8.5in 11in; margin: 2cm }
  div.page { page-break-after: always }
  </style>
</head><body><center>
<div class="page">

<?php
//Open a new connection to the MySQL server
require_once "getacctdb.php";

//MySqli Select Query
$results = $mysqli->query (SELECT acctno, recur, pd, payrec, bname, datepaid, amtdue,
DATEDIFF(CURDATE(),duedate) AS dayslate, FROM accttbl` WHERE payrec = 'p' AND amtdue != '' AND
recur = 'N'";

echo date('m/d/y');
?>
<font size=+1><b><center> Accounts Payable Report</font></center></b></b><br />
<table cellspacing=0 cellpadding=0 border=1>
<thead>
<tr>
<th bgcolor="#ccffff">acct#</th>
<th bgcolor="#ccffff">creditor</th>
<th bgcolor="#ccffff">due date</th>
<th bgcolor="#ccffff">days late</th>
<th bgcolor="#ccffff">amt due</th>
</tr>
<?php
while($row = mysql_fetch_array($result))
  {
$totdue += $row['amtdue'];  
  echo '
  <tr>
  <td>', $row['acctno'], '</td>
  <td>', $row['bname'], '</td>
  <td>', $row['duedate'], '</td>  
  <td>', $row['dayslate'], '</td>  
  <td align=right class="currency">$'.number_format($row['amtdue'],2).'</td>  
  </tr>';
  }  
  echo '
  <tr>
  <th bgcolor="#ccffff" scope="row" colspan="7">Grand Total:</th>
  <td bgcolor="#FFD4D4" class="currency">$'.number_format($totdue, 2, '.', ''), '</td>
  </tr>
  </table>';

  echo "Page 1";
  ?>
  </div>
  <div class="page">
  <?php
  echo "Page 2";
  
  mysql_close();
  ?>
</body></html>
    PHP:
    ---------------------------------
    the result:
    query ("SELECT acctno, recur, pd, payrec, bname, DATEDIFF(CURDATE(),duedate) AS dayslate, datepaid, amtdue FROM accttbl` WHERE payrec = 'p' AND amtdue != '' AND recur = 'N'"); echo date('m/d/y'); ?>
    Accounts Payable Report

    '; } echo '
    acct# creditor due date days late amt due
    ', $row['acctno'], ' ', $row['bname'], ' ', $row['duedate'], ' ', $row['dayslate'], ' $'.number_format($row['amtdue'],2).'
    Grand Total: $'.number_format($totdue, 2, '.', ''), '
    '; echo "Page 1";
     
    Last edited by a moderator: Jun 9, 2021
    pshaw, Jun 9, 2021 IP
  2. sarahk

    sarahk iTamer Staff

    Messages:
    28,807
    Likes Received:
    4,534
    Best Answers:
    123
    Trophy Points:
    665
    #2
    Your query doesn't have a ) at the end. Your string doesn't look like a string. Your ` don't match up. You have a comma after dayslate

    $results = $mysqli->query (SELECT acctno, recur, pd, payrec, bname, datepaid, amtdue,
DATEDIFF(CURDATE(),duedate) AS dayslate, FROM accttbl` WHERE payrec = 'p' AND amtdue != '' AND
recur = 'N'";
    PHP:
    should be
    $results = $mysqli->query ("SELECT `acctno`, `recur`, `pd`, `payrec`, `bname`, `datepaid`, `amtdue`,
DATEDIFF(CURDATE(),`duedate`) AS `dayslate` FROM `accttbl` WHERE `payrec` = 'p' AND `amtdue` != '' AND
`recur` = 'N'");
    PHP:
    an alternate way to write it is:
    $sql = "SELECT `acctno`, `recur`, `pd`, `payrec`, `bname`, `datepaid`, `amtdue`,
DATEDIFF(CURDATE(),`duedate`) AS `dayslate` FROM `accttbl` WHERE `payrec` = 'p' AND `amtdue` != '' AND
`recur` = 'N'";
$results = $mysqli->query ($sql);
    PHP:
    Yes, you've got 2 lines and an extra variable but it's more readable, debuggable, and understandable for a newbie. You won't be one for long but a few extra steps will make life easier.

    When the problem is query based it's a good idea to copy it into phpMyAdmin (or whatever tool you use) and see if it runs there.
     
    sarahk, Jun 9, 2021 IP
  3. pshaw

    pshaw Greenhorn

    Messages:
    90
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    16
    #3
    $sql = "SELECT `acctno`, `recur`, `pd`, `payrec`, `bname`, `datepaid`, `amtdue`,
    DATEDIFF(CURDATE(),`duedate`) AS `dayslate` FROM `accttbl` WHERE `payrec` = 'p' AND `amtdue` != '' AND
    `recur` = 'N'";
    $results = $mysqli->query ($sql);

    produces:
    query ($sql); echo date('m/d/y'); ?>
    Accounts Payable Report

    '; } echo '
    acct# creditor due date days late amt due
    ', $row['acctno'], ' ', $row['bname'], ' ', $row['duedate'], ' ', $row['dayslate'], ' $'.number_format($row['amtdue'],2).'
    Grand Total: $'.number_format($totdue, 2, '.', ''), '
    '; echo "Page 1"; ?>
     
    pshaw, Jun 9, 2021 IP
  4. pshaw

    pshaw Greenhorn

    Messages:
    90
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    16
    #4
    can u explain: "When the problem is query based it's a good idea to copy it into phpMyAdmin (or whatever tool you use) and see if it runs there."?
     
    pshaw, Jun 9, 2021 IP
  5. sarahk

    sarahk iTamer Staff

    Messages:
    28,807
    Likes Received:
    4,534
    Best Answers:
    123
    Trophy Points:
    665
    #5
    That makes it look like it's not inside a <?PHP and it's just outputting the raw script.
     
    sarahk, Jun 9, 2021 IP
  6. JEET

    JEET Notable Member

    Messages:
    3,832
    Likes Received:
    502
    Best Answers:
    19
    Trophy Points:
    265
    #6
    For some reason your "->" is working as PHP closing tag in this line:
    $result= $mysqli->query($sql);
     
    JEET, Jun 12, 2021 IP
  7. deathshadow

    deathshadow Acclaimed Member

    Messages:
    9,732
    Likes Received:
    1,999
    Best Answers:
    253
    Trophy Points:
    515
    #7
    @sarahk got the big points... I'd also suggest you not slop 25 year out of date markup in there like FONT/CENTER or the inlined style.
     
    deathshadow, Jun 27, 2021 IP