Problem displaying data from MSSql using FPDF
I am in need of some help. I am using FPDF, extracting data from a MS SQL Server 2014 table. I am using PHP v5.6.25.
I am rather confused about two columns that are giving me grief. One is a money column and the other is a date column.
The money column displays 4 decimals and I need to display it as 2 decimal places.
The date column will not display at all and it stalls the creation of the pdf.
With the date column, I decided to explode the date, reassemble it as a string in the order that I need to display, MM-DD-YYYY, and it will not display.
If I comment it out, it displays the PDF.
This is my select statement:
And this is what I have tried as to displaying the date - doesn't work.
I am thinking I need to change my sql select statement to put both the money and date columns in the format that I need.
How can I fix this and what am I doing wrong with the date and money columns ??
Any help appreciated.
I am rather confused about two columns that are giving me grief. One is a money column and the other is a date column.
The money column displays 4 decimals and I need to display it as 2 decimal places.
The date column will not display at all and it stalls the creation of the pdf.
With the date column, I decided to explode the date, reassemble it as a string in the order that I need to display, MM-DD-YYYY, and it will not display.
If I comment it out, it displays the PDF.
This is my select statement:
$sql = "select SupplierCode,ConfirmNbr,AmtRcvd,DateRcvd, Description,IDOC,InvoiceNbr from importCmsReceipts where AgencyId = $strAgencyId ";
And this is what I have tried as to displaying the date - doesn't work.
$strDateRcvd = $row['DateRcvd'];
$strArray = explode('-',$strDateRcvd);
$strDate = strval($strArray[2]) . '-' . strval($strArray[1]) .'-' . strval($strArray[0]);
$pdf->Cell(30,15,'$strDate');
I am thinking I need to change my sql select statement to put both the money and date columns in the format that I need.
How can I fix this and what am I doing wrong with the date and money columns ??
Any help appreciated.
Last Comment
ASKER
Thank you for responding - it is appreciated,
Here's what I came up with:
It's working good - I wanted as much done up from in the select in order to cut down on the processing time when creating the pdf.
I noticed two things - I could not get the date right using your suggestion. No clue why - pretty straight forward and simple. The money portion worked well, but slowed down creating the pdf.
BUT your suggestion did jingle in my mind to use convert in sql - so you helped a great deal.
Thank you !!
Here's what I came up with:
$sql = "select SupplierCode,ConfirmNbr,Convert(varchar,AmtRcvd,1)as AmtRcvd, Convert(varchar,DateRcvd,101) as DateRcvd,
Description,IDOC,InvoiceNbr from importCmsReceipts where AgencyId = $strAgencyId ";
It's working good - I wanted as much done up from in the select in order to cut down on the processing time when creating the pdf.
I noticed two things - I could not get the date right using your suggestion. No clue why - pretty straight forward and simple. The money portion worked well, but slowed down creating the pdf.
BUT your suggestion did jingle in my mind to use convert in sql - so you helped a great deal.
Thank you !!
No worries. It looks like you're not storing your data in your DB in the correct format in the first place. You appear to be storing numbers and dates as VarChar. You'd be better off sorting that out and then you won't need to do the extra processing
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Yep, you are correct about how some of the data is stored in the DB !! In this situation, the date in the DB is defined as "date" but the format is 'YYYY-DD-MM' - or I should say to us it is visually displayed as 'YYYY-DD-MM' and money is defined as 'money' with 4 decimal places - so I do not know !! I inherited the situation of DB - so got to make the best of it and hope I can always extract the data in expected client format !!
With FPDF, it expects a 'Blank' canvas so to speak and using echo etc just causes it to spit in your face. But One can create a little page on the side to test the coding in question. I think the problem with having it not accept this format:
was actually caused by a previous existing error. It had me baffled. But it is working using a variable once I fixed the money/date issue.
Again, thank you for helping. :)
With FPDF, it expects a 'Blank' canvas so to speak and using echo etc just causes it to spit in your face. But One can create a little page on the side to test the coding in question. I think the problem with having it not accept this format:
$pdf->Cell(40,15,$strPassenger,0,0,'L');
was actually caused by a previous existing error. It had me baffled. But it is working using a variable once I fixed the money/date issue.
Again, thank you for helping. :)
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
For the number, you can use the number_format() function:
number_format($row['AmtRcv
For the date, you're often better off using the DateTime class and then formatting the output however you need:
$date = new DateTime($row['DateRcvd'])
echo $date->format('m-d-Y'); // outputs 01-30-2020
It may help you to visualise your data once you've retrieved the records from your DB. You can easily do this using var_dump:
var_dump($row);
That should show you exactly what's coming back from your DB.
Also, I would expect the Cell() method to take your variable without the quotes. As you have it, you're just sending in a string of '$strDate', so this:
$pdf->Cell(30,15, $strDate);