Hi all,

I am writing a script that uses FPDF to create a PDF file. The script runs a query and should return data but for some reason the variables in the WHERE clause are not working. If I run the query alone standalone it works but when embedded in the script it does not return a result. I am using a class obtains from the FPDF site to produce tables called "PDF_MySQL_Table". This works if I take out the variables for date. Should I be passing the dates in a different way.

The code I am using is:

$pdf=new PDF();
$pdf->AddPage();
$pdf->AddCol('Room',15,'Room','15');
$pdf->AddCol('AuditBy',40,'Audit by');
$pdf->AddCol('LastAudit',40,'Audit date','L');
$pdf->AddCol('SeqID1306',15,'Hot','C');
$pdf->AddCol('SeqID1307',15,'Cold','C');
$pdf->AddCol('Comments13',60,'Comments','L');
//First table: put all columns automatically
$pdf->Table("SELECT Room, AuditBy, DATE_FORMAT(LastAudit,'%d-%m-%Y') AS LastAudit, SeqID1306, SeqID1307, Comments13 FROM Room_Hist WHERE CAST(LastAudit AS Date) BETWEEN " . $_SESSION['WaterFromDate'] ." AND ". $_SESSION['WaterToDate']." AND Complete = 1 ORDER BY Room ASC");
$pdf->Output();

Can anyone see why this sould not work.

Many thanks in advance.

Blackbox

    I would suspect you need single quotes around each date string (and probably should be applying an applicable escaping function to the values, too, to prevent SQL injection errors).

    I'm not liking the fact that you are cast()-ing that column to a date type, as that probably means the query will require a full table scan regardless of how you index it, which will result in degrading performance as/when the table gets larger. Is there some reason it is not defined already as a Date type?

      Hi NogDof, many thanks for your reply.

      I removed the cast()-ing, that field is a date field. I also changed the quotes from double to single but this produces the following error:

      Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING

      The new query is:

      SELECT Room, AuditBy, DATE_FORMAT(LastAudit,'%d-%m-%Y') AS LastAudit, SeqID1306, SeqID1307, Comments13 FROM Room_Hist WHERE LastAudit  BETWEEN ' . $_SESSION['WaterFromDate'] .' AND '. $_SESSION['WaterToDate'].' AND Complete = 1 ORDER BY Room ASC

      I have tried a number of approaches without a result to the issue.

      Do you have any ideas?

      Regards,

      Blackbox

        I just meant quotes around the actual date strings in the BETWEEN clause:

        "SELECT Room, AuditBy, DATE_FORMAT(LastAudit,'%d-%m-%Y') AS LastAudit, SeqID1306, SeqID1307, Comments13 
FROM Room_Hist 
WHERE LastAudit BETWEEN [B][COLOR="#FF0000"]'[/COLOR][/B]" . $_SESSION['WaterFromDate'] ."[B][COLOR="#FF0000"]'[/COLOR][/B] AND [B][COLOR="#FF0000"]'[/COLOR][/B]". $_SESSION['WaterToDate']."[B][COLOR="#FF0000"]'[/COLOR][/B] 
AND Complete = 1 ORDER BY Room ASC"

          PS: It might be a bit easier to read/follow in this case using "complex" (curly brace) notation for the array variables, so you don't have to mess with quotes/dots to concatenate everything together:

          $query = "
SELECT 
  Room, 
  AuditBy, 
  DATE_FORMAT(LastAudit,'%d-%m-%Y') AS LastAudit, 
  SeqID1306, 
  SeqID1307, 
  Comments13 
FROM Room_Hist 
WHERE 
  LastAudit BETWEEN '{$_SESSION['WaterFromDate']}' AND '{$_SESSION['WaterToDate']}' 
  AND Complete = 1 
ORDER BY Room ASC
";
$pdf->Table($query);

            Hi NogDog,

            Many, many thanks for this solution. I used your second and placed the query in a variable. Works perfectly.

            Regards,

            Blackbox

              Write a Reply...