Delete records that not between , insert new dates and skip exiting results PHP mysql not working

PHP
1

Lets say i had period of 2017-06-01 to 2017-06-08

that is 6 working dates:
2017-06-01
2017-06-02
2017-06-05
2017-06-06
2017-06-07
2017-06-08

I m trying to make form to update lets say from 2017-06-02 to 2017-06-12

that is 7 working days:
2017-06-02
2017-06-05
2017-06-06
2017-06-07
2017-06-08
2017-06-09
2017-06-12

my problem:
when i try to update what i want is to delete from database dates that is not in new period ,
do nothing with exiting dates and insert new dates.

I tried so many things. … but still failing!

I have this code now:

$date_from=htmlentities($_POST['date_from'], ENT_QUOTES, "UTF-8");
$date_to=htmlentities($_POST['date_to'], ENT_QUOTES, "UTF-8");
        // DELETE works
	mysqli_query($conn, "DELETE FROM mytable WHERE date NOT BETWEEN '".$date_from."' AND '".$date_to."' AND oid='".$id."'");

function isWeekend($date) {
	return (date('N', strtotime($date)) <= 5);
}		

$begin = new DateTime( $date_from );
$end = new DateTime( $date_to );
$end = $end->modify( '+1 day' ); 

$interval = new DateInterval('P1D');
$daterange = new DatePeriod($begin, $interval ,$end);

foreach($daterange as $date){
	$ddate = $date->format("Y-m-d");
	
	$querys = mysqli_query($db, "SELECT * FROM mytable WHERE date='".$ddate."' AND oid='".$id."'");
	
	if(mysqli_num_rows($querys)>0){
		//do nothing
	}else{
		//insert
	}
	
	
	}

what do i do wrong?

2

Can you please tell us what your code does do right now?
Do you get an error?

3

Yes, on the face of it there’s nothing really wrong there - you delete some records, then create a range of dates, iterate through them and check to see if there are any records already in existence. If there are not, presumably you call your isWeekend() function on each date, and insert the new record if it returns true. Presumably you’ve already tried adding various debug echo statements to show that you’re getting the various DateTime objects created correctly, and that the date inside the loop is what you expect.

By the way, the naming of that function is a bit strange, given that it will only return true if the date passed in is not a weekend.

4

Sorry if I’m going off-topic, but I can’t help but wonder why a “delete rows” would be needed. In my experience the databases I’ve worked with have never had such severe size limitations that routine pruning would be required.

Perhaps it would be a good idea to reconsider your database architecture / table schema and the queries you are using that lead you to think deleting rows is something you should be doing?

If the data is that ephemeral, does it even need to be in a database?

5

if date is changed i do not need the dates that is not between new period so i need to delete them.
Will post my issue soon.

6

I only now could get back to this question again !

I think i just found my mistake.I forgot to add:

mysqli_query($conn, $queryz);

Seems funny. …

But thanks to everyone that tried to help!

7

Well, I imagined that would be where your “insert” comment was, and that you’d omitted it because you felt the issue was elsewhere.

1 Like
8

Yep correct! Such a fail :stuck_out_tongue:

9

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.