|
 |
| |
จะสั่งให้ dropdown list เลือกเทอมการศึกษาตามวันที่เริ่มต้น-สิ้นสุดตามที่เรากำหนดได้อย่างไรครับ |
| |
 |
|
|
 |
 |
|
ถ้าผมต้องการที่จะสั่งให้ dropdown list มีเทอมให้เลือกอยู่ เทอม1 เทอม2 เทอม3
เทอม 1 ตั้งแต่2018-06-15 ถึง 2018-10-31
เทอม 2 ตั้งแต่2018-11-15 ถึง 2019-03-20
เทอม 3 ตั้งแต่2019-04-15 ถึง 2019-06-31
และอีกปัญหาคือจะทำอย่างไรให้มันรองรับการเปลี่ยน พศ เพราะปีการศึกษามันเพิ่มขึ้นเรื่อยๆ
file index.php
แล้วทีนี้ผมจะแก้ไขคำสั้ง sql ให้สามารถ query ข้อมูลตามวันที่เริ่มต้นเเละสิ้นสุดตามที่เราเลือกได้อย่างไรครับ ช่วยชี้แนะผมทีครับ
column ที่ใช้เก็บวันที่ใน mysql app_date
index.php
Code (PHP)
<?php
include('database_connection.php');
$app_year = '';
$category_id = '';
$query = "SELECT DISTINCT app_year, category_id FROM appointment ORDER BY appstudent_id ASC";
$statement = $connect->prepare($query);
$statement->execute();
$result = $statement->fetchAll();
foreach($result as $row)
{
$app_year .= '<option value="'.$row['app_year'].'">'.$row['app_year'].'</option>';
$category_id .= '<option value="'.$row['category_id'].'">'.$row['category_id'].'</option>';
}
?>
<html>
<head>
<title>Custom Search</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
<script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.12/js/dataTables.bootstrap.min.js"></script>
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.12/css/dataTables.bootstrap.min.css" />
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container box">
<h1 align="center">รายงาน</h1>
<br />
<br />
<div class="row">
<div class="input-daterange">
<div class="col-md-3">
<select name="filter_year" id="filter_year" class="form-control" required>
<option value="">เลือกปีการศึกษา</option>
<?php echo $app_year; ?>
</select>
</div>
<div class="col-md-3">
<select name="filter_semester" id="filter_semester" class="form-control" required>
<option value="">เลือกเทอมการศึกษา</option>
<option name="01" id="01" value="ทอม 1">เทอม 1 </option>
<option name="02" id="02" value="ทอม 2">เทอม 2 </option>
<option name="03" id="03" value="ทอม 3">เทอม 3 </option>
</select>
</div>
<div class="col-md-3">
<select name="filter_category" id="filter_category" class="form-control" required>
<option value="">เลือกประเภท</option>
<?php echo $category_id; ?>
</select>
</div>
<div class="col-md-3">
<button type="button" name="filter" id="filter" class="btn btn-info">ค้นหา</button>
</div>
</div>
</div>
<br/>
<div class="table-responsive">
<table id="customer_data" class="table table-bordered table-striped">
<thead>
<tr>
<th width="13%">รหัสนักศึกษา</th>
<th width="15%">ชื่อ</th>
<th width="8%">วันที่</th>
<th width="5%">เวลา</th>
<th width="10%">ประเภท</th>
<th width="9%">ปีการศึกษา</th>
<th width="25%">เรื่อง</th>
</tr>
</thead>
</table>
<br />
<br />
<br />
</div>
</div>
</body>
</html>
<script type="text/javascript" language="javascript" >
$(document).ready(function(){
fill_datatable();
function fill_datatable(filter_year = '', filter_semester = '', filter_category = '')
{
var dataTable = $('#customer_data').DataTable({
"processing" : true,
"serverSide" : true,
"order" : [],
"searching" : false,
"ajax" : {
url:"fetch.php",
type:"POST",
data:{
filter_year:filter_year, filter_semester:filter_semester, filter_category:filter_category
}
}
});
}
$('#filter').click(function(){
var filter_year = $('#filter_year').val();
var filter_semester = $('#filter_semester').val();
var filter_category = $('#filter_category').val();
if(filter_year != ''&& filter_semester !='' && filter_category != '')
{
$('#customer_data').DataTable().destroy();
fill_datatable(filter_year, filter_semester, filter_category);
}
else
{
alert('กรุณาใส่ข้อมูลให้ครบ');
$('#customer_data').DataTable().destroy();
fill_datatable();
}
});
});
</script>
fetch.php
Code (PHP)
<?php
$dfrom = date('2018-06-10');
$dto = date('2018-10-31');
include('database_connection.php');
$column = array('student_number', 'student_name', 'app_date', 'time_id', 'category_id', 'app_year', 'app_message');
$query = " SELECT * FROM appointment ";
if(isset($_POST['filter_year'], $_POST['filter_semester'], $_POST['filter_category']) && $_POST['filter_year'] != '' && $_POST['filter_semester'] != '' && $_POST['filter_category'] != '')
{
$query .= 'WHERE app_year = "'.$_POST['filter_year'].'" AND category_id = "'.$_POST['filter_category'].'" ';
/*AND filter_semester = "'.$_POST['filter_semester'].'" and app_date BETWEEN "2018-06-15" AND "2018-10-31" ';*/
}
if(isset($_POST['order']))
{
$query .= 'ORDER BY '.$column[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir'].' ';
}
else
{
$query .= 'ORDER BY appstudent_id DESC ';
}
$query1 = '';
if($_POST["length"] != -1)
{
$query1 = 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}
$statement = $connect->prepare($query);
$statement->execute();
$number_filter_row = $statement->rowCount();
$statement = $connect->prepare($query . $query1);
$statement->execute();
$result = $statement->fetchAll();
$data = array();
foreach($result as $row)
{
$sub_array = array();
$sub_array[] = $row['student_number'];
$sub_array[] = $row['student_name'];
$sub_array[] = $row['app_date'];
$sub_array[] = $row['time_id'];
$sub_array[] = $row['category_id'];
$sub_array[] = $row['app_year'];
$sub_array[] = $row['app_message'];
$data[] = $sub_array;
}
function count_all_data($connect)
{
$query = "SELECT * FROM appointment";
$statement = $connect->prepare($query);
$statement->execute();
return $statement->rowCount();
}
$output = array(
"draw" => intval($_POST["draw"]),
"recordsTotal" => count_all_data($connect),
"recordsFiltered" => $number_filter_row,
"data" => $data
);
echo json_encode($output);
?>
Tag : PHP, MySQL, Ajax
|
|
|
 |
 |
 |
 |
| Date :
2019-01-14 11:46:18 |
By :
sanchaimax |
View :
2088 |
Reply :
3 |
|
 |
 |
 |
 |
|
|
|
|
 |
 |
|
 |
 |
| |
|
|
|
| |
|
ออกแบบ database มันสัมพันธ์กันยังไงอ่ะ
|
| |
|
|
| |
| Date :
2019-01-14 12:23:32 |
By :
Pong Thep |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
 ตอบความคิดเห็นที่ : 1 เขียนโดย : Pong Thep เมื่อวันที่ 2019-01-14 12:23:32
รายละเอียดของการตอบ ::
นี่ครับพี่ Code (SQL)
-- phpMyAdmin SQL Dump
-- version 4.8.3
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Jan 14, 2019 at 10:29 AM
-- Server version: 10.1.37-MariaDB
-- PHP Version: 7.2.12
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Database: `calendardb`
--
-- --------------------------------------------------------
--
-- Table structure for table `appointment`
--
CREATE TABLE `appointment` (
`appstudent_id` int(11) NOT NULL,
`student_number` char(20) NOT NULL,
`student_name` varchar(50) NOT NULL,
`email` varchar(50) NOT NULL,
`app_date` date NOT NULL,
`time_id` char(10) NOT NULL,
`category_id` varchar(50) NOT NULL,
`app_year` char(10) NOT NULL,
`app_message` varchar(200) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
--
-- Dumping data for table `appointment`
--
INSERT INTO `appointment` (`appstudent_id`, `student_number`, `student_name`, `email`, `app_date`, `time_id`, `category_id`, `app_year`, `app_message`) VALUES
(12, '1111111111', 'à¸à¸à¸à¸à¸à¸à¸à¸à¸à¸', '[email protected]', '2018-06-10', '15.00', 'à¸à¸²à¸£à¹€à¸£à¸µà¸¢à¸™', '2018', 'à¸à¸à¸à¸à¸à¸à¸à¸à¸à¸à¸à¸à¸'),
(13, '22222222', 'ดดดดดดด', '[email protected]', '2018-07-10', '12.00', 'à¸à¸²à¸£à¹€à¸£à¸µà¸¢à¸™', '2018', 'ดดดดดดดดดดดดด'),
(14, '33333333333', 'à¸à¸à¸à¸à¸à¸à¸à¸à¸', '[email protected]', '2018-10-30', '14.30', 'à¸à¸²à¸£à¹€à¸£à¸µà¸¢à¸™', '2018', 'à¸à¸à¸à¸à¸à¸à¸à¸à¸à¸à¸à¸à¸à¸'),
(15, '33333333333', 'à¸à¸à¸à¸à¸à¸à¸à¸à¸', '[email protected]', '2018-10-30', '14.30', 'à¸à¸²à¸£à¹€à¸£à¸µà¸¢à¸™', '2018', 'à¸à¸à¸à¸à¸à¸à¸à¸à¸à¸à¸à¸à¸à¸');
-- --------------------------------------------------------
--
-- Table structure for table `category_recommend`
--
CREATE TABLE `category_recommend` (
`category_id` int(11) NOT NULL,
`category_name` varchar(50) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
--
-- Dumping data for table `category_recommend`
--
INSERT INTO `category_recommend` (`category_id`, `category_name`) VALUES
(2001, 'การเรียน'),
(2002, 'ทุนการศึกษา'),
(2003, 'ปัญหาชีวิต'),
(2004, 'ปัญหาสุขภาพ');
-- --------------------------------------------------------
--
-- Table structure for table `time_student`
--
CREATE TABLE `time_student` (
`time_id` int(11) NOT NULL,
`time_name` varchar(50) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
--
-- Dumping data for table `time_student`
--
INSERT INTO `time_student` (`time_id`, `time_name`) VALUES
(1018, '17.00'),
(1017, '16.30'),
(1016, '16.00'),
(1015, '15.30'),
(1014, '15.00'),
(1013, '14.30'),
(1012, '14.00'),
(1011, '13.30'),
(1010, '13.00'),
(1009, '12.30'),
(1008, '12.00'),
(1007, '11.30'),
(1006, '11.00'),
(1005, '10.30'),
(1004, '10.00'),
(1003, '09.30'),
(1002, '09.00'),
(1001, '08.30');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `appointment`
--
ALTER TABLE `appointment`
ADD PRIMARY KEY (`appstudent_id`);
--
-- Indexes for table `category_recommend`
--
ALTER TABLE `category_recommend`
ADD PRIMARY KEY (`category_id`);
--
-- Indexes for table `time_student`
--
ALTER TABLE `time_student`
ADD PRIMARY KEY (`time_id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `appointment`
--
ALTER TABLE `appointment`
MODIFY `appstudent_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=16;
COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
|
| |
|
|
| |
| Date :
2019-01-14 16:29:38 |
By :
max |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
ผมยังไม่ได้ทำให้มันเก็บเป็นภาษาไทยได้ตอนนี้เเค่เทสใส่ข้อมูลดูก่อนว่าบันทึกได้รึเปล่าครับ
|
| |
|
|
| |
| Date :
2019-01-14 16:33:02 |
By :
max |
|
|
| |
|
|
|
|
|
| |
|
|
|
|
|
| |
|
 Load balance : Server 04
|
