|
 |
| |
สอบถามเกี่ยวกับการ Insert จังหวัด อำเภอ ตำบล ใน mysql หน่อยครับ |
| |
 |
|
|
 |
 |
|
คือผมทำการ Insert แต่กลับ insert ได้ค่า ID จะทำให้มันเป็น name ยังไงครับ มือใหม่ ขอบคุณครับ
Code (PHP)
<?php
session_start();
if (!$_SESSION['userid']){
header("Location: index.php");
} else {
$con= mysqli_connect("localhost","root","","databaseanysupply") or die("Error: " . mysqli_error($con));
mysqli_query($con, "SET NAMES 'utf8' ");
error_reporting( error_reporting() & ~E_NOTICE );
date_default_timezone_set('Asia/Bangkok');
?>
<!DOCTYPE html>
<html lang="en" dir="ltr">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<link rel="stylesheet" href="styleAdminMain.css">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css">
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.5.1/jquery.min.js" charset="utf-8"></script>
<script src="https://kit.fontawesome.com/a076d05399.js"></script>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/bs4/dt-1.10.21/datatables.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<title>Site Page</title>
</head>
<body>
<?php
$sql_provinces = "SELECT * FROM provinces";
$query = mysqli_query($con, $sql_provinces);
?>
<!-- Popper JS -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>
<!-- Latest compiled JavaScript -->
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.0/js/bootstrap.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/v/bs4/dt-1.10.21/datatables.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/sweetalert2@9"></script>
<script type="text/javascript">
$(document).ready(function(){
showAllUsers();
function showAllUsers(){
$.ajax({
url: "actionsite.php",
type: "POST",
data: {action:"view"},
success:function(response){
//console.log(response);
$("#showUser").html(response);
$("table").DataTable({
order: [0,'desc']
});
}
});
}
//Insert Work
$("#insert").click(function(e){
if($("#form-data")[0].checkValidity()){
e.preventDefault();
$.ajax({
url: "actionsite.php",
type: "POST",
data: $("#form-data").serialize()+"&action=insert",
success:function(response){
Swal.fire({
title: 'Add Successfully!',
icon: 'success'
})
$("#addModal").modal('hide');
$("#form-data")[0].reset();
showAllUsers();
}
});
}
});
//Edit Assign
$("body").on("click",".editBtn", function(e){
e.preventDefault();
edit_id = $(this).attr('id');
$.ajax({
url:"actionsite.php",
type: "POST",
data: {edit_id:edit_id},
success:function(response){
datasite = JSON.parse(response);
$("#Siteid").val(datasite.Siteid);
$("#sname").val(datasite.Sitename);
$("#slocation").val(datasite.Sitelocation);
}
});
});
//Update
$("#update").click(function(e){
if($("#edit-form-data")[0].checkValidity()){
e.preventDefault();
$.ajax({
url: "actionsite.php",
type: "POST",
data: $("#edit-form-data").serialize()+"&action=update",
success:function(response){
Swal.fire({
title: 'Update Successfully!',
icon: 'success'
})
$("#editModal").modal('hide');
$("#edit-form-data")[0].reset();
showAllUsers();
}
});
}
});
//Delete
$("body").on("click",".delBtn", function(e){
e.preventDefault();
var tr = $(this).closest('tr');
del_id = $(this).attr('id');
Swal.fire({
title: 'Are you sure?',
text: "You won't be able to revert this!",
icon: 'warning',
showCancelButton: true,
confirmButtonColor: '#3085d6',
cancelButtonColor: '#d33',
confirmButtonText: 'Yes, delete it!'
}).then((result) => {
if (result.value) {
$.ajax({
url: "actionsite.php",
type:"POST",
data:{del_id: del_id},
success:function(response){
tr.css('background-color','#ff6666');
Swal.fire(
'Deleted!',
'Deleted Successfully',
'success'
)
showAllUsers();
}
});
}
});
});
//showUser Details
$("body").on("click",".infoBtn", function(e){
e.preventDefault();
info_id = $(this).attr('id');
$.ajax({
url:"actionsite.php",
type:"POST",
data:{info_id:info_id},
success:function(response){
//console.log(response);
datasite = JSON.parse(response);
Swal.fire({
title:'<strong>Site Info: ID('+datasite.Siteid+')</strong>',
icon: 'info',
html: '<b>ชื่อสถานที่ :</b>'+datasite.Sitename+'<br><b>ที่อยู่สถานที่ :</b>'+datasite.Sitelocation,
showCancelButton: true,
})
}
});
});
});
</script>
<div class="wrapper">
<div class="header">
<div class="header-menu">
<div class="sidebar-btn">
<i class="fas fa-bars"></i>
</div>
<div class="title">Any <span>Supply</span></div>
<ul>
<li><a href="userprofile_page.php"><i class="fas fa-user"></i></a></li>
<li><a href="logout.php"><i class="fas fa-power-off"></i></a></li>
</ul>
</div>
</div>
<div class="sidebar">
<div class="sidebar-menu">
<center class="profile">
<img src="img/userpiclogo.jpg" class="profile_image" alt="">
<p><?php echo $_SESSION['user']." ".$_SESSION['lastname']; ?></p>
</center>
<li class="item" id="member">
<a href="#member" class="menu-btn">
<i class="fas fa-user-circle"></i><span>Member<i class="fas fa-chevron-down drop-down">
</i></span>
</a>
<div class="sub-menu">
<a href="registermember.php"><i class="fas fa-folder-plus"></i><span>ผูัใช้งาน</span></a>
<a href="site.php"><i class="fas fa-folder-plus"></i><span>Site</span></a>
</div>
</li>
<li class="item">
<a href="location.php" class="menu-btn">
<i class="fa fa-map-marker"></i><span>FixMap</span>
</a>
</li>
<li class="item" id="messages">
<a href="#messages" class="menu-btn">
<i class="fas fa-comment-alt"></i><span>Inquire<i class="fas fa-chevron-down drop-down">
</i></span>
</a>
<div class="sub-menu">
<a href="JobOrder_Admin.php"><i class="fas fa-folder-plus"></i><span>JobOrder</span></a>
<a href="#"><i class="fas fa-folder-plus"></i><span>JobOrderIS</span></a>
<a href="#"><i class="fas fa-folder-plus"></i><span>JobOrderPM</span></a>
</div>
</li>
<li class="item" id="master">
<a href="#master" class="menu-btn">
<i class="fas fa-database"></i><span>Master Data<i class="fas fa-chevron-down drop-down">
</i></span>
</a>
<div class="sub-menu">
<a href="#"><i class="fas fa-globe-asia"></i><span>Sites</span></a>
<a href="#"><i class="fab fa-product-hunt"></i><span>Product Site</span></a>
<a href="#"><i class="fas fa-user-secret"></i><span>Customer</span></a>
<a href="#"><i class="far fa-address-book"></i><span>Contracts</span></a>
<a href="#"><i class="fas fa-file-signature"></i><span>ตรวจสอบสัญญาบริการ</span></a>
</div>
</li>
</div>
</div>
<div class="main-container">
<div class="tableJobOrder">
<div class="row">
<div class="col-lg-12">
<h4 class="text-center text-danger font-weight-normal my-3">Site</h4>
</div>
</div>
<div class="row">
<div class="col-lg-6">
<h4 class="mt-2 text-primary">All Site in database!</h4>
</div>
<div class="col-lg-6">
<button type="button" class="btn btn-primary m-1 float-right" data-toggle="modal" data-target="#addModal"><i class="fas fa-map-marker fa-lg"> </i>Add Site</button>
<a href="actionsite.php?export=excel" class="btn btn-success m-1 float-right"><i class="fas fa-table fa-lg"></i> Export to Excel</a>
</div>
</div>
<hr class="my-1">
<div class="row">
<div class="col-lg-12">
<div class="table-responsive" id="showUser">
<h3 class="text-center text-success" style="margin-top:150">Loading...</h3>
</div>
</div>
</div>
</div>
<!-- Add New -->
<div class="modal fade" id="addModal">
<div class="modal-dialog modal-dialog-centered">
<div class="modal-content">
<!-- Modal Header -->
<div class="modal-header">
<h4 class="modal-title">SiteJob</h4>
<button type="button" class="close" data-dismiss="modal">×</button>
</div>
<!-- Modal body -->
<div class="modal-body px-4">
<form action="" method="post" id="form-data">
<div class="form-group">
<input type="text" name="sname" class="form-control" placeholder="Site Name" require>
</div>
<div class="form-group">
<input type="text" name="slocation" class="form-control" placeholder="Site Location" require>
</div>
<div class="form-group">
<label for="sel1">จังหวัด:</label>
<select class="form-control" name="provinces" id="provinces">
<option value="" selected disabled>-กรุณาเลือกจังหวัด-</option>
<?php foreach ($query as $value) { ?>
<option value="<?=$value['id']?>"><?=$value['name_th']?></option>
<?php } ?>
</select>
<br>
<label for="sel1">อำเภอ:</label>
<select class="form-control" name="amphures" id="amphures">
</select>
<br>
<label for="sel1">ตำบล:</label>
<select class="form-control" name="districts" id="districts">
</select>
<br>
<label for="sel1">รหัสไปรษณีย์:</label>
<input type="text" name="zip_code" id="zip_code" class="form-control">
<br>
</div>
<div class="form-group">
<input type="submit" name="insert" id="insert" value="เพิ่ม" class="btn btn-success btn-block">
</div>
</form>
</div>
</div>
</div>
</div>
<!-- Edit New -->
<div class="modal fade" id="editModal">
<div class="modal-dialog modal-dialog-centered">
<div class="modal-content">
<!-- Modal Header -->
<div class="modal-header">
<h4 class="modal-title">Edit SiteJob</h4>
<button type="button" class="close" data-dismiss="modal">×</button>
</div>
<!-- Modal body -->
<div class="modal-body px-4">
<form action="" method="post" id="edit-form-data">
<input type="hidden" name="Siteid" id="Siteid">
<div class="form-group">
<input type="text" name="sname" class="form-control" id="sname" require>
</div>
<div class="form-group">
<input type="text" name="slocation" class="form-control" id="slocation" require>
</div>
<div class="form-group">
<input type="submit" name="update" id="update" value="UPDATE" class="btn btn-primary btn-block">
</div>
</form>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</body>
</html>
<?php include('script.php');?>
<?php } ?>
หน้า Action
Code (PHP)
<?php
require_once 'database_joborder.php';
$dbsite = new Database();
if(isset($_POST['action']) && $_POST['action'] == "view"){
$output = '';
$datasite = $dbsite->readsite();
if($dbsite->totalRowCount()>0){
$output .= '<table class="table table-striped table-sm table-bordered">
<thead>
<tr class="text-center">
<th>No.Site</th>
<th>Site Name</th>
<th>Site Location</th>
<th>Action</th>
</tr>
</thead>
<tbody>';
foreach ($datasite as $row) {
$output .= '<tr class="text-center text-secondary">
<td>'.$row['Siteid'].'</td>
<td>'.$row['Sitename'].'</td>
<td>'.$row['Sitelocation'].'</td>
<td>
<a href="#" Title="View Details" class="text-success infoBtn" id="'.$row['Siteid'].'"><i class="fas fa-info-circle fa-lg"></i></a>
<a href="#" Title="Edit" class="text-primary editBtn" data-toggle="modal"
data-target="#editModal" id="'.$row['Siteid'].'"><i class="fas fa-edit fa-lg"></i></a>
<a href="#" Title="Delete" class="text-danger delBtn" id="'.$row['Siteid'].'"><i class="fas fa-trash-alt fa-lg"></i></a>
</td></tr>';
}
$output .= '</tbody></table>';
echo $output;
}
else{
echo '<h3 class="text-center text-secondary mt-5">No Data in the database! Please Add</h3>';
}
}
if(isset($_POST['action']) && $_POST['action'] == "insert"){
$sname = $_POST['sname'];
$slocation = $_POST['slocation'];
$provinces = $_POST['provinces'];
$amphures = $_POST['amphures'];
$districts = $_POST['districts'];
$zip_code = $_POST['zip_code'];
$dbsite->insertsite($sname,$slocation,$provinces,$amphures,$districts,$zip_code);
}
if(isset($_POST['edit_id'])){
$Siteid = $_POST['edit_id'];
$row = $dbsite->getUserByIdsite($Siteid);
echo json_encode($row);
}
if(isset($_POST['action']) && $_POST['action'] == "update"){
$Siteid = $_POST['Siteid'];
$sname = $_POST['sname'];
$slocation = $_POST['slocation'];
$dbsite->updatesite($Siteid,$sname,$slocation);
}
if(isset($_POST['del_id'])){
$Siteid = $_POST['del_id'];
$dbsite->deletesite($Siteid);
}
if(isset($_POST['info_id'])){
$Siteid = $_POST['info_id'];
$row = $dbsite->getUserByIdsite($Siteid);
echo json_encode($row);
}
if(isset($_GET['export'])&& $_GET['export'] == "excel"){
header("Content-Type: application/xls");
header("Content-Disposition: attachment; filename=Site.xls");
header("Pragma: no-cache");
header("Expires: 0");
$datasite = $dbsite->readsite();
echo'<table border=1';
echo'<tr><th>Siteid</th><th>SiteName</th><th>SiteLocation</th></tr>';
foreach ($datasite as $row){
echo '<tr>
<td>'.$row['Siteid'].'</td>
<td>'.$row['Sitename'].'</td>
<td>'.$row['Sitelocation'].'</td>
</tr>';
}
echo '</table>';
}
หน้า Database
Code (PHP)
public function insertsite($sname,$slocation,$provinces,$amphures,$districts,$zip_code){
$sql = "INSERT INTO sitejob (Sitename,Sitelocation,Siteprovinces,Siteamphures,Sitedistricts,Sitezipcode) VALUES (:sitename,:sitelocation,
:siteprovinces,:siteamphures,:sitedistricts,:sitezipcode)";
$stmt = $this->conn->prepare($sql);
$stmt->execute(['sitename'=>$sname,'sitelocation'=>$slocation,'siteprovinces'=>$provinces,'siteamphures'=>$amphures,'sitedistricts'=>$districts,'sitezipcode'=>$zip_code]);
return true;
}
public function readsite() {
$data = array();
$sql = "SELECT * FROM sitejob";
$stmt = $this->conn->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($result as $row){
$datasite[] = $row;
}
return $datasite;
}
public function getUserByIdsite($Siteid){
$sql = "SELECT * FROM sitejob WHERE Siteid = :Siteid";
$stmt = $this->conn->prepare($sql);
$stmt->execute(['Siteid'=>$Siteid]);
$result = $stmt->fetch(PDO::FETCH_ASSOC);
return $result;
}
public function updatesite($Siteid,$sname,$slocation){
$sql = "UPDATE sitejob SET Sitename = :sitename, Sitelocation = :sitelocation WHERE Siteid = :Siteid";
$stmt = $this->conn->prepare($sql);
$stmt->execute(['sitename'=>$sname,'sitelocation'=>$slocation,'Siteid' =>$Siteid]);
return true;
}
public function deletesite($Siteid){
$sql = "DELETE FROM sitejob WHERE Siteid = :Siteid";
$stmt = $this->conn->prepare($sql);
$stmt->execute(['Siteid'=>$Siteid]);
return true;
}
public function totalRowCountsite(){
$sql = "SELECT * FROM sitejob";
$stmt = $this->conn->prepare($sql);
$stmt->execute();
$t_rows = $stmt->rowCount();
return $t_rows;
}
//MEMBER
public function insertmember($uname,$pword,$fname,$lname,$phone,$email,$position,$userlevel){
$sql = "INSERT INTO user (Username,Password,Firstname,Lastname,Phone,Email,Position,Userlevel) VALUES (:username,:password,:firstname,:lastname,:phone,:email,:position,:userlevel)";
$stmt = $this->conn->prepare($sql);
$stmt->execute(['username'=>$uname,'password'=>$pword,'firstname'=>$fname,'lastname'=>$lname,'phone'=>$phone,'email'=>$email,'position'=>$position,'userlevel'=>$userlevel]);
return true;
}
public function readmember() {
$data = array();
$sql = "SELECT * FROM user";
$stmt = $this->conn->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($result as $row){
$datamember[] = $row;
}
return $datamember;
}
public function getUserByIdmember($id){
$sql = "SELECT * FROM user WHERE id = :id";
$stmt = $this->conn->prepare($sql);
$stmt->execute(['id'=>$id]);
$result = $stmt->fetch(PDO::FETCH_ASSOC);
return $result;
}
public function updatemember($id,$uname,$pword,$fname,$lname,$phone,$email,$position,$userlevel){
$sql = "UPDATE user SET Username = :username, Password = :password, Firstname = :firstname, Lastname = :lastname, Phone = :phone, Email = :email, Position = :position, Userlevel = :userlevel WHERE id = :id";
$stmt = $this->conn->prepare($sql);
$stmt->execute(['username' => $uname,'password' => $pword,'firstname' => $fname,'lastname' => $lname,'phone' => $phone,'email' => $email,'position' => $position,'userlevel' => $userlevel,'id' => $id]);
return true;
}
public function deletemember($id){
$sql = "DELETE FROM user WHERE id = :id";
$stmt = $this->conn->prepare($sql);
$stmt->execute(['id'=>$id]);
return true;
}
public function totalRowCountmember(){
$sql = "SELECT * FROM user";
$stmt = $this->conn->prepare($sql);
$stmt->execute();
$t_rows = $stmt->rowCount();
return $t_rows;
}
}
ajax
Code (PHP)
<?php
$con= mysqli_connect("localhost","root","","databaseanysupply") or die("Error: " . mysqli_error($con));
mysqli_query($con, "SET NAMES 'utf8' ");
error_reporting( error_reporting() & ~E_NOTICE );
date_default_timezone_set('Asia/Bangkok');
if (isset($_POST['function']) && $_POST['function'] == 'provinces') {
$id = $_POST['id'];
$sql = "SELECT * FROM amphures WHERE province_id='$id'";
$query = mysqli_query($con, $sql);
echo '<option value="" selected disabled>-กรุณาเลือกอำเภอ-</option>';
foreach ($query as $value) {
echo '<option value="'.$value['id'].'">'.$value['name_th'].'</option>';
}
}
if (isset($_POST['function']) && $_POST['function'] == 'amphures') {
$id = $_POST['id'];
$sql = "SELECT * FROM districts WHERE amphure_id='$id'";
$query = mysqli_query($con, $sql);
echo '<option value="" selected disabled>-กรุณาเลือกตำบล-</option>';
foreach ($query as $value2) {
echo '<option value="'.$value2['id'].'">'.$value2['name_th'].'</option>';
}
}
if (isset($_POST['function']) && $_POST['function'] == 'districts') {
$id = $_POST['id'];
$sql = "SELECT * FROM districts WHERE id='$id'";
$query3 = mysqli_query($con, $sql);
$result = mysqli_fetch_assoc($query3);
echo $result['zip_code'];
exit();
}
?>
Script
Code (JavaScript)
<script type="text/javascript">
$('#provinces').change(function() {
var id_province = $(this).val();
$.ajax({
type: "POST",
url: "ajax_db.php",
data: {id:id_province,function:'provinces'},
success: function(data){
$('#amphures').html(data);
$('#districts').html(' ');
$('#districts').val(' ');
$('#zip_code').val(' ');
}
});
});
$('#amphures').change(function() {
var id_amphures = $(this).val();
$.ajax({
type: "POST",
url: "ajax_db.php",
data: {id:id_amphures,function:'amphures'},
success: function(data){
$('#districts').html(data);
$('#zip_code').val(' ');
}
});
});
$('#districts').change(function() {
var id_districts= $(this).val();
$.ajax({
type: "POST",
url: "ajax_db.php",
data: {id:id_districts,function:'districts'},
success: function(data){
$('#zip_code').val(data)
}
});
});
</script>
หลังInsert
Tag : PHP, MySQL, JavaScript, Ajax, XAMPP
|
ประวัติการแก้ไข
2020-08-03 09:59:56
2020-08-03 11:24:29
2020-08-03 11:31:53
|
 |
 |
 |
 |
