Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2015
    Location
    Philippines
    Posts
    82
    Rep Power
    4

    PHP Auto System Auto Refresh


    Hello there guys.

    I just want to get opinions from you. I am currently developing a counter reservation. Since the system needs to refresh every 12:00 AM, is there any way to make the system automatically refresh the page (I have here a script that check if its 12:00 AM, so it will delete all the data in the database)

    Because if I depend on user's visit via header refresh, meta or javascript (script will only work if someone visits the site), there are chances that my "delete data script" can be skipped. (For example they will visit the site 7 in the morning).

    Thank you in advance
  2. #2
  3. Impoverished Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,884
    Rep Power
    9646
    Do not delete data. Perhaps once or twice in a lifetime you may encounter a situation where it's needed, but this isn't one of those. Any time you find a need for your application to delete anything, that means you need to fix your application so it doesn't.

    Track your reservations with the date as well as the time. You can easily detect the current date and enter or display data accordingly.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2015
    Location
    Philippines
    Posts
    82
    Rep Power
    4
    Originally Posted by requinix
    Do not delete data. Perhaps once or twice in a lifetime you may encounter a situation where it's needed, but this isn't one of those. Any time you find a need for your application to delete anything, that means you need to fix your application so it doesn't.

    Track your reservations with the date as well as the time. You can easily detect the current date and enter or display data accordingly.
    Thank you, noted on that.

    PHP Code:

        date_default_timezone_set
    ('Asia/Singapore');

        
    $t strtotime('tomorrow 17:55');
        if(
    $t === time()) {
            
    $stmt $core->prepare("SELECT * FROM queue");
            
    $stmt->execute();

            while(
    $row $stmt->fetch(PDO::FETCH_ASSOC)) {
                
    $time time();
                
    $stmt2 $core->prepare("INSERT INTO history_queue VALUES(null, :qid, :uid, :sid, :fin, :fint, :stamp);");
                
    $stmt2->bindParam(":qid"$row['id']);
                
    $stmt2->bindParam(":uid"$row['user_id']);
                
    $stmt2->bindParam(":sid"$row['staff']);
                
    $stmt2->bindParam(":fin"$row['finished']);
                
    $stmt2->bindParam(":fint"$row['finish_time']);
                
    $stmt2->bindParam(":stamp"$time);
                
    $stmt2->execute();
            }

                    
    // Insert statistical data to db for future use

            
    $stmt2 $core->prepare("DELETE FROM queue");
            
    $stmt2->execute();

            
    $stmt2 $core->prepare("UPDATE time_schedule SET counter = 0");
            
    $stmt2->execute();
        } 
    My system needs to reset the data every midnight which is 5:55 PM (nvm the 12 midnight earlier). But this code is inside the db.inc where it is required_once in every page. This code will only run every time a user visits the page. Is there any way that it will automatically run (or should I say the db.inc will automatically refresh every 5 second interval so it will run) without a visit from a user?
  6. #4
  7. Impoverished Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,884
    Rep Power
    9646
    The solution is perhaps much simpler than you think it is: only display information for the desired "day". If it resets at 17:55 then only display data >= yesterday 17:55 and < today 17:55. You don't have to, and shouldn't, do anything to the data that's stored.

    I can probably explain better. What's an example of how this daily reset affects your application?
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2015
    Location
    Philippines
    Posts
    82
    Rep Power
    4
    Originally Posted by requinix
    The solution is perhaps much simpler than you think it is: only display information for the desired "day". If it resets at 17:55 then only display data >= yesterday 17:55 and < today 17:55. You don't have to, and shouldn't, do anything to the data that's stored.

    I can probably explain better. What's an example of how this daily reset affects your application?
    Thank you for fast response!

    My website is a scheduling system. It's more like passport appointment application, here each user of organization will need to select their schedule from 8 AM to 4 PM (8:00 - 9:30, 9:30, 11:00 so on..). Scheduling time will be from 6:00 PM to 7:00 AM which means they will schedule off-campus. Each user will be entertained by several windows (4 windows) (It will be automatically generated where they will be assigned, First In First Out basis each window)

    Code:
    windows table:  id | window_name | occupied | user_id | active | stamp
    My system will count whether user appeared on their appointment or not. Occupied column will count how many users appeared on their appointment. user_id will serve as the user who's managing the window.

    Code:
    queue table: id | user_id | date | time_schedule | finished | finish_time | staff | stamp 
    The thing which I want to reset every 17:55 is the occupied, user_id, and active. Also, I'd like to move current queue table's data to history_queue so I can easily manage the analytics.

    But if I do the:

    Code:
    display data >= yesterday 17:55 and < today 17:55
    It won't be triggered unless there will be a user who will open / login on the page for that script to be run. There is only 5 minutes interval before others can appoint again for tomorrow's schedule.
  10. #6
  11. Impoverished Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,884
    Rep Power
    9646
    "windows" sounds like the list of all possible windows. They can potentially all be used once a day, or something like that. Okay. The problem is that you're mixing two types of data into that one table: one set is about the window itself in a general sense, like the name, and the other set is about the usage of that window.

    Split it into two tables. The main window table is just the first set of data. The ID and name. It might not ever get new records, and since there's just the name it might never even get edited once in place.
    The second table is the usage of each window, and has the ID (as a foreign key) with the occupied, user_id, and active columns. It also needs to know what date the usage is for, so add a column for that.

    Not sure what the queue table is for.

    How about some example data?


    And I'm moving this to the MySQL forum.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2015
    Location
    Philippines
    Posts
    82
    Rep Power
    4
    Once again, thank you for replying.

    Here's my important tables:

    queue - id | user_id | date | time_schedule | finished | finish_time | staff | campus | stamp
    Sample Data for queue - 1 | 1 | timestamphere | 1 | 0 | 0 | 2 | 1 | timestamphere

    Queue table is where schedules/appointment goes. time_schedule from 8:00 - 8:30 with id of 1 so on...

    windows - id | window_name | occupied | user_id | stamp
    Sample Data for windows - 1 | Window 1 | 1 | 2 | timestamphere

    windows table is where each window is stored. If Window 1 is only active, then all will FIFO in Window 1

    history_queue - id | queue_id | user_id | staff_id | finished | finish_time | stamp
    Sample Data for history_queue - 1 | 1 | 1 | 2 | 1 | timestamphere | timestamphere

    History Queue table is where queue goes when 17:55 script runs. This is for neat analytic purpose. If there's something better please note me thank you.

    cashier - id | user_id | window | time_in
    Sample Data for cashier - 1 | 2 | 1 | timestamphere

    Cashier table is the staff who's currently logged in on the n window. Window table and cashier table can be joined together.
  14. #8
  15. Impoverished Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,884
    Rep Power
    9646
    "timestamphere" isn't too helpful. Are they DATEs? TIMEs? DATETIMEs? In fact, what does SHOW CREATE TABLE output for each of those tables?
    Why is queue.finish_time 0 and history_queue.finish_time a timestamp? Is queue.staff the number of staff? queue.campus a foreign key? If history_queue is a historical copy of the queue data then why isn't it nearly the same structure?

    Can you describe how the data is getting into those tables? queue is apparently added by the user when they do the thing, windows is a list of the available time windows and they get set up at some point without user intervention, and cashier is something new so I don't know but I would guess it has the "user_id" values.

    And now that I know you have a history_queue table, is it already being used or what? Did you just add it now?
    Last edited by requinix; July 20th, 2018 at 09:04 AM.
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2015
    Location
    Philippines
    Posts
    82
    Rep Power
    4
    Hello there again requinix. Thank you

    Sorry I didn't include the timestamp.

    timestamphere - its a stamp from time()

    finish_time will be set when the user finished his appointment or when staff clicked "Finished" on his console so that he will move to the next client. Else it will just be set to 0 if the client didnt appeared and it will be marked as 2 in finished column.
    By the way, the finished column consist of:
    0 - still not entertained by the cashier/window
    1 - completed his appointment
    2 - didnt appeared
    3 - cancelled

    queue.staff its a user.id also. User has role column in it. I forgot to include it here. Also the campus, since I am currently working for a single campus, all should be 1 and yes a foreign key.
  18. #10
  19. Impoverished Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,884
    Rep Power
    9646
    time() the PHP function that returns the current time (which surely it should not be?) or TIME() the MySQL function that returns the time portion of a date string?

    Anyway, there's two basic rules I'd like to see you work within when designing the database:
    1. Don't delete data.
    2. Don't alter data on a regular basis. I'm talking mostly about actions not driven by external (user or cashier) inputs.

    For example, clearing out the queue table at 17:55 every day violates the first rule. Instead try to see that table as having all queues, past and future. That also means you don't need the history_queue table.
    Also, setting an appointment to be 2 (didn't appear) probably violates the second rule because I'd expect that the system is automatically marking it as such when the time has passed. Instead you can tell whether someone met their appointment by checking that finished=0 (ie, wasn't completed or canceled) and the appointment date was in the past.

    Does that make sense? Can you see where I'm trying to go with this?
    Last edited by requinix; July 21st, 2018 at 12:26 AM.
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2015
    Location
    Philippines
    Posts
    82
    Rep Power
    4
    Ohh thanks Mr. requinix.

    1. That make sense, specially when storing data and they should not be CLEARED OUT in the table. I see dozens of purpose here like for whole year analytics / past year's analytics vs new just in one table.

    2. I have a question here, how can I determine manually if the user met his appointment? It can be triggered only by the cashier or staff by clicking the button. Should it be only 0 = canceled (including didn't appeared), and 1 = finished?
  22. #12
  23. Impoverished Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,884
    Rep Power
    9646
    Surely you want to know the difference between a cancel and a no-show? That means three possible states:
    1. Appointment was set up and nothing has happened with it. This is the initial state, and it remains in this state unless either of the next two events happens. That means it remains here if the user doesn't show for their appointment.
    2. The person showed up for the appointment and all went well. The employee tells the system when the appointment is complete.
    3. The person did not show up for the appointment. The user, or perhaps an employee, tells the system to cancel the appointment.
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2015
    Location
    Philippines
    Posts
    82
    Rep Power
    4
    Ohh now I get it. Thank you for your help fixing my database structure for this program! And now I get it on how I will put everything in place. Thank you so much!
  26. #14
  27. Impoverished Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,884
    Rep Power
    9646
    Re-reading #3 confuses me.

    3. The person did not want to show up for the appointment. Someone told the system to cancel.

    To be absolutely clear (and repeat myself), if they no-show then the appointment stays in the original state.
  28. #15
  29. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2015
    Location
    Philippines
    Posts
    82
    Rep Power
    4
    Hello sir requinix!

    I was reading back again this thread and I have another question needs to be clarified. I'd like to know how should I store the finished time when appointment is done and the stamped time where the user set up his/her appointment (basically, when did the user inserted his appointment)

    Also, I'd like to know some basic rules about designing my database structure about this project (if there's still any). Since the two rules really helped me a lot (thanks to you sir!) to work on this project more flexible.

    Thank you in advance!
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo