Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    New to the CF scene
    Join Date
    Jan 2012
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Mysql 3 record in 1 hr = 1 record in another table

    Hi!

    I have a problem, i have a table, where the information of some devices are deposited if they have an alert, the structure of the table is this:

    ID | SERVERID | TEMP | SENSOR | PROCESSED | DATETIME
    1 | 12458788 | 23.3 | 1 | 0 |2012-01-16 16:50:01

    I need to send the information to another table, if i have 3 records in 1 hour, the script sends only 1 record to another table or 2 followed records = 1 record in another table , do you have some ideas? i really really need help!

    Thank for your time!!

    [EDIT]

    The table populates with various SERVERID's in an hour.....i need to send 1 record to another table for every 3 records in an hour of every serverid..... Thanks!!
    Last edited by rolperez; 01-17-2012 at 07:48 AM. Reason: missunderstood

  • #2
    Senior Coder
    Join Date
    Jul 2011
    Posts
    1,226
    Thanks
    3
    Thanked 171 Times in 171 Posts
    What would this 1 record be?

    Also, why would you want to do this? Why not just pull the data from the other table (I'm assuming both tables are in the same database) as you need it?
    Useful function to retrieve difference in times
    The best PHP resource
    A good PHP FAQ
    PLEASE remember to wrap your code in [PHP] tags.
    PHP Code:
    // Replace this
    if(isset($_POST['submitButton']))
    // With this
    if(!empty($_POST))
    // Then check for values/forms. Some IE versions don't send the submit button 
    Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live.

  • #3
    New to the CF scene
    Join Date
    Jan 2012
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi! Ihave 2 tables 'prealarms' and 'alarms'


    the structure of the table 'prealarms' are this:
    ID | SERVERID | TEMP | SENSOR | PROCESSED | DATETIME
    1 | 12458788 | 23.3 | 1 | 0 |2012-01-16 16:50:01
    2 | 12458789 | 24.3 | 1 | 0 |2012-01-16 16:55:01
    3 | 12458786 | 21.3 | 1 | 0 |2012-01-16 16:55:01
    4 | 12458788 | 21.3 | 1 | 0 |2012-01-16 16:55:01
    etc..

    and i need a query to separate the records by id and if there are three records in a lapse of one hour, send the 'serverid' to the 'alarms' table....only 1 record every 3 records found in a lapse of one hour...

    Thanks!

  • #4
    New Coder
    Join Date
    Sep 2011
    Posts
    40
    Thanks
    0
    Thanked 7 Times in 7 Posts
    Is what you are trying to accomplish

    --- for every hour, if it has 3 or more records, I need to make a record in the other table

    or is it

    --- for every hour, I need to write (#records / 3 rounded down) records to the other table

    or is it

    --- if there exists any record for which 2 other records are within 60 minutes of it, write a record to the other table

    ?



    (The best solution will differ depending on which it is)
    Last edited by themousemaster; 01-17-2012 at 09:50 PM.

  • #5
    New to the CF scene
    Join Date
    Jan 2012
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts
    One server send 1 record every 5 minutes, then the maximum number of records for 1 servers in 'prealarms' are 12....every 3 records i need to send 1 record to 'alarms'....

    the best are this i think:


    "--- for evert hour, if it has more than 3 records, I need to make a record in the other table"

    Thanks!

  • #6
    New to the CF scene
    Join Date
    Jan 2012
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts
    any suggestions?

  • #7
    New to the CF scene
    Join Date
    Jan 2012
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I thik i find an answer, if you have something better, please reply.

    The code i was find is this:

    Code:
    SELECT
    prealarmas.preid,
    prealarmas.serverid,
    prealarmas.temp,
    prealarmas.visto,
    prealarmas.datetime,
    HOUR(prealarmas.datetime) AS h, COUNT(*)
    FROM
    prealarmas
    WHERE
    prealarmas.serverid =  '12345678887' AND
    prealarmas.visto =  '0'
    GROUP BY
    h
    And if h is = or > 3 , insert bla, bla... into 'alarms'

    What doy you think?

  • #8
    New Coder
    Join Date
    Sep 2011
    Posts
    40
    Thanks
    0
    Thanked 7 Times in 7 Posts
    that would be similar to what I would have suggested.

    You are grouping on "h" though, so you dont want to look for h >= 3, you want to look for COUNT(*) >= 3

  • Users who have thanked themousemaster for this post:

    rolperez (01-19-2012)


  •  

    Tags for this Thread

    Posting Permissions

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