สอบถามคะการ import file .CSV เข้าสู่ Database mySQL ค่ะ
คือลองทำตัว drop down list แล้วต้องการจะดึงค่าจาก drop down มาใส่ใน phpmyadmin แต่มันติดปัญหา อัพโฟลดไฟล์ csv ไปแล้วก็ยังได้แบบนี้คะ ช่องmajor ไม่ขึ้นอะคะ แล้วรายชื่ออกมาได้แค่ชื่อเดียวอะคะ ทั้งๆที่ในไฟล์ csv มีรายชื่ออยู่หลายชื่อคะอันนี้คือส่วนโค้ดคะ config.php Code (PHP) <?php
$host = "localhost"; // ส่วนมากมักเป็น localhost
$user = "root"; // Username
$pass = ""; // Password
$dbname = "project"; // ชื่อฐานข้อมูล
function conndb() {
global $conn;
global $host;
global $user;
global $pass;
global $dbname;
$conn = mysql_connect($host,$user,$pass);
mysql_query("SET NAMES UTF8");
mysql_select_db($dbname) ;
if (!$conn)
die("ไม่สามารถติดต่อกับฐานข้อมูลได้");
mysql_select_db($dbname,$conn)
or die("ไม่สามารถเลือกใช้งานฐานข้อมูลได้");
}
function closedb() {
global $conn;
mysql_close($conn);
}
?>
localtion.php Code (PHP) <?php
include "config.php";
conndb();
$data = $_GET['data'];
$val = $_GET['val'];
if($data=='degree') {
echo "<select name='Degree' onChange=\"dochange('faculty', this.value)\">";
echo "<option value='0'>- ระดับปริญญา -</option>\n";
$result = mysql_query("SELECT * FROM degree ");
while($row = mysql_fetch_array($result)){
echo "<option value=\"$row[id_degree]\" >$row[degree_name]</option> ";
}
}else if($data=='faculty') {
echo "<select name='faculty' onChange=\"dochange('major', this.value)\">";
echo "<option value='0'>- คณะ -</option>\n";
$result = mysql_query("SELECT * FROM faculty WHERE Degree = '$val' GROUP BY FaName ");
while($row = mysql_fetch_array($result)){
echo "<option value=\"$row[FaNumber]\" >$row[FaName]</option> ";
}
} else if ($data=='major') {
echo "<select name='mojor'>\n";
echo "<option value='0'>- สาขาวิชา -</option>\n";
$result = mysql_query("SELECT * FROM major WHERE FaNumber= '$val' GROUP BY MName");
while($row = mysql_fetch_array($result)){
echo "<option value=\"$row[MNumber]\" >$row[MName]</option> \n" ;
}
}
echo "</select>\n";
echo mysql_error();
closedb();
?>
UploadCsv.php Code (PHP) <?
include "config.php";
?>
<!DOCTYPE html>
<html>
<head>
<script language="JavaScript">
function OnUploadCheck()
{
var extall="csv";
file = document.form.file.value;
ext = file.split('.').pop().toLowerCase();
if(parseInt(extall.indexOf(ext)) < 0)
{
alert('Extension support only : ' + extall + 'file');
return false;
}
return true;
}
</script>
<meta http-equiv=Content-Type content="text/html; charset=utf-8">
<script href="bootstrap/js/bootstrap.min.js" rel="stylesheet"></script>
<link href="bootstrap/css/bootstrap.min.css" rel="stylesheet">
<link href="bootstrap/css/bootstrap-responsive.min.css" rel="stylesheet">
<link rel="stylesheet" type="text/css" href="bootstrap/css/style.css" />
<script src="bootstrap/js/jquery.1.9.1.min.js" rel="stylesheet"></script>
<script src="bootstrap/js/jquery.validate.min.js" rel="stylesheet"></script>
<script src="bootstrap/js/bootstrap.validate.min.js" rel="stylesheet"></script>
<link href="bootstrap/font-awesome/css/font-awesome.min.css" rel="stylesheet">
<link href='http://fonts.googleapis.com/css?family=Roboto:300' rel='stylesheet' type='text/css'>
<script src="bootstrap/FileUpload/bootstrap-fileupload.js" type="text/javascript"></script>
<script language=Javascript>
function Inint_AJAX() {
try { return new ActiveXObject("Msxml2.XMLHTTP"); } catch(e) {} //IE
try { return new ActiveXObject("Microsoft.XMLHTTP"); } catch(e) {} //IE
try { return new XMLHttpRequest(); } catch(e) {} //Native Javascript
alert("XMLHttpRequest not supported");
return null;
};
function dochange(src, val) {
var req = Inint_AJAX();
req.onreadystatechange = function () {
if (req.readyState==4) {
if (req.status==200) {
document.getElementById(src).innerHTML=req.responseText; //รับค่ากลับมา
}
}
};
req.open("GET", "localtion.php?data="+src+"&val="+val); //สร้าง connection
req.setRequestHeader("Content-Type", "application/x-www-form-urlencoded;charset=utf-8"); // set Header
req.send(null); //ส่งค่า
}
window.onLoad=dochange('degree', -1);
</script>
</head>
</br></br>
<center>
<img src="bn.png">
<body>
<form method="post" name="form" action="SaveCsv.php" enctype="multipart/form-data" onSubmit="return OnUploadCheck();">
<center>
<span id="degree" >
<select>
<option value='0'>- ระดับปริญญา -</option>
</select>
</span>
<span id="faculty">
<select>
<option value='0'>- คณะ -</option>
</select>
</span>
<span id="major">
<select>
<option value='0'>- สาขาวิชา -</option>
</select>
</span>
</center>
</br> <center> <font size="+3"> Upload File Here </font></center> </br>
<center>
<div class="fileupload fileupload-new" data-provides="fileupload">
<div class="input-append">
<div class="uneditable-input span3"><i class="icon-file fileupload-exists"></i><font size="3" style="letter-spacing: 0.8px;"> <span class="fileupload-preview"></span></font></div>
<span class="btn btn-file">
<span class="fileupload-new"><i class="icon-folder-open"></i>
<font size="3" style="letter-spacing: 0.8px;">Select file</font></span>
<span class="fileupload-exists"><i class="icon-folder-open"></i>
<font size="3" style="letter-spacing: 0.8px;">Change</font></span>
<input type="file" id="file" name="file">
</span>
<a class="btn fileupload-exists" data-dismiss="fileupload"><i class="icon-remove"></i>
<font size="3" style="letter-spacing: 0.8px;">Remove</font></a>
</div>
</div>
<button class="btn" name="submit" value="submit" type="submit">
<i class="icon-ok"></i><font size="3" style="letter-spacing: 0.8px;"> Submit</font></button>
<button type="button" class="btn" name="submit" value="Submit" onClick="window.location='SaveCsv.php'">
<i class="icon-remove"></i><font size="3" style="letter-spacing: 0.8px;"> Cancel</font></button>
</div>
</center>
</form>
</body>
</html>
<style type="text/css">
body{
background-color:#FF9;
}
</style>
SaveCsv.php Code (PHP) <!DOCTYPE html>
<html>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<?php
include "config.php";
conndb();
"degree=".$degree = $_POST['Degree'];
"<br/>";
"FaNumber=".$FaNumber = $_POST['faculty'];
"<br/>";
"MNumber=".$MNumber = $_POST['major'];
"<hr>";
$sql_1 = "SELECT * FROM degree WHERE id_degree = '$degree' ";
$result_1 = mysql_query($sql_1);
$row_1 = mysql_fetch_array($result_1);
$degree = $row_1['degree_name'];
$sql_2 = "SELECT * FROM faculty WHERE FaNumber = '$FaNumber' ";
$result_2 = mysql_query($sql_2);
$row_2 = mysql_fetch_array($result_2);
$FaName = $row_2['FaName'];
$sql_3 = "SELECT * FROM major WHERE MNumber = '$MNumber' ";
$result_3 = mysql_query($sql_3);
$row_3 = mysql_fetch_array($result_3);
$MName = $row_3['MName'];
if(isset($_POST['submit'])) {
$file = $_FILES['file']['tmp_name'];
$handle = fopen($file,"r");
while (($objArr = fgetcsv($handle,1000, ",")) !== false)
{
echo $strSQL .="INSERT INTO `student` (`ID`, `Title`, `Firstname`, `Lastname`, `Degree`, `Faculty`, `Major`, `Honor`) VALUES (NULL, '$objArr[0]', '$objArr[1]', '$objArr[2]', '".$degree."', '".$FaName."', '".$MName."', '$objArr[3]')";
mysql_query("SET NAMES UTF8");
$objQuery = mysql_query($strSQL);
if($strSQL)
{
echo("<script> alert('Upload Successful !!!'); window.location='UploadCsv.php';</script>");
}
}
}
closedb();
?>
<body>
<p><?php echo $degree." (".$degree.")"; ?></p>
<p><?php echo $FaNumber." (".$FaName.")"; ?></p>
<p><?php echo $MNumber." (".$MName.")"; ?></p>
</body>
</html>
Tag PHP, MySQL
ประวัติการแก้ไข
Date :
2014-08-06 16:15:35By :
PawnniEzView :
1861Reply :
7
ถ้าค่าตัวอื่นเข้า DB ปกติ ก็ลองเช็คสคริปตรงที่หา Major ครับCode (PHP) $sql_3 = "SELECT * FROM major WHERE MNumber = '$MNumber' ";
$result_3 = mysql_query($sql_3);
$row_3 = mysql_fetch_array($result_3);
$MName = $row_3['MName'];
echo $sql_3."<br>";
print_r($row_3);
echo "<br>";
echo $MName ."<br>";
exit; #สั่งให้ทำงานแค่ตรงนี้ แล้วหยุดการทำงานส่วนสคริปล่างจากบรรทัดนี้
ประวัติการแก้ไข
Date :
2014-08-06 16:47:52By :
arm8957
ก็ไปหาที่มาของตัวแปรชื่อ $MNumber ครับ แล้วเช็คดูดีๆ ว่าพลาดตรงไหน?
Date :
2014-08-06 17:09:20By :
arm8957
เอาสคริปนี้ไว้นอก loop ครับCode if($strSQL)
{
echo("<script> alert('Upload Successful !!!'); window.location='UploadCsv.php';</script>");
}
Code (PHP) <!DOCTYPE html>
<html>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<?php
include "config.php";
conndb();
"degree=".$degree = $_POST['Degree'];
"<br/>";
"FaNumber=".$FaNumber = $_POST['faculty'];
"<br/>";
"MNumber=".$MNumber = $_POST['major'];
"<hr>";
$sql_1 = "SELECT * FROM degree WHERE id_degree = '$degree' ";
$result_1 = mysql_query($sql_1);
$row_1 = mysql_fetch_array($result_1);
$degree = $row_1['degree_name'];
$sql_2 = "SELECT * FROM faculty WHERE FaNumber = '$FaNumber' ";
$result_2 = mysql_query($sql_2);
$row_2 = mysql_fetch_array($result_2);
$FaName = $row_2['FaName'];
$sql_3 = "SELECT * FROM major WHERE MNumber = '$MNumber' ";
$result_3 = mysql_query($sql_3);
$row_3 = mysql_fetch_array($result_3);
$MName = $row_3['MName'];
if(isset($_POST['submit'])) {
$file = $_FILES['file']['tmp_name'];
$handle = fopen($file,"r");
while (($objArr = fgetcsv($handle,1000, ",")) !== false)
{
$strSQL .="INSERT INTO `student` (`ID`, `Title`, `Firstname`, `Lastname`, `Degree`, `Faculty`, `Major`, `Honor`) VALUES (NULL, '$objArr[0]', '$objArr[1]', '$objArr[2]', '".$degree."', '".$FaName."', '".$MName."', '$objArr[3]')";
mysql_query("SET NAMES UTF8");
$objQuery = mysql_query($strSQL);
}
echo("<script> alert('Upload Successful !!!'); window.location='UploadCsv.php';</script>");
}
closedb();
?>
<body>
<p><?php echo $degree." (".$degree.")"; ?></p>
<p><?php echo $FaNumber." (".$FaName.")"; ?></p>
<p><?php echo $MNumber." (".$MName.")"; ?></p>
</body>
</html>
Date :
2014-08-06 17:50:42By :
arm8957
Server 04
Load balance : Server 04
