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])

<?php
define("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  '姜',  '翰中', id
FROM  `jos_bl_teams` 
WHERE  `t_name` LIKE  '國賢'

Multiple Byte String Process (UTF-8 for example)

<?php
setlocale(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);
}
?>