PHP select statement from MySql to Excel. Formatting date to YYYYMMDD -
note: in mysql database, "dob" varchar , in format: mm/dd/yyyy
date of birth, cvrg effective date, & cvrg expiration date need changed mm/dd/yyyy yyyymmdd when arrives in excel. please see code below:
<?php if($qry == "ok"){ // db connection include("connection string goes here"); $select = "select 'a' 'transaction type', policyno 'uhcsr policy no', school 'campus location description', code 'campus location number', '' 'date received uhcsr', '' 'date stamped school', '' 'social security number', ssn 'studentid number', lname 'lastname', fname 'firstname', mi 'middle initial', dob 'date of birth', gender 'gender', concat( concat(address1, ' '), address2) '(mailing) address ln 1','' '(mailing) address ln 2',city '(mailing) city', state '(mailing) state', zipcode '(mailing) zipcode', '' '(mailing) zipcode suffix', '' '(permanent) address ln 1', '' '(permanent) address ln 2', '' '(permanent) city', '' '(permanent) state', '' '(permanent) zipcode', '' '(permanent) zipcode suffix', email 'email address', replace(phone, '-', '') 'telephone number', 'itl' 'insured category code', '' 'insured type code', 'a' 'insured (row)id code', period 'period code', semester 'cvrg period description', trim(left(coveragedate, locate('-', coveragedate)-1)) 'cvrg effective date', trim(replace(right(coveragedate, locate('-', coveragedate)+0),'-','')) 'cvrg expiration date', noadfee 'premium amount', quantity 'coverage period quantity', '' 'primary insured ssn', ssn 'primary insured student id', '' 'organization name', '' 'organization number', '' 'dependent coverage code', '' 'check number', '' 'credit card number', '' 'credit card expiration month', '' 'credit card expiration year', tedfee 'amount paid', paytype 'pay type' intenrollment confirm = 'y' , amountpaid != '0' , (policyno '%200473-%' or policyno '%2060-%' or policyno '%202377-%' or policyno='')"; if ( $smon != "none" , $sday != "none" , $syear != "none" , $emon != "none" , $eday != "none" , $eyear != "none" ) { $startdate = strtotime("$smon $sday, $syear"); $enddate = strtotime("$emon $eday, $eyear"); $select .= "and applydate between $startdate , $enddate "; } elseif ( $smon != "none" , $sday != "none" , $syear != "none" ) { $startdate = strtotime("$smon $sday, $syear"); $select .= "and applydate > $startdate "; } elseif ( $emon != "none" , $eday != "none" , $eyear != "none" ) { $enddate = strtotime("$emon $eday, $eyear"); $select .= "and applydate < $enddate "; } $select .= "order school asc, lname asc"; // run query $export = mysql_query($select) or die(mysql_error()); $fields = mysql_num_fields($export); // field info headers $header = ''; ( $i = 0; $i < $fields; $i++ ) { $header .= mysql_field_name($export, $i) . "\t"; } // extract data , convert excel readable format $data = ''; while ( $row = mysql_fetch_row($export)) { $line = ''; foreach ( $row $value ) { if ( ( !isset($value) ) or ( $value == "" ) ) { $value = "\t"; } else { $value = str_replace('"', '""', $value); $value = '="' . $value . '"' . "\t"; // ^ // added equal sign } $line .= $value; } $data .= trim($line)."\n"; } $data = str_replace("\r", "", $data); // return message if query returns no data if ( $data == "" ) { $data = "\n(0) records found!\n"; } // setup headers no caching header("content-type: application/octet-stream"); header("content-disposition: attachment; filename=data.xls"); header("pragma: no-cache"); header("expires: 0"); print "$header\n$data"; } else { ?> <style> body,td,table {font-family:arial; font-size:10px;} select {font-family:arial; font-size:12px;} </style> <table align="center"> <tr><td align=center><a href="index.php"><font size=2>back menu</font></a></td></tr> <tr><td> <form action="extract2.php" method="post"> start: <select name='smon' size='1'> <option value="none">month</option> <option value='january'>january</option> <option value='february'>february</option> <option value='march'>march</option> <option value='april'>april</option> <option value='may'>may</option> <option value='june'>june</option> <option value='july'>july</option> <option value='august'>august</option> <option value='september'>september</option> <option value='october'>october</option> <option value='november'>november</option> <option value='december'>december</option> </select> <select name="sday"> <option value="none">day</option> <? for($ctr=1; $ctr<=31;++$ctr){ if($row[sday] == $ctr){ print("<option value='$ctr' selected>$ctr</option>"); } else{ print("<option value='$ctr'>$ctr</option>"); } } echo "</select> "; echo "<select name='syear' size='1'>"; $today = getdate(); $yr = $today['year']; ?> <option value="none">year</option>"; <option value='2005'>2005</option>"; <option value='2006'>2006</option>"; <option value='2007'>2007</option>"; <option value='2008'>2008</option>"; <option value='2009'>2009</option>"; <option value='2010'>2010</option>"; </select> - end: <select name='emon' size='1'> <option value="none">month</option> <option value='january'>january</option> <option value='february'>february</option> <option value='march'>march</option> <option value='april'>april</option> <option value='may'>may</option> <option value='june'>june</option> <option value='july'>july</option> <option value='august'>august</option> <option value='september'>september</option> <option value='october'>october</option> <option value='november'>november</option> <option value='december'>december</option> </select> <select name="eday"> <option value="none">day</option> <? for($ctr=1; $ctr<=31;++$ctr){ if($row[eday] == $ctr){ print("<option value='$ctr' selected>$ctr</option>"); } else{ print("<option value='$ctr'>$ctr</option>"); } } echo "</select> "; echo "<select name='eyear' size='1'>"; $today = getdate(); $yr = $today['year']; ?> <option value="none">year</option>"; <option value='2005'>2005</option>"; <option value='2006'>2006</option>"; <option value='2007'>2007</option>"; <option value='2008'>2008</option>"; <option value='2009'>2009</option>"; <option value='2010'>2010</option>"; </select> <input type="submit" value="select"> <input type="hidden" name="qry" value="ok"> </tr> <tr><td align=center style="font-famiy:tahoma; font-size:12px; color:777777;">if not enter date, records displayed.</td></tr> </form> </table><p> <? } ?>
given date in specific format, can quick conversion using date , strtotime follows:
// convert old date string yyyymmdd format $date = date('ymd', strtotime($old_date_string));
Comments
Post a Comment