| Date :
2020-08-03 09:48:48 |
By :
ronagon1 |
View :
1845 |
Reply :
6 |
|
 |
 |
 |
 |
|
|
|
|
 |
 |
|
 |
 |
| |
|
|
|
| |
|
จาก ajax_db.php แสดงว่าคุณทำตารางแยก amphures, districts (แต่ไม่มีตาราง provinces)
แต่นี่ไม่ใช่ปัญหาหลัก...ที่สำคัญคือในหน้า actionsite.php ที่เป็นส่วน insert (line 44-53)
คุณส่งค่าจาก $_POST โดยตรง ซึ่งเป็น id ของ amphures, districts, provinces
ดังนั้นแก้ไขตรงจุดนี้โดย query เพิ่อดึงชื่อจากตาราง amphures, districts โดยใส่ WHERE ตาม $_POST ของ id เช่น
Code (SQL)
-- สำหรับจังหวัด
SELECT province_name FROM provinces WHERE province_id = $_POST['provinces']
(ดูหน้า ajax_db.php เป็นตัวอย่าง)
หลังจาก query ได้ชื่อมาแล้ว ค่อย assign value
Code (PHP)
$provinces = $value['name_th']; // ตัวอย่างชื่อจังหวัด
// ทำตัวแปรอื่นในส่วน insert ให้ครบ
สุดท้ายก็ส่งชื่อเป็น parameters ไปกับเมธอด insertsite()
แค่นี้คำสั่ง execute of statement ในหน้า database_joborder.php ก็จะ insert เป็นชื่อแทน id
การกำหนดชื่อตัวแปรหรือชื่อตารางหรือชื่ออย่างอื่น ระวังพยายามหลีกเลี่ยงคำที่มีความหมายเหมือนกัน ที่พบคือ
amphures, districts สองคำนี้คือสิ่งเดียวกัน ถ้าต้องการสื่อถึงตำบลก็ใส่ทับศัพท์ไปเลย เช่น Tambon หรือ Sub-districts
|
ประวัติการแก้ไข
2020-08-03 12:22:19
| |
|
|
| |
| Date :
2020-08-03 12:13:52 |
By :
PhrayaDev |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
ขอบคุณครับ คือผมต้องสร้าง
SELECT province_name FROM provinces WHERE province_id = $_POST['provinces']
มาไว้ใน actionsite หรอครับ
งงครับ
|
| |
|
|
| |
| Date :
2020-08-03 13:11:15 |
By :
ronagon1 |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
ผมก็เริ่มงง เหมือนใช้ยาผิดแขนง เอ้าเพื่อ confirm
ลอง echo $_POST['provinces']; ได้ id หรือ name
แล้วรายงานด่วน...เพราะงานกำลังเข้าแล้ว 
|
| |
|
|
| |
| Date :
2020-08-03 13:28:56 |
By :
PhrayaDev |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
echo ออกมาเป็น ID ครับ T_T
|
| |
|
|
| |
| Date :
2020-08-03 13:47:52 |
By :
ronagon1 |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
ขอบคุณครับ กำลังค่อยๆพยายามทำความเข้าใจครับ
|
| |
|
|
| |
| Date :
2020-08-03 14:28:36 |
By :
ronagon1 |
|
|
| |
|
|
|
|
|
| |
|
|
|
|
|
| |
|
 Load balance : Server 04
|
