1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

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,500
    Likes Received:
    4,460
    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,500
    Likes Received:
    4,460
    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,825
    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,998
    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