Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2012
    Posts
    19

    Unanswered: update a record after 24 hour

    how do i update a record using php

    i have a row in table tuser

    sms_remaining is my column name .....

    when user login he has 10 sms messages to send ... but when he send 10 sms his limit reached ...

    when 24 hour passed

    so i want to do that this column after 24 hour updated for every user.

    how do i do that ?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I'd redesign your database...

    Add a new table with columns similar to: user_id, message_id, when_sent [datetime]

    Create a new entry in this table when each message is sent.

    You can then easily find how many messages each user has sent in a day and then decide whether you want to allow any further sending or not.
    Code:
    SELECT Count(*) As number_of_messages
    FROM   message_log
    WHERE  user_id = 937
    AND    when_sent >= CurDate()
    AND    when_sent <  DateAdd(dd, 1, CurDate())
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Do you mean the user has 10 SMS within a 24 hour period
    Or only ever has 24 SMS.

    Assuming its 24 messages in a 24 hour period you need to record when the user sent each message. Then you know what messages were sent
    A simple COUNT of the messages sent BETWEEN 24 hours ago and NOW() w will give you the number of messages sent in the window. Subtracting that from the user allocation gives the number of messages left.

    Unless you have good reasons its never smart to store something like messages left. The reason
    Say you want to change the SMS limit.
    Or want to change the number of messages over a time period.
    Or want to alter the allocation per member.

    If you are concerned about storage space you could delete message that are say a week old. Running the delete query AFTER you retrirved yhe messages for that user.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jan 2012
    Posts
    19
    yes only 10 sms in 24 hour period the counter decreases and reach to 0 then after 24 hour it again go to 10 server time period...

  5. #5
    Join Date
    Jan 2012
    Posts
    19
    Quote Originally Posted by healdem View Post
    Do you mean the user has 10 SMS within a 24 hour period
    Or only ever has 24 SMS.

    Assuming its 24 messages in a 24 hour period you need to record when the user sent each message. Then you know what messages were sent
    A simple COUNT of the messages sent BETWEEN 24 hours ago and NOW() w will give you the number of messages sent in the window. Subtracting that from the user allocation gives the number of messages left.

    Unless you have good reasons its never smart to store something like messages left. The reason
    Say you want to change the SMS limit.
    Or want to change the number of messages over a time period.
    Or want to alter the allocation per member.

    If you are concerned about storage space you could delete message that are say a week old. Running the delete query AFTER you retrirved yhe messages for that user.

    Help me ...

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have a table with the date and time of each SMS message sent (doens't need to store the SMS, just the date and time it was sent + the userid

    as GVee suggests then run a count query for the specified period
    Code:
    select count(*) from mySMSTable where DateSMSSent between dateadd(now() - INTERVAL 1 DAY) and now() and userid = 'altafhpatel';
    or
    Code:
    select count(*) from mySMSTable where DateSMSSent >= dateadd(now() - INTERVAL 1 DAY) and userid = 'altafhpatel';
    to delete old messages
    Code:
    delete from MySMSTable where  dateadd(now() - INTERVAL 7 DAY) and userid = 'altafhpatel';
    you will need to replace the table and column names with the names from your new table
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jan 2012
    Posts
    19

    Here is the data i used

    see the query please

    then suggest me

    when user sent a message it counts

    user didn't not send msg in specific time any time he can send sms

    but only the thing is on every day server time like after 24 hours

    automatic update the row this is more simple i am beginner so please help me in beginner perspective i can't handle such huge queries

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    no
    you don't store the number of messages a user has left, but instead you record each message the user has sent
    from that you can work out how many messages a user has sent in a specific time period as suggested in posts 2,3 & 6
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    CREATE TABLE message_log (
       user_id varchar(10) NOT NULL
     , when_sent datetime NOT NULL DEFAULT (Current_Timestamp)
    );
    Every time a user sends a message:
    Code:
    INSERT INTO message_log (user_id) VALUES ('BILL');
    George
    Home | Blog

  10. #10
    Join Date
    Jan 2012
    Posts
    19
    sir healdem and gvee

    i made table now how do i check the remaining messages

    like i send 4 sms in from 00:01 to 9:30 pm

    still remaining msgs 6

    a user login again and how do i write the query

    PHP Code:
    select count (*) from message_log where user_id=$session_user

    i got he send 4 sms but i don't know about date and time concerns please write a query for me


    or should i use above queries you give me ??/
    Last edited by altafhpatel; 08-20-13 at 13:37.

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by altafhpatel View Post
    ...or should i use above queries you give me ??/
    ..now thats a radical thought......
    but bear n mind you will need to replace the table & column names in our examples with the tabel and column names used by you
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •