conditional date overlaping

I have one table and I'm trying to implement overlapping time for a teaching and learning activity. I have created a table named order_learn the contents of the field is ID (int, auto increment), id_user (varchar), material_id (varchar), start_periode (date), end_periode (date), generation (varchar), totalday (varchar), remarks (text).

a student can take any subject whose learning hours are determined by the start_period (date) and end_periode (date), generation (varchar).

which students are not allowed to take on subjects that coincide with other lessons (overlap on lesson dates).

Table
 study_request

id,
 id_user,
 material_id,
 start_periode,
 end_periode,
 generation,
 totalday

Value :
1     2       1             03/01/2017          03/13/2017      I          10  (return true for the first record)
2     2       2             03/01/2017          03/13/2017      I          10  ( return true if record existing)
3    2        2             03/15/2017          03/21/2017      II          10   (return true becouse the new record not overlap with other record by date ).......etc
4     2       5             03/08/2017          03/17/2017      III         10  (return false because the record is date overlap with other record)        

from the above data shows that id_user takes 4 subjects with the same time that should not be allowed by the system later unless the schedule of the lesson is in the second generation with the implementation time above 21/13/2017.

Question:
1. how do I make a condition if a student wants to take 3 subjects whose time has certainly not overlapped based on a predetermined schedule (condition)?
2. should this overlap be made on the Javascript part of the on load event or Before Record Add?
3. how to script it?

so my question, for your help I thank you.
adigugun2000Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
1) what schedule?
2) in the database, in the PHP code and in JavaScript.
3) by reviewing your data model..

order_learn the contents of the field is ID (int, auto increment), id_user (varchar), material_id (varchar), start_periode (date), end_periode (date), generation (varchar), totalday (varchar), remarks (text).
Bad, inconsistent naming convention. There's a norm for it..

ISO/IEC 11179-5:2005 - Information technology -- Metadata registries (MDR) -- Part 5: Naming and identification principles

Why should be record 2 true? It overlaps with record 1.
Why should be record 3 true? It overlaps with record 4.
0
Julian HansenCommented:
1. Each new record coming in needs to check that the start date and end date of the target session does not lie between the start and end dates of any record AND check that the range does not contain any other record
 

SELECT 
  * 
FROM 
  order_learn
WHERE 
  id_user = 2 AND (
     (new_record_start_date BETWEEN start_periode AND end_periode OR 
      new_record_end_date BETWEEN start_periode AND end_periode) OR 
    (new_record_start_date < start_periode AND 
     new_record_end_date > end_periode)
)

Open in new window

If that returns any rows you have a clash.

2. I would create the interface so that you cannot select ranges that are not valid. Request a list of unique ranges from the server and blank those out of your Date Picker or whatever control you are using
On the server side when you submit the data - you will need to check it again - you cannot trust anything from the client.

3. This is a bit broad - it depends on your frontend and backend code.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
adigugun2000Author Commented:
ok i say many thanks to
 ste5an and Julian Hansen, good luck for you and the relatives who have been willing to help me in solving this overlaping problem,
I will try to give a little of what I do because I just got this problem and indeed I'm not too expert about programming languages:

I tried with ste5an first,

