We first prepare the MySQL datasheet, assuming that the project has a record of student information on the table student, and have id,name,sex,age record the student's name, gender, age and other information.
Copy Code code as follows:
CREATE TABLE ' Student ' (
' id ' int (one) not NULL auto_increment,
' Name ' varchar not NULL,
' Sex ' varchar not NULL,
' Age ' smallint (3) Not NULL default ' 0 ',
PRIMARY KEY (' id ')
) Engine=myisam DEFAULT Charset=utf8;
We also need an HTML interactive page to place the import form and export button.
Copy Code code as follows:
<form id= "AddForm" action= "Do.php?action=import" method= "post" enctype= "Multipart/form-data" >
<p> Please select the CSV file to import: <br/><input type= "file" name= "file" > <input type= "Submit"
class= "BTN" value= "Import csv" >
<input type= "button" class= "BTN" value= "Export csv" onclick= "window.location.href=" do.php?
Action=export ' "></p>
</form>
Select a good local CSV file, click Import, submit to Do.php?action=import processing, and click the Export button to request address Do.php?action=export data export processing.
1. Import CSV
Do.php needs to process the import and export process separately according to the parameters of Get, PHP structure as follows:
Copy Code code as follows:
Include_once ("connect.php"); Connecting to a database
$action = $_get[' action '];
if ($action = = ' import ')//importing CSV
{
Import processing
}elseif ($action = = ' export ')//exporting CSV
{
Export Processing
}
Import CSV processing process: Verify CSV file legality (omitted in this article)-> opens the read in and resolves the fields in the CSV file-> loops get each field value-> the batch add to the datasheet-> done.
Copy Code code as follows:
if ($action = = ' Import ') {//import CSV
$filename = $_files[' file ' [' Tmp_name '];
if (Emptyempty ($filename))
{
Echo ' Please select the CSV file you want to import! ';
Exit
}
$handle = fopen ($filename, ' R ');
$result = Input_csv ($handle); Parsing CSV
$len _result = count ($result);
if ($len _result==0)
{
Echo ' doesn't have any data! ';
Exit
}
for ($i = 1; $i < $len _result $i + +)//loop get each field value
{
$name = Iconv (' gb2312 ', ' utf-8 ', $result [$i][0]); Chinese transfer Code
$sex = Iconv (' gb2312 ', ' utf-8 ', $result [$i][1]);
$age = $result [$i][2];
$data _values. = "(' $name ', ' $sex ', ' $age '),";
}
$data _values = substr ($data _values,0,-1); Remove the last comma
Fclose ($handle); Close pointer
$query = mysql_query ("INSERT into student (name,sex,age) values $data _values"); Bulk INSERT in Datasheet
if ($query)
{
echo ' Import successful! ';
}else{
echo ' Import failed! ';
}
}
Note that PHP's fgetcsv function makes it easy to work with CSV, which allows you to read a line from the file pointer and parse the CSV field. The following function parses the CSV file field and returns it as an array.
Copy Code code as follows:
function Input_csv ($handle)
{
$out = Array ();
$n = 0;
while ($data = Fgetcsv ($handle, 10000))
{
$num = count ($data);
for ($i = 0; $i < $num; $i + +)
{
$out [$n] [$i] = $data [$i];
}
$n + +;
}
return $out;
}
In addition, when importing into a database, we use bulk inserts instead of inserts, so when building SQL statements, we need to do a little processing, see code.
2. Export CSV
We know that a CSV file is a plain text file made up of comma-delimited characters, and you can open it in Excel with the same effect as the XLS table.
Export CSV processing flow: Read Student information table-> Circular record build comma-delimited field information-> set header information-> export file (download) to local
Copy Code code as follows:
...
}elseif ($action = = ' export ')//exporting CSV
{
$result = mysql_query ("SELECT * FROM student-ID ASC");
$str = "name, sex, age \ n";
$str = Iconv (' utf-8 ', ' gb2312 ', $str);
while ($row =mysql_fetch_array ($result))
{
$name = Iconv (' utf-8 ', ' gb2312 ', $row [' name ']); Chinese transfer Code
$sex = Iconv (' utf-8 ', ' gb2312 ', $row [' sex ']);
$str. = $name. ",". $sex. "," $row [' age ']. " \ n "; Separate with quotation commas
}
$filename = Date (' Ymd '). CSV '; Set file name
Export_csv ($filename, $STR); Export
}
To export the data locally, you need to modify the header information as follows:
Copy Code code as follows:
function Export_csv ($filename, $data)
{
Header ("Content-type:text/csv");
Header ("Content-disposition:attachment;filename=". $filename);
Header (' cache-control:must-revalidate,post-check=0,pre-check=0 ');
Header (' expires:0 ');
Header (' Pragma:public ');
Echo $data;
}
Note that the import and export process, because we are using a unified UTF-8 code, encountered in Chinese characters must remember to turn code, otherwise it may appear in the case of garbled characters.
OK, this article explains this, I will also an article introduce PHP combined with MySQL Import export Excel, as well as the import and export of XML, please pay attention to.
