How to create repeatable event php/mysql (weekly)

Question

I am trying to create an event which should repeat every week at the same time.

This is what I have right now:

User sends postString to my php script looking like this:

weekly=true&dateFrom=2016-10-01 14:00&dateTo=2016-10-01 15:00&usrId=1

Then my MySQL statement with php script insert these data to MySQL DB. This is my inserting function:

public function newEvent($weekly, $dateFrom, $dateTo, $usrId) {

    $sql = "insert into myTableName set weekly=?, dateFrom=?, dateTo=?, usrId=?";
    $statement = $this->conn->prepare($sql);
    if (!$statement)
        throw new Exception($statement->error);
    $statement->bind_param("ssss", $weekly, $price, $sport, $participants, $latitude, $longitude, $description, $dateFrom, $dateTo, $usrId, $title);
    $returnValue = $statement->execute();
    return $returnValue;
}

It works fine , but my actual goal is:

When user sends postString like example above and if weekly is true I would like my event to repeat every week at the same time and I would like to be able to search the event in some future week.

Example: My weekly=true&dateFrom=2016-10-01 14:00&dateTo=2016-10-01 15:00&usrId=1 is like this and if I ran this MySQL statement

SELECT *
    FROM myTableName WHERE dateFrom >= 2016-10-08 13:00 
        AND dateTo <= 2016-10-08 16:00 LIMIT 0 , 30

I would like to get the data from my event which had time dateFrom=2016-10-01 14:00&dateTo=2016-10-01 15:00 when it was created.

My question is:

Is there any way how can I accomplish the thing I have just described?

If you need more details just let me know. Thank you very much :)

I am mostly iOS developer so I use php/MySQL very marginally. — 0ndre_
– 0ndre_, Commented Oct 1, 2016 at 9:37
I don't understand. You're duplicating the original event each day when it is weekly? How do you keep reference to the original event? I would expect your SELECT statement to include "where weekly='true'" or something like that. Or have PHP handle it right after: if weekly do this, else... — Christian Bonato
– Christian Bonato, Commented Oct 1, 2016 at 9:50
When one user1 does this SELECT * FROM myTableName WHERE dateFrom >= 2016-10-08 13:00 AND dateTo <= 2016-10-08 16:00 LIMIT 0 , 30 I would like to show him the original event from other user2 weekly=true&dateFrom=2016-10-01 14:00&dateTo=2016-10-01 15:00&usrId=1 but user1 is looking for his date and he does not know the original time. — 0ndre_
– 0ndre_, Commented Oct 1, 2016 at 9:54

ch271828n · Accepted Answer · 2016-10-01 10:30:26Z

2

How about this:

Add two columns: weeklyDateFrom, weeklyDateTo, which storages the minutes from Monday 0:00.

For example, for 2016-10-01 13:00 is Sat. 13:00, you just storage it as 7560, because 5day+6h=126h=7560min. Then 2016-10-01 15:00 is 7720.

When you want to search 10-08 12:00 ~ 16:00, just do:

SELECT *
    FROM myTableName WHERE weeklyDateFrom >= 7500
    AND weeklyDateTo <= 7780 LIMIT 0 , 30

And you will get it, with all the original event data not missing!

Hope this works! Have a good day!

answered Oct 1, 2016 at 10:30

ch271828n

17.9k5 gold badges71 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

0ndre_ Over a year ago

Awesome Idea!! I will definitely try it

ch271828n Over a year ago

@0ndre_ thanks for your appreciation! (If you like it, you can choose it as the best answer and give it a vote up.)

0ndre_ Over a year ago

I have already voted up and I will accept it when I solve my problem. :)

Collectives™ on Stack Overflow

How to create repeatable event php/mysql (weekly)

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related