Dudaoruconfig. PHP page
<? PHP $ db_name = "WordPress"; // if you need to change the database configuration, change $ conn = mysql_connect ("localhost", "root", "root") here "); mysql_select_db ($ db_name, $ conn); mysql_query ("set names 'utf-8'"); // parses the CSV file and returns a two-dimensional array, the first dimension is the total number of rows of CSV data, and the second dimension is the key name CSV column name. The value is the CSV data value at the top of the current row. Function input_csv ($ csv_file) {$ result_arr = array (); $ I = 0; while ($ data_line = fgetcsv ($ csv_file, 10000) {if ($ I = 0) {$ globals ['csv _ key_name_arr '] = $ data_line; $ I ++; continu E;} foreach ($ globals ['csv _ key_name_arr '] as $ csv_key_num => $ csv_key_name) {$ result_arr [$ I] [$ csv_key_name] = $ data_line [$ csv_key_num] ;}$ I ++;} return $ result_arr ;}?> <SCRIPT type = "text/JavaScript" src = "jquery-1.8.2.js"> </SCRIPT>
Dodaoru. php
<form action="doDaoRu2.php" method="post" enctype="multipart/form-data"> <input type="file" name="csv_file" size="50" maxlength="100000" /><br/> <input type="submit" value="submit"/></form>
Dodaoru2.php
<? Phpinclude_once ("dudaoruconfig. PHP "); $ dir = ". /upload/"; if (is_dir ($ DIR) = false) {mkdir ($ Dir, 0777 ); // create an upload folder under the page Directory to save the uploaded CSV file} // 1. Store the CSV file $ csv_filename =$ _ FILES ["csv_file"] ["name"]; move_uploaded_file ($ _ FILES ["csv_file"] ["tmp_name"], ". /upload /". $ _ FILES ["csv_file"] ["name"]); // 2, get all table names $ selalltablename_str = "select table_name from information_schema.tables where table_schema = '$ db_name '"; $ Alltablename_que = mysql_query ($ selalltablename_str); // 3. Obtain all column names of CSV file data $ csv_key_name_arr = array (); // 4, use the CSV column name as the key name to obtain all CSV data $ csv_file = fopen ('upload /'. $ csv_filename, 'R'); $ result_arr = input_csv ($ csv_file); fclose ($ csv_file);?> <Form action = "dodaoru3.php" method = "Post"> column name: <select class = "table_name_sel" name = "table_name_sel"> <option> </option> <? PHP while ($ tablename_row = mysql_fetch_array ($ alltablename_que) {// You can select all table names?> <Option> <? PHP echo $ tablename_row ['table _ name']?> </Option> <? PHP }?> </SELECT> <br/> <? PHP foreach ($ csv_key_name_arr as $ csv_key_name) // list all CSV column names, and select the corresponding table name to import, or do not import the CSV column {?> <Span> <input type = "hidden" name = "<? PHP echo $ csv_key_name;?> "Class =" csv_key_name_hid "value =" "/> <? PHP echo $ csv_key_name;?> <Select class = 'table _ column_name_sel '> <option> </SELECT> </span> <? PHP }?> <Input type = "hidden" name = "csv_filename_hid" value = "<? PHP echo $ csv_filename;?> "/> <Input type =" Submit "value =" Submit "/> </form> <SCRIPT type =" text/JavaScript ">$ (". table_column_name_sel "). change (function () {// when selecting the corresponding table column name for the CSV column name, assign the selected table column name for the CSV hidden Domain value $ (this ). parent (). find ("input "). val ($ (this ). val () ;}) $ (". table_name_sel "). change (function () {$ (". csv_key_name_hid "). val (""); var tablename = $ (this ). val (); var ajaxaddurl = "dodaoruajax. PHP "; // window. location = ajaxaddurl + "? Tablename = "+ tablename; $. post (ajaxaddurl, {'tablename': tablename}, function (jieshou) {$ (". table_column_name_sel option "). remove (); $ (". table_column_name_sel "). append ("<option> </option>"); $. each (jieshou, function (I, n) {$ (". table_column_name_sel "). append ("<option>" + N + "</option>") ;}, "JSON") ;}</SCRIPT>
Dodaoru3.php
<? Phpinclude_once ("dudaoruconfig. PHP "); $ csv_filename = $ _ post ['csv _ filename_hid ']; $ tablename = $ _ post ['table _ name_sel']; // association between columns and columns that save the CSV data to be saved $ table_real_column_name_arr = array (); // 3. All columns for saving CSV file data $ csv_key_name_arr = array (); // obtain all CSV column names and parse data $ csv_file = fopen ('upload /'. $ csv_filename, 'R'); $ result_arr = input_csv ($ csv_file); // Number of CSV data entries (the first row and column names have been deducted) $ result_arr_len = count ($ result_arr ); // associate the CSV column to be saved with the table column fo REACH ($ csv_key_name_arr as $ csv_key_name) {if ($ _ post [$ csv_key_name]) {// determine whether the CSV column on the previous page has been assigned the table column name $ table_real_column_name_arr [$ csv_key_name] =$ _ post [$ csv_key_name] ;}// use batch insert, piece together all the data to be inserted into an SQL statement with a long string $ all_insert_data_value_str = ""; for ($ I = 1; $ I <= $ result_arr_len; $ I ++) {// obtain the Field Values cyclically $ csv_line_data_value = ""; $ j = 1; foreach ($ table_real_column_name_arr as $ csv_real_key_name => $ table_real_column_name) {if ($ J = Count ($ table_real_column_name_arr) {$ csv_line_data_value. = "'". $ result_arr [$ I] [$ csv_real_key_name]. "'";} else {$ csv_line_data_value. = "'". $ result_arr [$ I] [$ csv_real_key_name]. "'," ;}$ J ++;} $ all_insert_data_value_str. = "($ csv_line_data_value)," ;}$ all_insert_data_value_str = substr ($ all_insert_data_value_str, 0,-1 ); // remove the last comma // piece together all field names that need to be inserted into a long string to $ all_insert_column_name_str =" "; $ I = 1; foreach ($ table_real_column_name_arr as $ csv_real_key_name => $ table_real_column_name) {if ($ I = count ($ table_real_column_name_arr) {$ region. = "$ table_real_column_name";} else {$ all_insert_column_name_str. = "$ table_real_column_name," ;}$ I ++;} // problem. If a carriage return or line break occurs in the text, the import of escape characters in the string fails, 2 Chinese garbled characters // execute batch insert. CSV import is complete $ query = mysql_query ("insert into $ tablename ($ all_insert_column_name_s Tr) values $ all_insert_data_value_str "); // insert fclose ($ csv_file) into the data table in batches; if ($ query) {echo 'import successful! ';} Else {echo' import failed! ';}?>
Dodaoruajax. php
<? Phpinclude_once ("dudaoruconfig. PHP "); $ talbename = $ _ post ['tablename']; // query all columns in the table $ selallcolumns_str =" show columns from $ talbename "; $ allcolumnsname_que = mysql_query ($ selallcolumns_str); $ response = array (); $ I = 0; while ($ allcolumnsname_row = mysql_fetch_array ($ allcolumnsname_que )) {$ allcolumnsname_arr [$ I] = $ allcolumnsname_row ['field']; $ I ++;} ob_end_clean (); echo json_encode ($ allcolumnsname _ ARR);?>