[SOLVED] Date Format From MySQL DB

[refiking]

I am trying to retrieve the date fields in the dd/mm/yy format.  I have about 40 dates that are stored, so how can I retrieve them all as dd/mm/yy format at once?  Here is my code.  the date fields begin with  $upf_sd

 

while($row = mysql_fetch_assoc($bor)) {
$bor_id = $row[bor_id];
$bfn = $row[bfn];
$bln = $row[bln];
$cfn = $row[cfn];
$cln = $row[cln];
$addy = $row[addy];
$zip = $row[zip];
$status = $row[status];
$pstat = $row[pstat];
$upf_sd = $row[upf_sd];
$upf_ecd = $row[upf_ecd];
$upf_cd = $row[upf_cd];
$upf_lmd = $row[upf_lmd];
$uld_sd = $row[uld_sd];
$uld_ecd = $row[uld_ecd];
$uld_cd = $row[uld_cd];
$uld_lmd = $row[uld_lmd];
$app_sd = $row[app_sd];
$app_ecd = $row[app_ecd];
$app_cd = $row[app_cd];
$app_lmd = $row[app_lmd];
$tit_sd = $row[tit_sd];
$tit_ecd = $row[tit_ecd];
$tit_cd = $row[tit_cd];
$tit_lmd = $row[tit_lmd];
$ins_sd = $row[ins_sd];
$ins_ecd = $row[ins_ecd];
$ins_cd = $row[ins_cd];
$ins_lmd = $row[ins_lmd];
$dup_sd = $row[dup_sd];
$dup_ecd = $row[dup_ecd];
$dup_cd = $row[dup_cd];
$dup_lmd = $row[dup_lmd];
$aps_sd = $row[aps_sd];
$aps_ecd = $row[aps_ecd];
$aps_cd = $row[aps_cd];
$aps_lmd = $row[aps_lmd];
$fh_sd = $row[fh_sd];
$fh_cd = $row[fh_cd];
$fh_ecd = $row[fh_ecd];
$fh_lmd = $row[fh_lmd];
$mp_sd = $row[mp_sd];
$mp_ecd = $row[mp_ecd];
$mp_cd = $row[mp_cd];
$mp_lmd = $row[mp_lmd];
$op_sd = $row[op_sd];
$op_ecd = $row[op_ecd];
$op_cd = $row[op_cd];
$op_lmd = $row[op_lmd];
}

[revraz]

Extract may be helpful here

 

http://us3.php.net/extract

[PFMaBiSmAd]

Use the mysql DATE_FORMAT() function with the DATE fields in your SELECT query to get mysql to do the conversion to dd/mm/yy for you at the time the data is retrieved from the database -

 

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format

[refiking]

Would that affect the other fields that are being retrieved?  The others are VARCHAR.

[PFMaBiSmAd]

Any mysql function is written to use the column you want as a parameter in the function. If you have 5 date columns, your query would use the DATE_FORMAT() function in 5 places in the SELECT portion of the query.

 

If you are just using SELECT * you will need to write out each column you want to select or you will need to use php code to perform the same actions on the date columns after the data has been fetched.

 

You could write a foreach() loop in php that looks at the index name and only formats the ones you want.

[refiking]

I tried splitting up the queries so only the date fields would be formatted.  However, it's not showing up at all.  Here is what I have so far:

 

$bo = "SELECT * FROM Loans where loan_id = '$loan_id'";
$bor = mysql_query($bo) or die;
while($row = mysql_fetch_assoc($bor)) {
$bor_id = $row[bor_id];
$bfn = $row[bfn];
$bln = $row[bln];
$cfn = $row[cfn];
$cln = $row[cln];
$addy = $row[addy];
$zip = $row[zip];
$status = $row[status];
$pstat = $row[pstat];
}
$bo1 = "(SELECT DATE_FORMAT *, %c-%e-%y) FROM Loans where loan_id = '$loan_id'";
$bor2 = mysql_query($bo1) or die;
while($row = mysql_fetch_assoc($bor2)) {
$upf_sd = $row[upf_sd];
$upf_ecd = $row[upf_ecd];
$upf_cd = $row[upf_cd];
$upf_lmd = $row[upf_lmd];
$uld_sd = $row[uld_sd];
$uld_ecd = $row[uld_ecd];
$uld_cd = $row[uld_cd];
$uld_lmd = $row[uld_lmd];
$app_sd = $row[app_sd];
$app_ecd = $row[app_ecd];
$app_cd = $row[app_cd];
$app_lmd = $row[app_lmd];
$tit_sd = $row[tit_sd];
$tit_ecd = $row[tit_ecd];
$tit_cd = $row[tit_cd];
$tit_lmd = $row[tit_lmd];
$ins_sd = $row[ins_sd];
$ins_ecd = $row[ins_ecd];
$ins_cd = $row[ins_cd];
$ins_lmd = $row[ins_lmd];
$dup_sd = $row[dup_sd];
$dup_ecd = $row[dup_ecd];
$dup_cd = $row[dup_cd];
$dup_lmd = $row[dup_lmd];
$aps_sd = $row[aps_sd];
$aps_ecd = $row[aps_ecd];
$aps_cd = $row[aps_cd];
$aps_lmd = $row[aps_lmd];
$fh_sd = $row[fh_sd];
$fh_cd = $row[fh_cd];
$fh_ecd = $row[fh_ecd];
$fh_lmd = $row[fh_lmd];
$mp_sd = $row[mp_sd];
$mp_ecd = $row[mp_ecd];
$mp_cd = $row[mp_cd];
$mp_lmd = $row[mp_lmd];
$op_sd = $row[op_sd];
$op_ecd = $row[op_ecd];
$op_cd = $row[op_cd];
$op_lmd = $row[op_lmd];
}

[PFMaBiSmAd]

Using the * is not valid sql and the query if probably failing. The die; statement is just stopping your code.

[refiking]

So, how would I enter the code for the $bo1 = line?  Can you just use one, two, three as the example fields?

[refiking]

I changed each one of the lines individually, but now they all give me today's date.  What do I need to change to make it the date of the field?

 

$upf_sd = date('m/d/y', strtotime($row[upf_sd]));