Home >Backend Development >PHP Tutorial >PHP imports mysql data into Excel table_PHP tutorial

PHP imports mysql data into Excel table_PHP tutorial

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOriginal: 2016-07-21 14:57:111015browse

Sometimes when writing a program, the background requires a large amount of data to be imported into the database. For example, test scores, phone books, etc. are generally data stored in excel. At this time, we can export excel into a csv file, and then use the following program. Batch import data into the database

Upload cvs and import into the database, the test is successful (some codes are not standardized, such as PHP_SELF, which needs to be rewritten as $_SERVER["PHP_SELF"])
PHP code

The following is the quoted content:

以下为引用的内容：
$fname = $_FILES['MyFile']['name'];
$do = copy($_FILES['MyFile']['tmp_name'],$fname);
if ($do)
{
echo"导入数据成功
";
} else {
echo "";
}
?>

error_reporting(0);
//导入CSV格式的文件
$connect=mysql_connect("localhost","a0530093319","123456") or die("could not connect to database");
mysql_select_db("a0530093319",$connect) or die (mysql_error());
$fname = $_FILES['MyFile']['name'];
$handle=fopen("$fname","r");
while($data=fgetcsv($handle,10000,","))
{
$q="insert into test (code,name,date) values ('$data[0]','$data[1]','$data[2]')";
mysql_query($q) or die (mysql_error());

}
fclose($handle);
?>

$ fname = $_FILES['MyFile']['name'];
$do = copy($_FILES['MyFile']['tmp_name'],$fname);
if ($do)

以下为引用的内容：
$DB_Server = "localhost";
$DB_Username = "root";
$DB_Password = "";
$DB_DBName = "ishop";
$DB_TBLName = "oi_mall_payment";

$savename = date("YmjHis");
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Couldn't connect.");
mysql_query("Set Names 'gbk'");
$file_type = "vnd.ms-excel";
$file_ending = "xls";
header("Content-Type: application/$file_type;charset=big5");
header("Content-Disposition: attachment; filename=".$savename.".$file_ending");
//header("Pragma: no-cache");

$now_date = date("Y-m-j H:i:s");
$title = "数据库名:$DB_DBName,数据表:$DB_TBLName,备份日期:$now_date";

$sql = "Select * from $DB_TBLName";
$ALT_Db = @mysql_select_db($DB_DBName, $Connect) or die("Couldn't select database");
$result = @mysql_query($sql,$Connect) or die(mysql_error());

echo("$titlen");
$sep = "t";
for ($i = 0; $i < mysql_num_fields($result); $i++) {
echo mysql_field_name($result,$i) . "t";
}
print("n");
$i = 0;
while($row = mysql_fetch_row($result)) {
$schema_insert = "";
for($j=0; $jif(!isset($row[$j]))
$schema_insert .= "NULL".$sep;
elseif ($row[$j] != "")
$schema_insert .= "$row[$j]".$sep;
else
$schema_insert .= "".$sep;
}
$schema_insert = str_replace($sep."$", "", $schema_insert);
$schema_insert .= "t";
print(trim($schema_insert));
print "n";
$i++;
}
return (true);
?>

{ echo "Import data successfully
"; } else { echo ""; } ?> error_reporting(0 ); <🎜>//Import files in CSV format<🎜>$connect=mysql_connect("localhost","a0530093319","123456") or die("could not connect to database"); <🎜>mysql_select_db(" a0530093319",$connect) or die (mysql_error()); <🎜>$fname = $_FILES['MyFile']['name']; <🎜>$handle=fopen("$fname","r") ; <🎜>while($data=fgetcsv($handle,10000,",")) <🎜>{ <🎜>$q="insert into test (code,name,date) values ('$data[0] ','$data[1]','$data[2]')"; <🎜>mysql_query($q) or die (mysql_error()); <🎜><🎜>} <🎜>fclose($handle ); <🎜>?> Used php to export the database to excel, the test was completely successful PHP code www.devdao.com

The following is the quoted content:$ DB_Server = "localhost"; <🎜>$DB_Username = "root"; <🎜>$DB_Password = ""; <🎜>$DB_DBName = "ishop"; <🎜>$DB_TBLName = "oi_mall_payment"; <🎜><🎜 >$savename = date("YmjHis"); <🎜>$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Couldn't connect."); <🎜>mysql_query("Set Names ' gbk'"); <🎜>$file_type = "vnd.ms-excel"; <🎜>$file_ending = "xls"; <🎜>header("Content-Type: application/$file_type;charset=big5"); <🎜>header("Content-Disposition: attachment; filename=".$savename.".$file_ending"); <🎜>//header("Pragma: no-cache"); <🎜><🎜>$now_date = date("Y-m-j H:i:s"); <🎜>$title = "Database name: $DB_DBName, data table: $DB_TBLName, backup date: $now_date"; <🎜><🎜>$sql = "Select * from $DB_TBLName"; <🎜>$ALT_Db = @mysql_select_db($DB_DBName, $Connect) or die("Couldn't select database"); <🎜>$result = @mysql_query($sql,$Connect) or die (mysql_error()); <🎜><🎜>echo("$titlen"); <🎜>$sep = "t"; <🎜>for ($i = 0; $i < mysql_num_fields($result); $i++) { <🎜>echo mysql_field_name($result,$i) . "t"; <🎜>} <🎜>print("n"); <🎜>$i = 0; <🎜>while($row = mysql_fetch_row($result)) { <🎜>$schema_insert = ""; <🎜>for($j=0; $jif(!isset($row [$j])) <🎜>$schema_insert .= "NULL".$sep; <🎜>elseif ($row[$j] != "") <🎜>$schema_insert .= "$row[$j] ".$sep; <🎜>else <🎜>$schema_insert .= "".$sep; <🎜>} <🎜>$schema_insert = str_replace($sep."$", "", $schema_insert); <🎜 >$schema_insert .= "t"; <🎜>print(trim($schema_insert)); <🎜>print "n"; <🎜>$i++; <🎜>} <🎜>return (true); <🎜> ?>

Statement：

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Previous article：Detailed explanation of PHP script and database functions (Part 2)_PHP tutorialNext article：Detailed explanation of PHP script and database functions (Part 2)_PHP tutorial

See more

PHP imports mysql data into Excel table_PHP tutorial

Related articles