[SOLVED] Can someone help me figure this out?

elentz · November 12, 2008

I am using FPDf to create a PDF from a MySql query. The result query has two date fields in it and the result is in the mysql format. I would like to get the date in an easier to read format. Here is the whole script

<?php
define('FPDF_FONTPATH','font/');
require('mysql_table.php');

class PDF extends PDF_MySQL_Table
{
function Header()
{
        //Title
        $this->SetFont('Arial','',18);
        $this->Cell(0,50,'Account Statement',0,1,'C');   //the second number in the Cell(x,yy,) is the vertical position of the line
        $this->Ln(10);
        //Ensure table header is output
        parent::Header();
}
}

//Connect to database
mysql_connect('localhost','admin','9axmy425');
mysql_select_db('vtigercrm50');

//***********Get Billing Info **************
$sql = "SELECT
`vtiger_account`.`accountname`,
`vtiger_accountbillads`.`accountaddressid`,
`vtiger_accountbillads`.`bill_street`,
`vtiger_accountbillads`.`bill_city`,
`vtiger_accountbillads`.`bill_state`,
`vtiger_accountbillads`.`bill_code`,
`vtiger_account`.`phone`,
`vtiger_account`.`fax`
FROM
`vtiger_account`
Inner Join `vtiger_accountbillads` ON `vtiger_account`.`accountid` = `vtiger_accountbillads`.`accountaddressid`
WHERE
`vtiger_account`.`accountid` =  '4103'";


$result=mysql_query($sql);
$row=mysql_fetch_assoc($result);
$customername =$row['accountname'];
$fax=$row['fax'];
$address=$row['bill_street'];
$city=$row['bill_city'];
$state=$row['bill_state'];
$code=$row['bill_code'];
$phone=$row['phone'];
//*************Close *******************

$pdf=new PDF();
$pdf->Open();

$pdf->AddPage();
$pdf->Image('cqilogo.png',5,4,50);
$pdf->SetFont('Times','B',12);
$pdf->Cell(1,-55,'Customer :');
$pdf->SetFont('Times','',10);
$pdf->Cell(1,-45,$customername);
$pdf->Cell(-1,-38,$address);
$pdf->Cell(1,-31,$city);

$pdf->AddCol('invoice_no',20,'Inv. #','L');
$pdf->AddCol('invoicestatus',30,'Status','L');
$pdf->AddCol('subject',80,'Synposis','L');
$pdf->AddCol('total',25,'Total','R');
$pdf->AddCol('invoicedate',25,'Invoice Date','L');
$pdf->AddCol('cf_674',25,'Date Paid','L');
$prop=array('HeaderColor'=>array(255,150,100),
                       // 'color1'=>array(210,245,255),
                       // 'color2'=>array(255,255,210),
                        'padding'=>2);

