CODEWITHSUNDEEP
phpmysqlselect
AMV Veiculos
AMV Veiculos

Reputation: 21

PHP - Select data from database geting first record and others by 3 minutes value

I just wanna know how to select data from specific user in database but just print data in a interval of 3 minutes.

For example: THIS IS DATABSE:

ID ------ User----- Hour----- Minute
#1 ------ USERA---- 09 ------ 00
#2 ------ USERA---- 09 ------ 01
#3 ------ USERA---- 09 ------ 02
#4 ------ USERA---- 09 ------ 03
#5 ------ USERA---- 09 ------ 04
#6 ------ USERA---- 09 ------ 05
#7 ------ USERA---- 09 ------ 06
#8 ------ USERA---- 09 ------ 07
#9 ------ USERA---- 09 ------ 08
#10 ------ USERA---- 09 ------ 09
#11 ------ USERA---- 09 ------ 10

THIS IS WHAT I WANNA SHOW

#1 ------ USERA---- 09 ------ 00
#4 ------ USERA---- 09 ------ 03
#7 ------ USERA---- 09 ------ 06
#10 ------ USERA---- 09 ------ 09
...

Is there a way to do this?

Thanks

Upvotes: 1

Views: 76

Answers (3)

Frédéric Clausset
Frédéric Clausset

Reputation: 1096

You can filter the data with the modulus (%) operator.

foreach ($data as $row) {
    if($row['Minute']%3 == 0){
        echo $row['user'];
    }
}

Upvotes: 0

IsThisJavascript
IsThisJavascript

Reputation: 1716

A quick solution may be to use the modula function..

SELECT * FROM table WHERE (table.Minute % 3) = 0

If you want to start from the first instance of hour,minute and then get 3minutes from that, you may use this query:

SELECT * FROM table WHERE ((table.Minute - (SELECT Minute FROM table ORDER BY table.Minute,table.Hour ASC LIMIT 1)) % 3 ) = 0;

Working demo can be found here: https://paiza.io/projects/4tlF_ZtwTSmSHFY_YKhtaw?language=mysql

Edit:
You may get better results by using GROUP BY

SELECT * FROM table WHERE ((table.Minute - (SELECT table.Minute FROM table GROUP BY table.Minute,table.Hour ORDER BY table.Hour ASC LIMIT 1)) % 3 ) = 0;

Upvotes: 4

Michael
Michael

Reputation: 566

Use MOD

    SELECT 
        ID, 
        User,
        Hour,
        Minute
    FROM table 
    WHERE MOD( Minute, 3 ) = 0
    ORDER BY Hour ASC, Minute ASC;

Upvotes: 3

Related Questions