Date and number format in a Query

[elentz]

How can I change my date format from 2008-11-10 to 11-10-2008 from within a MySql query? I also, need to format a number to two decimal places and add a comma for thousands in the same query. Don't need much do I?

Here's the Query:

$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);

As you can see I am using it to make a table in a PDF using FPDF. Without the formatting it works fine.

I tried this:
'SELECT
vtiger_invoice.invoice_no,
vtiger_invoice.invoicestatus,
vtiger_invoice.subject,
vtiger_invoice.total,
vtiger_invoice.DATE_FORMAT(invoicedate(), '%m/%d/%Y'),
vtiger_invoicecf.cf_674
FROM
vtiger_invoice

But all I got was a blank screen. I haven't gotten to formatting the total field.

Thanks in advance for any suggestions.!

 

[r937]

change this --

vtiger_invoice.DATE_FORMAT(invoicedate(), '%m/%d/%Y')

to this --

DATE_FORMAT(vtiger_invoice.invoicedate,'%m/%d/%Y')

and for the formatting, use the FORMAT function


r937.com | rudy.ca

 

[elentz]

Thanks for the reply! I changed the query as you suggested and I got the same result. Here's what I have:

$pdf->Table('SELECT
vtiger_invoice.invoice_no,
vtiger_invoice.invoicestatus,
vtiger_invoice.subject,
vtiger_invoice.total,
DATE_FORMAT(vtiger_invoice.invoicedate,'%m/%d/%Y'),
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);

Any other suggestions?

Thanks

 

[elentz]

I just put that query into Phpmyadmin and it worked. So there must be an issue with the rest of my script. Although without the Date_format all worked out fine. Here is the whole thing:

<?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','xxxxxxx');
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,
DATE_FORMAT(vtiger_invoice.invoicedate,'%m/%d/%Y'),
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 for any suggestions.

 

[r937]

sorry, i don't do php, but it will likely be the issue of the single quotes around the entire SELECT statement conflicting with the single quotes around the date format string

r937.com | rudy.ca