import ข้อมูล excel ลง mysql ขึ้น Upload & Import Done. แต่ไม่มีข้อมูลค่ะ
ดูบนความจาก PHP Upload and Convert/Import CSV To MySQL DatabaseCode (PHP) <html>
<head>
<title>ThaiCreate.Com PHP & CSV To MySQL</title>
</head>
<body>
<?
copy($_FILES["fileCSV"]["tmp_name"],$_FILES["fileCSV"]["name"]); // Copy/Upload CSV
$objConnect = mysql_connect("localhost","root","1234") or die("Error Connect to Database"); // Conect to MySQL
$objDB = mysql_select_db("tiew");
$objCSV = fopen("TH.csv", "r");
while (($objArr = fgetcsv($objCSV, 1000, ",")) !== FALSE) {
$strSQL = "INSERT INTO hotel (";
$strSQL .= " hotel_id, ";
$strSQL .= " chain_id, ";
$strSQL .= " chain_name, ";
$strSQL .= " brand_id, ";
$strSQL .= " brand_name, ";
$strSQL .= " hotel_name, ";
$strSQL .= " hotel_formerly_name, ";
$strSQL .= " hotel_translated_name, ";
$strSQL .= " addressline1, ";
$strSQL .= " addressline2, ";
$strSQL .= " zipcode, ";
$strSQL .= " city, ";
$strSQL .= " state, ";
$strSQL .= " country, ";
$strSQL .= " countryisocode, ";
$strSQL .= " star_rating, ";
$strSQL .= " longitude, ";
$strSQL .= " latitude, ";
$strSQL .= " url, ";
$strSQL .= " checkin, ";
$strSQL .= " checkout, ";
$strSQL .= " numberrooms, ";
$strSQL .= " numberfloors, ";
$strSQL .= " yearopened, ";
$strSQL .= " yearrenovated, ";
$strSQL .= " photo1, ";
$strSQL .= " photo2, ";
$strSQL .= " photo3, ";
$strSQL .= " photo4, ";
$strSQL .= " photo5, ";
$strSQL .= " overview, ";
$strSQL .= " rates_from, ";
$strSQL .= " continent_id, ";
$strSQL .= " continent_name, ";
$strSQL .= " city_id, ";
$strSQL .= " country_id, ";
$strSQL .= " number_of_reviews, ";
$strSQL .= " rating_average, ";
$strSQL .= " rates_currency, ";
$strSQL .= " site_name )";
$strSQL .="VALUES ";
$strSQL .="('".$objArr[0]."','".$objArr[1]."','".$objArr[2]."' ";
$strSQL .="('".$objArr[3]."','".$objArr[4]."','".$objArr[5]."' ";
$strSQL .="('".$objArr[6]."','".$objArr[7]."','".$objArr[8]."' ";
$strSQL .="('".$objArr[9]."','".$objArr[10]."','".$objArr[11]."' ";
$strSQL .="('".$objArr[12]."','".$objArr[13]."','".$objArr[14]."' ";
$strSQL .="('".$objArr[15]."','".$objArr[16]."','".$objArr[17]."' ";
$strSQL .="('".$objArr[18]."','".$objArr[19]."','".$objArr[20]."' ";
$strSQL .="('".$objArr[21]."','".$objArr[22]."','".$objArr[23]."' ";
$strSQL .="('".$objArr[24]."','".$objArr[25]."','".$objArr[26]."' ";
$strSQL .="('".$objArr[27]."','".$objArr[28]."','".$objArr[29]."' ";
$strSQL .="('".$objArr[30]."','".$objArr[30]."','".$objArr[32]."' ";
$strSQL .="('".$objArr[33]."','".$objArr[34]."','".$objArr[35]."' ";
$strSQL .="('".$objArr[36]."' ";
$strSQL .=",'".$objArr[37]."','".$objArr[38]."','".$objArr[39]."') ";
$objQuery = mysql_query($strSQL);
}
fclose($objCSV);
echo "Upload & Import Done.";
?>
</table>
</body>
</html>
Tag PHP, MySQL
ประวัติการแก้ไข
Date :
2015-08-21 19:39:40By :
bonbonView :
3178Reply :
9
56.$strSQL .="( '".$objArr[0]."','".$objArr[1]."','".$objArr[2]."' ";Code (PHP) function insert_string($tb, $ar){
$fld=array(); $value=array();
foreach($ar as $k=>$vl){
$fld[]=$k;
$value[]=preg_match('/^[0-9\.]+$/', $vl)? $vl : mysql_real_escape_string($vl);
}
return "insert into $tb (`".implode('`,`',$fld)."`) values ('".implode("','", $value)."')";
}
$ar = array(
'field1'=>$value1,
'field2'=>$value2
);
$insertSQL = insert_string('tablename', $ar);
or die(mysql_error() . "<br>$strSQL) ;
ประวัติการแก้ไข
Date :
2015-08-22 05:27:38By :
NewbiePHP
ทำได้แล้วค่ะขอบคุณมากๆค่ะมาที่ไรไม่เคยผิดหวัง รบกวนถามอีกนิดค่ะ ถ้ามีข้อมูลชุดใหม่มาอีกมันก็จะต่อข้อมูลเดิมใช่ไมค่ะและมันจำกันไม่ว่าmaxที่กีไฟจุเท่าไรค่ะ
ประวัติการแก้ไข
Date :
2015-08-22 19:06:31By :
nudsara
insert จะบวกเพิ่ม เรคคอร์ดต่อไปครับ
Date :
2015-08-23 06:26:14By :
NewbiePHP
ขอโทษน่ะค่ะถ้าอยากให้มันอับไฟล์ที่เราเลือกเลย ในส่วนนี้ต้องใส่เป็นอ่ะไรค่ะCode (PHP) 12.
$objCSV = fopen("TH.csv", "r");
Date :
2015-08-25 22:05:12By :
nudsara
ส่งเป็นตัวแปรไปครับCode (PHP) <?php
// yourprogram.php
....
....
....
if( !file_exists($_GET['filename'])){
echo 'File Not Found'; exit;
}
$objCSV = fopen($_GET['filename'], "r");
Code (PHP) <?php
// yourprogram.php
if( !move_uploaded_file($_FILES["fileCSV"]["tmp_name"],$_FILES["fileCSV"]["name"]) ){
echo 'Upload File is Error'; exit;
}
....
....
....
$objCSV = fopen($_FILES["fileCSV"]["name"], "r");
ประวัติการแก้ไข
Date :
2015-08-26 05:38:45By :
NewbiePHP
ลองทำแล้วค่ะ ติด Upload File is Error ค่ะ ถ้าเอา if ออก ข้อมูลก็ลง ฐานข้อมูลนะค่ะ แต่เป็นค่าว่างค่ะ นู๋ต้องแก่อย่างไรค่ะรบกวนด้วยนะค่ะเพิ่งหัดเขียนได้ไม่นานค่ะCode (PHP) <?
function insert_string($tb, $ar){
$fld=array(); $value=array();
foreach($ar as $k=>$vl){
$fld[]=$k;
$value[]=preg_match('/^[0-9\.]/', $vl)? $vl : mysql_real_escape_string($vl);
}
return "insert into $tb (`".implode('`,`',$fld)."`) values ('".implode("','", $value)."')";
}
$ar = array(
'field1'=>$value1,
'field2'=>$value2
);
$insertSQL = insert_string('tablename', $ar);
if( !move_uploaded_file($_FILES["fileCSV"]["tmp_name"],$_FILES["fileCSV"]["name"]) ){
echo 'Upload File is Error'; exit;
}
$objConnect = mysql_connect("localhost","root","1234") or die("Error Connect to Database"); // Conect to MySQL
mysql_query("SET NAMES TIS620");
mysql_query("SET character_set_results=tis620");
$objDB = mysql_select_db("tiew");
$objCSV = fopen($_FILES["fileCSV"]["name"], "r");
Date :
2015-08-26 22:07:48By :
nudsara
น่าจะเปลี่ยนเป็น mysqli ได้แล้วนะครับ และการเขียนโค๊ดเพื่อให้อ่านแล้วเข้าใจง่ายCode (PHP) <?php
// class นี้เอามาจาก PHP document
class UploadException extends Exception{
public function __construct($code) {
$message = $this->codeToMessage($code);
parent::__construct($message, $code);
}
private function codeToMessage($code){
switch ($code) {
case UPLOAD_ERR_INI_SIZE:
$message = "The uploaded file exceeds the upload_max_filesize directive in php.ini";
break;
case UPLOAD_ERR_FORM_SIZE:
$message = "The uploaded file exceeds the MAX_FILE_SIZE directive that was specified in the HTML form";
break;
case UPLOAD_ERR_PARTIAL:
$message = "The uploaded file was only partially uploaded";
break;
case UPLOAD_ERR_NO_FILE:
$message = "No file was uploaded";
break;
case UPLOAD_ERR_NO_TMP_DIR:
$message = "Missing a temporary folder";
break;
case UPLOAD_ERR_CANT_WRITE:
$message = "Failed to write file to disk";
break;
case UPLOAD_ERR_EXTENSION:
$message = "File upload stopped by extension";
break;
default:
$message = "Unknown upload error";
break;
}
return $message;
}
}
function insert_string($tb, $ar){
$fld=array(); $value=array();
foreach($ar as $k=>$vl){
$fld[]=$k;
$value[]=preg_match('/^[0-9\.]/', $vl)? $vl : mysql_real_escape_string($vl);
}
return "insert into $tb (`".implode('`,`',$fld)."`) values ('".implode("','", $value)."')";
}
$ar = array(
'field1'=>$value1,
'field2'=>$value2
);
/////////////////////////////////////////////////
if($_FILES['fileCSV']['error'] !== UPLOAD_ERR_OK){
throw new UploadException($_FILES['fileCSV']['error']); exit;
}
if( !move_uploaded_file($_FILES["fileCSV"]["tmp_name"],$_FILES["fileCSV"]["name"]) ){
echo 'Upload File is Error'; exit;
}
/////////////////////////////////////////////////
$insertSQL = insert_string('tablename', $ar);
/////////////////////////////////////////////////
$db = new mysqli("localhost","root","1234", "tiew" ) or die("Error Connect to Database"); // Conect to MySQL
$db->query("SET NAMES TIS620");
$db->query("SET character_set_results=tis620");
/////////////////////////////////////////////////
$objCSV = fopen($_FILES["fileCSV"]["name"], "r");
Date :
2015-08-26 22:27:18By :
NewbiePHP
วางแบบนี้ใช่ไมค่ะ นู๋พยามทำมาวันนี้วันที่2และค่ะยัง ติด อยู่ค่ะช่วยแนะนู๋ด้วยนะค่ะCode (PHP) <?php
// class นี้เอามาจาก PHP document
class UploadException extends Exception{
public function __construct($code) {
$message = $this->codeToMessage($code);
parent::__construct($message, $code);
}
private function codeToMessage($code){
switch ($code) {
case UPLOAD_ERR_INI_SIZE:
$message = "The uploaded file exceeds the upload_max_filesize directive in php.ini";
break;
case UPLOAD_ERR_FORM_SIZE:
$message = "The uploaded file exceeds the MAX_FILE_SIZE directive that was specified in the HTML form";
break;
case UPLOAD_ERR_PARTIAL:
$message = "The uploaded file was only partially uploaded";
break;
case UPLOAD_ERR_NO_FILE:
$message = "No file was uploaded";
break;
case UPLOAD_ERR_NO_TMP_DIR:
$message = "Missing a temporary folder";
break;
case UPLOAD_ERR_CANT_WRITE:
$message = "Failed to write file to disk";
break;
case UPLOAD_ERR_EXTENSION:
$message = "File upload stopped by extension";
break;
default:
$message = "Unknown upload error";
break;
}
return $message;
}
}
function insert_string($tb, $ar){
$fld=array(); $value=array();
foreach($ar as $k=>$vl){
$fld[]=$k;
$value[]=preg_match('/^[0-9\.]/', $vl)? $vl : mysql_real_escape_string($vl);
}
return "insert into $tb (`".implode('`,`',$fld)."`) values ('".implode("','", $value)."')";
}
$ar = array(
'field1'=>$value1,
'field2'=>$value2
);
/////////////////////////////////////////////////
if($_FILES['fileCSV']['error'] !== UPLOAD_ERR_OK){
throw new UploadException($_FILES['fileCSV']['error']); exit;
}
if( !move_uploaded_file($_FILES["fileCSV"]["tmp_name"],$_FILES["fileCSV"]["name"]) ){
echo 'Upload File is Error'; exit;
}
/////////////////////////////////////////////////
$insertSQL = insert_string('tablename', $ar);
/////////////////////////////////////////////////
$db = new mysqli("localhost","root","1234", "tiew" ) or die("Error Connect to Database"); // Conect to MySQL
$db->query("SET NAMES TIS620");
$db->query("SET character_set_results=tis620");
/////////////////////////////////////////////////
$objCSV = fopen($_FILES["fileCSV"]["name"], "r");
while (($objArr = fgetcsv($objCSV, 1000, ",")) !== FALSE) {
$strSQL = "INSERT INTO tb_hotel (";
$strSQL .= " hotel_id, ";
$strSQL .= " chain_id, ";
$strSQL .= " chain_name, ";
$strSQL .= " brand_id, ";
$strSQL .= " brand_name, ";
$strSQL .= " hotel_name, ";
$strSQL .= " hotel_formerly_name, ";
$strSQL .= " hotel_translated_name, ";
$strSQL .= " addressline1, ";
$strSQL .= " addressline2, ";
$strSQL .= " zipcode, ";
$strSQL .= " city, ";
$strSQL .= " state, ";
$strSQL .= " country, ";
$strSQL .= " countryisocode, ";
$strSQL .= " star_rating, ";
$strSQL .= " longitude, ";
$strSQL .= " latitude, ";
$strSQL .= " url, ";
$strSQL .= " checkin, ";
$strSQL .= " checkout, ";
$strSQL .= " numberrooms, ";
$strSQL .= " numberfloors, ";
$strSQL .= " yearopened, ";
$strSQL .= " yearrenovated, ";
$strSQL .= " photo1, ";
$strSQL .= " photo2, ";
$strSQL .= " photo3, ";
$strSQL .= " photo4, ";
$strSQL .= " photo5, ";
$strSQL .= " overview, ";
$strSQL .= " rates_from, ";
$strSQL .= " continent_id, ";
$strSQL .= " continent_name, ";
$strSQL .= " city_id, ";
$strSQL .= " country_id, ";
$strSQL .= " number_of_reviews, ";
$strSQL .= " rating_average, ";
$strSQL .= " rates_currency, ";
$strSQL .= " site_name, ";
$strSQL .= " language )";
$strSQL .="VALUES ";
$strSQL .="('".$objArr[0]."','".$objArr[1]."','".$objArr[2]."' ";
$strSQL .=",'".$objArr[3]."','".$objArr[4]."','".$objArr[5]."' ";
$strSQL .=",'".$objArr[6]."','".$objArr[7]."','".$objArr[8]."' ";
$strSQL .=",'".$objArr[9]."','".$objArr[10]."','".$objArr[11]."' ";
$strSQL .=",'".$objArr[12]."','".$objArr[13]."','".$objArr[14]."' ";
$strSQL .=",'".$objArr[15]."','".$objArr[16]."','".$objArr[17]."' ";
$strSQL .=",'".$objArr[18]."','".$objArr[19]."','".$objArr[20]."' ";
$strSQL .=",'".$objArr[21]."','".$objArr[22]."','".$objArr[23]."' ";
$strSQL .=",'".$objArr[24]."','".$objArr[25]."','".$objArr[26]."' ";
$strSQL .=",'".$objArr[27]."','".$objArr[28]."','".$objArr[29]."' ";
$strSQL .=",'".$objArr[30]."','".$objArr[30]."','".$objArr[32]."' ";
$strSQL .=",'".$objArr[33]."','".$objArr[34]."','".$objArr[35]."' ";
$strSQL .=",'".$objArr[36]."','".$objArr[37]."' ";
$strSQL .=",'".$objArr[38]."','".$objArr[39]."','".$objArr[40]."') ";
$objQuery = mysql_query($strSQL) or die(mysql_error() . "<br>$strSQL");
}
fclose($objCSV);
echo "Upload & Import Done.";
?>
ประวัติการแก้ไข
Date :
2015-08-28 20:30:24By :
nudsara
โค๊ดข้างล่างมีการปรับแก้ class UploadException ให้ทำงาน อิสระ ไม่ inherit มาจากคำสั่งเดิมCode (PHP) <?php
//////////////////////// get Error message //////////////////
// class นี้ แปลงมาจาก PHP document UploadException
class Upload_Exception{
public $message;
public function __construct($code) {
$this->message=$this->codeToMessage($code);
}
public function codeToMessage($code){
switch ($code) {
case UPLOAD_ERR_INI_SIZE:
$message = "The uploaded file exceeds the upload_max_filesize directive in php.ini";
break;
case UPLOAD_ERR_FORM_SIZE:
$message = "The uploaded file exceeds the MAX_FILE_SIZE directive that was specified in the HTML form";
break;
case UPLOAD_ERR_PARTIAL:
$message = "The uploaded file was only partially uploaded";
break;
case UPLOAD_ERR_NO_FILE:
$message = "No file was uploaded";
break;
case UPLOAD_ERR_NO_TMP_DIR:
$message = "Missing a temporary folder";
break;
case UPLOAD_ERR_CANT_WRITE:
$message = "Failed to write file to disk";
break;
case UPLOAD_ERR_EXTENSION:
$message = "File upload stopped by extension";
break;
default:
$message = "Unknown upload error";
break;
}
return $message;
}
}
////////////////////// create statement //////////////////////////////
function insert_string($tb, $ar){
$fld=array(); $value=array(); global $db;
foreach($ar as $k=>$vl){
$fld[]=$k;
$value[]=preg_match('/^[0-9\.]/', $vl)? $vl : $db->real_escape_string($vl);
}
return "insert into $tb (`".implode('`,`',$fld)."`) values ('".implode("','", $value)."')";
}
///////////////// checking error and move uploaded file ///////////////////
if(isset($_FILES['fileCSV'])){
die('There is empty File Upload');
}
if($_FILES['fileCSV']['error'] !== UPLOAD_ERR_OK){
$upErr=new Upload_Exception($_FILES['fileCSV']['error']); exit;
}
if( !move_uploaded_file($_FILES["fileCSV"]["tmp_name"],$_FILES["fileCSV"]["name"]) ){
echo 'Upload File is Error'; exit;
}
//////////////// load csv file /////////////////////////////////
$objCSV = fopen($_FILES["fileCSV"]["name"], "r");
while (($objArr = fgetcsv($objCSV, 1000, ",")) !== FALSE) {
$arSql= array(
'hotel_id'=>$objArr[0], 'chain_id'=>$objArr[1], 'chain_name'=>$objArr[2],
'brand_id'=>$objArr[3], 'brand_name'=>$objArr[4], 'hotel_name'=>$objArr[5],
'hotel_formerly_name'=>$objArr[6], 'hotel_translated_name'=>$objArr[7],
'addressline1'=>$objArr[8], 'addressline2'=>$objArr[9], 'zipcode'=>$objArr[10],
'city'=>$objArr[11], 'state'=>$objArr[12], 'country'=>$objArr[13],
'countryisocode'=>$objArr[14], 'star_rating'=>$objArr[15], 'longitude'=>$objArr[16],
'latitude'=>$objArr[17], 'url'=>$objArr[18], 'checkin'=>$objArr[19],
'checkout'=>$objArr[20], 'numberrooms'=>$objArr[21], 'numberfloors'=>$objArr[22],
'yearopened'=>$objArr[23], 'yearrenovated'=>$objArr[24], 'photo1'=>$objArr[25],
'photo2'=>$objArr[26], 'photo3'=>$objArr[27], 'photo4'=>$objArr[28],
'photo5'=>$objArr[29], 'overview'=>$objArr[30], 'rates_from'=>$objArr[31],
'continent_id'=>$objArr[32], 'continent_name'=>$objArr[33], 'city_id'=>$objArr[34],
'country_id'=>$objArr[35], 'number_of_reviews'=>$objArr[36], 'rating_average'=>$objArr[37],
'rates_currency'=>$objArr[38], 'site_name'=>$objArr[39], 'language'=>$objArr[40]
);
}
fclose($objCSV);
/////////////////// insert to database //////////////////////////////
$db = new mysqli("localhost","root","1234", "tiew" ) or die("Error Connect to Database"); // Conect to MySQL
$db->query("SET NAMES TIS620");
$db->query("SET character_set_results=tis620");
$db=>query($sql=insert_string('tb_hotel', $arSql)) or die($db->error . "<br>$sql");
/////////////////////////////////////////////////
echo "Upload & Import Done.";
?>
Date :
2015-08-29 00:43:57By :
NewbiePHP
Server 05
Load balance : Server 05
