Use PHP to import mysql data to Excel

Sometimes, when writing a program, the background requires that a large amount of data be imported into the database, such as exam scores and phone books, which are generally stored in excel. at this time, we can export the excel file to a csv file, then you can import data

Sometimes, when writing a program, the background requires that a large amount of data be imported into the database, such as exam scores and phone books, which are generally stored in excel. at this time, we can export the excel file to a csv file, then you can import data to the database in batches using the following procedure:

Upload cvs and import it to the database. the test is successful (some codes are not standard, such as PHP_SELF, which must be rewritten to $ _ SERVER ["PHP_SELF"]).
PHP code

$ Fname = $ _ FILES ['myfile'] ['name']; $ Do = copy ($ _ FILES ['myfile'] ['tmp _ name'], $ fname ); If ($ do) { Echo "data imported successfully "; } Else { Echo ""; } ?> Error_reporting (0 ); // Import a CSV file $ Connect = mysql_connect ("localhost", "a0530093319", "123456") or die ("cocould 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 ); ?>

Export the database to excel using php. the test is successful.
PHP code

$ 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 ("$ title \ n "); $ 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; $ j If (! 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 ); ?>