1) what schedule?
indeed I have a table that contains a number of subject schedules like this:
CREATE TABLE IF NOT EXISTS `schedule` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_material` varchar(255) DEFAULT NULL,
  `material_name` varchar(255) DEFAULT NULL,
  `start_periode` date DEFAULT NULL,
  `end_periode` date DEFAULT NULL,
  `totalday` varchar(255) DEFAULT NULL,
  `generation` varchar(255) DEFAULT NULL,
  `quota` varchar(255) DEFAULT NULL,
  `stat` varchar(255) DEFAULT NULL,
  `remarks` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=34 ;

--
-- Dumping data for table `jadwal_mapel`
--

INSERT INTO `jadwal_mapel` (`id`, `id_material`, `material_name`, `start_periode`, `end_periode`, `totalday`, `generation`, `quota`, `stat`, `remarks`) VALUES
(16, '1', 'Matematic', '2018-03-01', '2018-03-10', '10', 'I', '10', '1', ''),
(17, '1', 'Matematic', '2018-03-08', '2018-03-17', '10', 'II', '10', '1', ''),
(18, '1', 'Matematic', '2018-03-15', '2018-03-24', '10', 'III', '10', '1', ''),
(19, '1', 'Matematic', '2018-03-22', '2018-03-31', '10', 'VI', '10', '1', ''),
(20, '2', 'physics', '2018-03-01', '2018-03-04', '4', 'I', '10', '1', ''),
(21, '2', 'physics', '2018-03-08', '2018-03-11', '4', 'II', '1o', '1', ''),
(22, '2', 'physics', '2018-03-15', '2018-03-18', '4', 'III', '10', '1', ''),
(23, '2', 'physics', '2018-03-22', '2018-03-25', '4', 'IV', '10', '1', ''),
(24, '3', 'chemistry', '2018-03-01', '2018-03-03', '3', 'I', '10', '1', ''),
(25, '3', 'chemistry', '2018-03-08', '2018-03-10', '3', 'II', '10', '1', ''),
(26, '3', 'chemistry', '2018-03-15', '2018-03-17', '3', 'III', '10', '1', ''),
(27, '3', 'chemistry', '2018-03-22', '2018-03-25', '4', 'IV', '10', '1', ''),
(28, '5', 'Computer', '2018-03-01', '2018-03-04', '4', 'I', '10', '1', ''),
(29, '5', 'Computer', '2018-03-08', '2018-03-11', '4', 'II', '10', '1', ''),
(30, '5', 'Computer', '2018-03-15', '2018-03-18', '4', 'III', '10', '1', ''),
(31, '5', 'Computer', '2018-03-22', '2018-03-25', '4', 'IV', '10', '1', ''),
(32, '20', 'Matematic II', '2018-04-01', '2018-04-10', '10', 'I', '10', '1', ''),
(33, '21', 'physics II', '2018-04-08', '2018-04-11', '4', 'II', '10', '1', '');



This list of subjects is a list table viewed by the user and will be selected directly by the user to be entered into their order via metote lookup table by id, so before the data in input (Before Record Add), this system detects whether these subjects have taken or not, if it is then the system will issue a message "Data can not be input again because you already have this subject with the same period with the same force" .... and if not, the system will check and create messages to the user with conditions, if it is in the selected date range then the system will refuse (return false) or not be added to the list of subject orders, for the record, it is impossible for a student to learn at once two subjects in the same time, surely should take precedence one of them.

that's about what I expected
 



2) in the database, in the PHP code and in JavaScript.
Roughly your suggestion which one is better, using php formats or or from the database (mySQL) or javascrip on load event ..., may have more experience on this matter.


3) by reviewing your model data ..
Maybe you have a suggestion to me, how to model the data I should apply

Bad, inconsistent naming convention. There's a norm for it ..
OK, forgive if I may be mistaken, maybe you have a better way than I do ..., thank you very much for suggestion.

 Why should be record 2 true? It overlaps with record 1.,
sorry this is wrong data when i try and overlap condition does not work at all.

Why should be record 3 true? It overlaps with record 4.
The sequence number three data is correct because it does not collide with the 1st order whose date starts from 01/15/2017 until the date of 21/03/2017, while the sequence number 4 data should not be added into the order data because although this subject force is III but the implementation period is in the range dated 03/08/2017 to 03/17/2017, of course this overlaps with the implementation of subjects number one, is not it? and not allowed in that day to carry out 2 subjects, just one subject per day.
Hopefully this can give a little explanation of what I mean.


untuk teman baik Julian Hansen,


to good friends Julian Hansen,


I will try the query you give and report what the result will be ...
please correct what is wrong with the script I am making, because the result is still the record added to the table order.

this is the script :

id,
 id_user,
 material_id,
 start_periode,
 end_periode,
 generation,
 totalday

$strSQLExists="select * from study_request where id_user='".$values['id_user']."' and material_id='".$values['material_idl']."' and generation='".$values['generation']."' and ((date(start_periode) <= '".$values['start_periode]."'  and '".$values['start_periode']."'  <= date(end_periode)) or (date(start_periode) <= '".$values['end_periode']."'  and '".$values['end_periode']."' <= date(end_periode)))";

$rsExists = db_query($strSQLExists,$conn);
$data=db_fetch_array($rsExists);
if($data)
{
$message = "Already Exist";
return false;
} else {
 return true;
}


490/5000
The above screens only issue a message if a student takes the same data (duplicate), but if the start_ period and end_periode are the same as the different batches remain unplugged which should still not be included in the reconstruction table study_request

I do not know where it's wrong ...

Please enlighten ...

Thank you very much for your time and help, this will add my future in the future.

Once again good luck for and all ..: D
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

adigugun2000Author Commented:
I am sorry table

order_learn is study_request,  
jadwal_mapel is  schedule (other language)

I am a little confused...
0
adigugun2000Author Commented:
Thank You....
0
Julian HansenCommented:
You are welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
JavaScript

From novice to tech pro — start learning today.