Dump data into MySQL from Excel with PHP

There are some factors may affect the result:

CSV file encoding format;

PHP file encoding format;

character set for collation data table in MySQL 

and one line for specifying encoding format when reading CSV file: setlocale(LC_ALL, "zh_TW.UTF-8");

and twoline for MySQL connection collation: 

$SetCharacterSetSql = "SET NAMES 'utf8'";$Recordset1 = mysql_query($SetCharacterSetSql, $conn) or die(mysql_error());

one line is also important:

addslashes($line[2])

<?phpdefine("DB_HOST", "localhost");define("DB_USER", "cosmos");define("DB_PW", "vincent");define("DB_DB", "soccer");$conn = mysql_connect(DB_HOST, DB_USER, DB_PW);if($conn == false){}else{//echo 'vin';$db = mysql_select_db(DB_DB, $conn);if($db == false){}else{setlocale(LC_ALL, "zh_TW.UTF-8");if( !$fp = fopen("teams.csv","r") ){echo "ERROR";exit;}else{$SetCharacterSetSql = "SET NAMES 'utf8'";$Recordset1 = mysql_query($SetCharacterSetSql, $conn) or die(mysql_error());//$row=0;while($line = fgetcsv($fp)){$left_b = "(";$pos = strpos($line[1], $left_b);if( !($pos === false) ){/*echo $line[2];echo "\n";echo "<br>";*/$query = "INSERT INTO jos_bl_teams (t_name) VALUES('".addslashes($line[2])."');";//echo $query."<br>\n";echo $query."\n";//$x = mysql_query($query_s, $conn);//var_dump($x);}}   fclose($fp);  }//echo 'vin';}}?>

INSERT INTO jos_bl_teams (t_name) VALUES('榮冠集團');INSERT INTO jos_bl_teams (t_name) VALUES('溫斯堡');INSERT INTO jos_bl_teams (t_name) VALUES('紐約聯');INSERT INTO jos_bl_teams (t_name) VALUES('SEA-AIR');INSERT INTO jos_bl_teams (t_name) VALUES('豐盛聯');INSERT INTO jos_bl_teams (t_name) VALUES('杰誠');INSERT INTO jos_bl_teams (t_name) VALUES('銀影');INSERT INTO jos_bl_teams (t_name) VALUES('世紀 FC ');INSERT INTO jos_bl_teams (t_name) VALUES('安達 FC');INSERT INTO jos_bl_teams (t_name) VALUES('源禾小器');INSERT INTO jos_bl_teams (t_name) VALUES('光子網絡');INSERT INTO jos_bl_teams (t_name) VALUES('懲教署');INSERT INTO jos_bl_teams (t_name) VALUES('三星');INSERT INTO jos_bl_teams (t_name) VALUES('紅辛子');INSERT INTO jos_bl_teams (t_name) VALUES('紐約聯(II)隊');INSERT INTO jos_bl_teams (t_name) VALUES('米青');INSERT INTO jos_bl_teams (t_name) VALUES('國賢');INSERT INTO jos_bl_teams (t_name) VALUES('大中華');INSERT INTO jos_bl_teams (t_name) VALUES('Honkees');INSERT INTO jos_bl_teams (t_name) VALUES('奇獅');INSERT INTO jos_bl_teams (t_name) VALUES('八旗');INSERT INTO jos_bl_teams (t_name) VALUES('Infinity');INSERT INTO jos_bl_teams (t_name) VALUES('XL UTD');INSERT INTO jos_bl_teams (t_name) VALUES('Nottingham');INSERT INTO jos_bl_teams (t_name) VALUES('創富');INSERT INTO jos_bl_teams (t_name) VALUES('車連');INSERT INTO jos_bl_teams (t_name) VALUES('千葉');INSERT INTO jos_bl_teams (t_name) VALUES('Suntory');INSERT INTO jos_bl_teams (t_name) VALUES('清水');INSERT INTO jos_bl_teams (t_name) VALUES('amass藍鯨');INSERT INTO jos_bl_teams (t_name) VALUES('安永');INSERT INTO jos_bl_teams (t_name) VALUES('域沙利奧');INSERT INTO jos_bl_teams (t_name) VALUES('夏聯');INSERT INTO jos_bl_teams (t_name) VALUES('紐約聯(III)隊');INSERT INTO jos_bl_teams (t_name) VALUES('龍星');INSERT INTO jos_bl_teams (t_name) VALUES('皇朝');INSERT INTO jos_bl_teams (t_name) VALUES('彭博');INSERT INTO jos_bl_teams (t_name) VALUES('青川 FC');INSERT INTO jos_bl_teams (t_name) VALUES('BlackStar FC');INSERT INTO jos_bl_teams (t_name) VALUES('泰寶');INSERT INTO jos_bl_teams (t_name) VALUES('駿豪');INSERT INTO jos_bl_teams (t_name) VALUES('富通');INSERT INTO jos_bl_teams (t_name) VALUES('Purple Power');INSERT INTO jos_bl_teams (t_name) VALUES('寶迪');INSERT INTO jos_bl_teams (t_name) VALUES('Vancity FC');INSERT INTO jos_bl_teams (t_name) VALUES('奔騰');INSERT INTO jos_bl_teams (t_name) VALUES('En\'joy Alliance');INSERT INTO jos_bl_teams (t_name) VALUES('United 144');INSERT INTO jos_bl_teams (t_name) VALUES('CSK');INSERT INTO jos_bl_teams (t_name) VALUES('浩盈');INSERT INTO jos_bl_teams (t_name) VALUES('研華');INSERT INTO jos_bl_teams (t_name) VALUES('青聯');INSERT INTO jos_bl_teams (t_name) VALUES('嘉星 FC');INSERT INTO jos_bl_teams (t_name) VALUES('iepaball.com');INSERT INTO jos_bl_teams (t_name) VALUES('老友記');

Embed a SELECT query inside an INSERT query

INSERT INTO  `jos_bl_players` (  `first_name` ,  `last_name` ,  `team_id` ) SELECT  '姜',  '翰中', idFROM  `jos_bl_teams` WHERE  `t_name` LIKE  '國賢'

Multiple Byte String Process (UTF-8 for example)

<?phpsetlocale(LC_ALL, "zh_TW.UTF-8");if( !$fp = fopen("yinying.csv","r") ){echo "ERROR";exit;}else{$cnt = 0;$lineNo = 0;$teamName;while($line = fgetcsv($fp)){if(1 == $lineNo){//echo $line[17];$length = strlen($line[17]);$teamName = substr($line[17], 2, $length - 2);$teamName = substr($teamName, 0, $length - 4);echo $teamName;echo "<br>";echo "\n";}$sub_str = "XX-";$pos = strpos($line[6], $sub_str);if( !($pos === false) ){/*echo $cnt++;echo "--";echo$line[4];echo "\n";*/$firstname = substr($line[4], 0, 2);/*echo $firstname;echo "\n";//echo utf8_encode(substr($line[4], 0, 2));//echo "\n";*/$lastname = substr($line[4], 2, 4);/*echo $lastname;echo "\n";*//*echo mb_substr($line[4], 0, 2);echo "\n";echo "<br>";*/$query = "INSERT INTO jos_bl_players (first_name , last_name , team_id) "."SELECT '".$firstname."', '".$lastname."', id FROM  jos_bl_teams WHERE  t_name LIKE '".addslashes($teamName)."';";//$query_s = addslashes($query);//echo $query."<br>\n";echo $query."\n";//$x = mysql_query($query_s, $conn);//var_dump($x);}$lineNo++;}fclose($fp);}?>