$pdf->Table('SELECT
vtiger_invoice.invoice_no,
vtiger_invoice.invoicestatus,
vtiger_invoice.subject,
vtiger_invoice.total,
[b]vtiger_invoice.invoicedate,
vtiger_invoicecf.cf_674[/b]
FROM
vtiger_invoice
Inner Join vtiger_invoicecf ON vtiger_invoicecf.invoiceid = vtiger_invoice.invoiceid
WHERE
vtiger_invoice.accountid =  4103
ORDER BY
vtiger_invoice.accountid ASC',$prop);

$pdf->Output();
?>

The Bold text is the date(s) that i need formatted. Here is what I tried in this script:

Changed
vtiger_invoice.invoicedate

to this --

  DATE_FORMAT(vtiger_invoice.invoicedate,'%m/%d/%Y')[code]  Within this script the PDF failed with a blank screen.  When I used it in Phpmyadmin it worked.  Is there a way to format the date fields before creating the table?

Thanks for looking

Ed

Alt_F4 · November 12, 2008

have a look here if your dates are in timestamp format:

http://au2.php.net/date

if not you could use this:

http://au2.php.net/manual/en/function.mktime.php

GingerRobot · November 12, 2008

You should be using the mysql DATE_FORMAT() function. I suspect your problem is that you used single quotes to delimit the string format, but are already using single quotes to delimit the entire query. Try:


$pdf->Table("SELECT
vtiger_invoice.invoice_no,
vtiger_invoice.invoicestatus,
vtiger_invoice.subject,
vtiger_invoice.total,
DATE_FORMAT(vtiger_invoice.invoicedate'%m/%d/%Y'),
DATE_FORMAT(vtiger_invoicecf.cf_674,'%m/%d/%Y')
FROM
vtiger_invoice
Inner Join vtiger_invoicecf ON vtiger_invoicecf.invoiceid = vtiger_invoice.invoiceid
WHERE
vtiger_invoice.accountid =  4103
ORDER BY
vtiger_invoice.accountid ASC",$prop);

As for why you got a blank screen, i would assume you have display_errors turned off.

elentz · November 12, 2008

Thanks for the replies.

I tried your suggestion Ben and I got this:

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%m/%d/%Y), DATE_FORMAT(vtiger_invoicecf.cf_674,%m/%d/%Y) FROM vtiger_invoice Inn' at line 6

Query: SELECT vtiger_invoice.invoice_no, vtiger_invoice.invoicestatus, vtiger_invoice.subject, vtiger_invoice.total, DATE_FORMAT(vtiger_invoice.invoicedate,%m/%d/%Y), DATE_FORMAT(vtiger_invoicecf.cf_674,%m/%d/%Y) FROM vtiger_invoice Inner Join vtiger_invoicecf ON vtiger_invoicecf.invoiceid = vtiger_invoice.invoiceid WHERE vtiger_invoice.accountid = 4103 ORDER BY vtiger_invoice.accountid ASC

GingerRobot · November 12, 2008

It looks from that like you've not put any quotes around the format string. In the code i pasted, i did miss a comma, but it looks like you added that in. This should work:

$pdf->Table("SELECT
vtiger_invoice.invoice_no,
vtiger_invoice.invoicestatus,
vtiger_invoice.subject,
vtiger_invoice.total,
DATE_FORMAT(vtiger_invoice.invoicedate,'%m/%d/%Y'),
DATE_FORMAT(vtiger_invoicecf.cf_674,'%m/%d/%Y')
FROM
vtiger_invoice
Inner Join vtiger_invoicecf ON vtiger_invoicecf.invoiceid = vtiger_invoice.invoiceid
WHERE
vtiger_invoice.accountid =  4103
ORDER BY
vtiger_invoice.accountid ASC",$prop);

If it doesn't can you paste the exact code you're now using?

elentz · November 12, 2008

When I plug in the query that you last offered I get the PDF (which is more than I have gotten since I have been messing around with this!) but, the date columns are blank, so that's not so good. So here is the complete code (including the included file for creating the table (sorry I just remembered that was part of this !)

Included file:

<?php

require('fpdf.php');

class PDF_MySQL_Table extends FPDF

{

var $ProcessingTable=false;

var $aCols=array();

var $TableX;

var $HeaderColor;

var $RowColors;

var $ColorIndex;

function Header()

{

//Print the table header if necessary

if($this->ProcessingTable)

$this->TableHeader();

}

function TableHeader()

{

$this->SetFont('Arial','B',10);

$this->SetX($this->TableX);

$fill=!empty($this->HeaderColor);

if($fill)

$this->SetFillColor($this->HeaderColor[0],$this->HeaderColor[1],$this->HeaderColor[2]);

foreach($this->aCols as $col)

$this->Cell($col['w'],6,$col['c'],1,0,'C',$fill);

$this->Ln();

}

function Row($data)

{

$this->SetX($this->TableX);

$ci=$this->ColorIndex;

$fill=!empty($this->RowColors[$ci]);

if($fill)

$this->SetFillColor($this->RowColors[$ci][0],$this->RowColors[$ci][1],$this->RowColors[$ci][2]);

foreach($this->aCols as $col)

$this->Cell($col['w'],5,$data[$col['f']],1,0,$col['a'],$fill);

$this->Ln();

$this->ColorIndex=1-$ci;

}

function CalcWidths($width,$align)

{

//Compute the widths of the columns

$TableWidth=0;

foreach($this->aCols as $i=>$col)

{

$w=$col['w'];

if($w==-1)

$w=$width/count($this->aCols);

elseif(substr($w,-1)=='%')

$w=$w/100*$width;

$this->aCols[$i]['w']=$w;

$TableWidth+=$w;

}

//Compute the abscissa of the table

if($align=='C')

$this->TableX=max(($this->w-$TableWidth)/2,0);

elseif($align=='R')

$this->TableX=max($this->w-$this->rMargin-$TableWidth,0);

else

$this->TableX=$this->lMargin;

}

function AddCol($field=-1,$width=-1,$caption='',$align='L')

{

//Add a column to the table

if($field==-1)

$field=count($this->aCols);

$this->aCols[]=array('f'=>$field,'c'=>$caption,'w'=>$width,'a'=>$align);

}

function Table($query,$prop=array())

{

//Issue query

$res=mysql_query($query) or die('Error: '.mysql_error()."<BR>Query: $query");

//Add all columns if none was specified

if(count($this->aCols)==0)

{

$nb=mysql_num_fields($res);

for($i=0;$i<$nb;$i++)

$this->AddCol();

}

//Retrieve column names when not specified

foreach($this->aCols as $i=>$col)

{

if($col['c']=='')

{

if(is_string($col['f']))

$this->aCols[$i]['c']=ucfirst($col['f']);

else

$this->aCols[$i]['c']=ucfirst(mysql_field_name($res,$col['f']));

}

//Handle properties

if(!isset($prop['width']))

$prop['width']=0;

if($prop['width']==0)

$prop['width']=$this->w-$this->lMargin-$this->rMargin;

if(!isset($prop['align']))

$prop['align']='C';

if(!isset($prop['padding']))

$prop['padding']=$this->cMargin;

$cMargin=$this->cMargin;

$this->cMargin=$prop['padding'];

if(!isset($prop['HeaderColor']))

$prop['HeaderColor']=array();

$this->HeaderColor=$prop['HeaderColor'];

if(!isset($prop['color1']))

$prop['color1']=array();

if(!isset($prop['color2']))

$prop['color2']=array();

$this->RowColors=array($prop['color1'],$prop['color2']);

//Compute column widths

$this->CalcWidths($prop['width'],$prop['align']);

//Print header

$this->TableHeader();

//Print rows

$this->SetFont('Arial','',9);

$this->ColorIndex=0;

$this->ProcessingTable=true;

while($row=mysql_fetch_array($res))

$this->Row($row);

$this->ProcessingTable=false;

$this->cMargin=$cMargin;

$this->aCols=array();

}

?>

PDF Creation file:

<?php

define('FPDF_FONTPATH','font/');

require('mysql_table.php');

class PDF extends PDF_MySQL_Table

{

function Header()

{

//Title

$this->SetFont('Arial','',18);

$this->Cell(0,50,'Account Statement',0,1,'C'); //the second number in the Cell(x,yy,) is the vertical position of the line

$this->Ln(10);

//Ensure table header is output

parent::Header();

}

//Connect to database

mysql_connect('localhost','admin','9axmy425');

mysql_select_db('vtigercrm50');

//***********Get Billing Info **************

$sql = "SELECT

`vtiger_account`.`accountname`,

`vtiger_accountbillads`.`accountaddressid`,

`vtiger_accountbillads`.`bill_street`,

`vtiger_accountbillads`.`bill_city`,

`vtiger_accountbillads`.`bill_state`,

`vtiger_accountbillads`.`bill_code`,

`vtiger_account`.`phone`,

`vtiger_account`.`fax`

FROM

`vtiger_account`

Inner Join `vtiger_accountbillads` ON `vtiger_account`.`accountid` = `vtiger_accountbillads`.`accountaddressid`

WHERE

`vtiger_account`.`accountid` = '4103'";

$result=mysql_query($sql);

$row=mysql_fetch_assoc($result);

$customername =$row['accountname'];

$fax=$row['fax'];

$address=$row['bill_street'];

$city=$row['bill_city'];

$state=$row['bill_state'];

$code=$row['bill_code'];

$phone=$row['phone'];

//*************Close *******************

$pdf=new PDF();

$pdf->Open();

$pdf->AddPage();

$pdf->Image('cqilogo.png',5,4,50);

$pdf->SetFont('Times','B',12);

$pdf->Cell(1,-55,'Customer :');

$pdf->SetFont('Times','',10);

$pdf->Cell(1,-45,$customername);

$pdf->Cell(-1,-38,$address);

$pdf->Cell(1,-31,$city);

$pdf->AddCol('invoice_no',20,'Inv. #','L');

$pdf->AddCol('invoicestatus',30,'Status','L');

$pdf->AddCol('subject',80,'Synposis','L');

$pdf->AddCol('total',25,'Total','R');

$pdf->AddCol('invoicedate',25,'Invoice Date','L');

$pdf->AddCol('cf_674',25,'Date Paid','L');

$prop=array('HeaderColor'=>array(255,150,100),

// 'color1'=>array(210,245,255),

// 'color2'=>array(255,255,210),

'padding'=>2);

$pdf->Table("SELECT

vtiger_invoice.invoice_no,

vtiger_invoice.invoicestatus,

vtiger_invoice.subject,

vtiger_invoice.total,

vtiger_invoice.invoicedate,

vtiger_invoicecf.cf_674

FROM

vtiger_invoice

Inner Join vtiger_invoicecf ON vtiger_invoicecf.invoiceid = vtiger_invoice.invoiceid

WHERE

vtiger_invoice.accountid = 4103

ORDER BY

vtiger_invoice.accountid ASC",$prop);

$pdf->Output();

?>

Thanks

Ed

GingerRobot · November 12, 2008

1.) Please use the

tags. You managed it before, why stop now?

2.) Again, confused. The code you posted doesn't include the DATE_FORMAT part of the query. What are you actually using?

3.) Not having worked with these classes i cant be entirely sure, but you might have to add aliases to your query:

$pdf->Table("SELECT
vtiger_invoice.invoice_no,
vtiger_invoice.invoicestatus,
vtiger_invoice.subject,
vtiger_invoice.total,
DATE_FORMAT(vtiger_invoice.invoicedate,'%m/%d/%Y') AS invoicedate,
DATE_FORMAT(vtiger_invoicecf.cf_674,'%m/%d/%Y') AS cf_674
FROM
vtiger_invoice
Inner Join vtiger_invoicecf ON vtiger_invoicecf.invoiceid = vtiger_invoice.invoiceid
WHERE
vtiger_invoice.accountid =  4103
ORDER BY
vtiger_invoice.accountid ASC",$prop);

elentz · November 12, 2008

Sorry about not using the

tags I forgot as I was using the quick reply. Using the Date_Format in the query as you have provided, when I run the PDF script the table is created just fine but without any data in the date columns. So maybe you are right that I need to use aliases, maybe that is why the dates are missing?

GingerRobot · November 12, 2008

Did you try the last query i provided?

elentz · November 12, 2008

Yeah, that one is the one that allows the pdf to be created but without any date info in the columns.

elentz · November 12, 2008

I figured it out, the last query works if I remove all the header cells, having the alias was messing up the table header for some reason. So that part I can fix easily (I think). I am assuming that I can format the total with commas, and 2 digits for dollar amounts, similarily to what is done with the dates. and I need to change the cell widths.

Thanks for all your help and patience!

Sign In

[SOLVED] Can someone help me figure this out?

Recommended Posts

elentz

Link to comment

Share on other sites

Alt_F4

Link to comment

Share on other sites

GingerRobot

Link to comment

Share on other sites

elentz

Link to comment

Share on other sites

GingerRobot

Link to comment

Share on other sites

elentz

Link to comment

Share on other sites

GingerRobot

Link to comment

Share on other sites

elentz

Link to comment

Share on other sites

GingerRobot

Link to comment

Share on other sites

elentz

Link to comment

Share on other sites

elentz

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information