PHP 查詢資料庫表單 匯出 excel


//datebase
define("DB_HOST", "localhost");
define("DB_USER", "xxx");
define("DB_PASSWORD", "xxx");
define("DB_NAME", "xxx");

db_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

function db_query($str)
{
$res = mysql_query($str);
if(!$res)
{
echo "$str
\n";
die(mysql_error());
}
return $res;
}

function db_connect($host, $user, $password, $db)
{

$link = mysql_connect($host, $user, $password);
db_select_db($db, $link);
return $link;
}

function db_select_db($db, $link)
{
$res = mysql_select_db($db, $link);
if(!$res) die(mysql_error());
return $res;
}

function cleanData(&$str)
{
$str = preg_replace("/\t/", "\\t", $str);
$str = preg_replace("/\n/", "\\n", $str);
}




HMTL表單撰寫




取得資料庫資料

else
{
$begin_year = $_GET['begin_year']="2009";
$begin_month= $_GET['begin_month']="01";
$begin_day = $_GET['begin_day']="01";

$end_year = $_GET['end_year']="2011";
$end_month = $_GET['end_month']="4";
$end_day = $_GET['end_day']="27";

$begin = "$begin_year-$begin_month-$begin_day";
$end = "$end_year-$end_month-$end_day";

$str = "SELECT user.name, course.name".
" from user_groups, user, course".
" where user_groups.userid = user.id and user_groups.pass = 1 and user_groups.courseid = course.id and course.passread > 0";

if(!empty($begin))
$str .= " AND user_groups.passTime >= '$begin' ";
if(!empty($end))
$str .= " AND user_groups.passTime <= '$end' "; db_query('SET NAMES "big5"'); $res = db_query($str);


匯出Excel文件

//export groups.xls
header("Pragma: public");
header("Expires: 0");
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Content-type: application/vnd.ms-excel');
header('Content-Disposition: inline; filename="groups.xls";');
header('Content-Transfer-Encoding: binary');




while($arr=mysql_fetch_array($res))
{
array_walk($arr, 'cleanData');
echo implode("\t", array_values($arr)) . "\n";
}
}