Join met val elk records de meest actuele
Omdat php en mysql een beetje een hobby is ben ik dus niet een expert.
Misschien dat ik daarom niet de oplossing vind voor dit probleem:
SELECT *
FROM tr_objecten
INNER JOIN data_tracking1
ON tr_objecten.imei = data_tracking1.IMEI WHERE DATE_TIME_STAMP + INTERVAL 10 MINUTE > NOW() ORDER BY data_tracking1.id DESC";
De opzet is als volgt:
In de database tr_objecten staan de unieke objecten met hun imei nummer.
Elk object stuur in elk geval elke 10 minuten een signaal met daarin zijn imei nummer welke in de database data_tracking1 wordt opgeslagen. Daaruit wil ik hebben: Van alle objecten welke zowel in tr_objecten als in data_tracking1 voorkomen de meest actuele.
De bovenstaande code werkt op zich wel maar krijg ik van elk object de laatste meldingen van de laatste 10 minuten. Dat lijkt goed maar de objecten sturen ook signalen tussen door bijvoorbeeld als ze verplaatsen. Maar ik wil enkel het meest actuele records per imei nummer...
LIMIT 1 werkt dus niet want dan krijg het van alle objecten gezamenlijk de laatste (is dus maar 1 terwijl er meerdere objecten zijn...)
Als er 20 objecten zijn wil ik dus ook 20 resultaten...
Gewijzigd op 16/09/2016 21:55:21 door Richard veldman
Je kunt de aggregate functie MAX() gebruiken in combinatie met een GROUP BY.
probeersel:
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
SELECT o.imei, dt.id, MAX(dt.DATE_TIME_STAMP)
FROM tr_objecten o
INNER JOIN data_tracking1 dt
ON o.imei = dt.IMEI
WHERE dt.DATE_TIME_STAMP + INTERVAL 10 MINUTE > NOW()
GROUP BY o.imei
ORDER BY dt.id DESC";
FROM tr_objecten o
INNER JOIN data_tracking1 dt
ON o.imei = dt.IMEI
WHERE dt.DATE_TIME_STAMP + INTERVAL 10 MINUTE > NOW()
GROUP BY o.imei
ORDER BY dt.id DESC";
Code (php)
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
<?php
$query = "
SELECT *
FROM tr_objecten
INNER JOIN data_tracking1
ON tr_objecten.imei = data_tracking1.IMEI
WHERE DATE_TIME_STAMP + INTERVAL 1 DAY > NOW()
GROUP BY tr_objecten.imei
ORDER BY data_tracking1.ID DESC
";
?>
$query = "
SELECT *
FROM tr_objecten
INNER JOIN data_tracking1
ON tr_objecten.imei = data_tracking1.IMEI
WHERE DATE_TIME_STAMP + INTERVAL 1 DAY > NOW()
GROUP BY tr_objecten.imei
ORDER BY data_tracking1.ID DESC
";
?>
Maar nu krijg ik het oudste record terwijl ik de nieuwste wil.
Dat geef ik toch aan met DESC?
Gewijzigd op 20/09/2016 19:13:58 door richard veldman
DESC == aflopend (Dus begin je met de hoogste waarde)
Volgens mij moet je dus ASC hebben...
Het lijkt er dus een beetje op alsof de ORDE BY niet doet wat ik verwacht...
`ID` int(11) NOT NULL AUTO_INCREMENT,
`DATE_TIME_STAMP` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`DATE` varchar(15) NOT NULL,
`TIME` time NOT NULL,
`LAT` varchar(10) NOT NULL,
`LON` varchar(10) NOT NULL,
`LATN` varchar(15) NOT NULL,
`LONN` varchar(15) NOT NULL,
`ALT` varchar(6) NOT NULL,
`SPEED` varchar(6) NOT NULL,
`COURSE` varchar(20) NOT NULL,
PRIMARY KEY (`ID`)
tr_objecten:
`id` int(5) NOT NULL AUTO_INCREMENT,
`tonen` int(1) NOT NULL DEFAULT '0',
`member` varchar(30) NOT NULL,
`naam` varchar(20) NOT NULL,
`type` varchar(10) NOT NULL,
`database` varchar(20) NOT NULL,
`imei` varchar(20) NOT NULL,
`sim` varchar(20) NOT NULL,
`telnr` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
<?php
$query = "
SELECT *
MAX(data_tracking1.DATE_TIME_STAMP)
FROM tr_objecten
INNER JOIN data_tracking1
ON tr_objecten.imei = data_tracking1.IMEI
WHERE DATE_TIME_STAMP + INTERVAL 1 DAY > NOW()
GROUP BY tr_objecten.imei
ORDER BY data_tracking1.ID ASC
";
?>
$query = "
SELECT *
MAX(data_tracking1.DATE_TIME_STAMP)
FROM tr_objecten
INNER JOIN data_tracking1
ON tr_objecten.imei = data_tracking1.IMEI
WHERE DATE_TIME_STAMP + INTERVAL 1 DAY > NOW()
GROUP BY tr_objecten.imei
ORDER BY data_tracking1.ID ASC
";
?>
Maar dan krijg ik niets meer terug.
Als ik dat MAX eruit haal krijg ik van elk object 1 record terug uit de data_tracking1.
Maar dan dus het oudste record ipv het jongste...
Gewijzigd op 20/09/2016 20:24:25 door richard veldman
SELECT *, MAX(data_tracking1.DATE_TIME_STAMP)
Toevoeging op 20/09/2016 22:40:12:
Je doet dus ook niets aan foutafhandeling. Anders kreeg je wel een nette foutmelding.
Ook nu blijf ik het oudste record zien ipv het nieuwste....
Of ik nu DESC of ASC gebruik...
Gewijzigd op 23/09/2016 16:50:00 door richard veldman
De eerste stap is bepalen wat het laatst ingevoerde record in datatracking voor een imeinummer is:
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
SELECT
IMEI,
MAX(id)
FROM
datatracking1
WHERE DATE_TIME_STAMP > NOW() - INTERVAL 1 DAY
GROUP BY IMEI
IMEI,
MAX(id)
FROM
datatracking1
WHERE DATE_TIME_STAMP > NOW() - INTERVAL 1 DAY
GROUP BY IMEI
Let op het verschil in de where!
Dit zet je dan in een subquery en dan ga je de anderen gegevens erbij halen:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT
o.IMEI,
o.naam,
t.DATE_TIME_STAMP
FROM
(
SELECT
IMEI,
MAX(ID)max_id
FROM
data_tracking1
WHERE DATE_TIME_STAMP > NOW() - INTERVAL 1 DAY
GROUP BY IMEI
) s
JOIN
tr_objecten o ON s.IMEI = o.imei
JOIN
data_tracking1 t ON s.max_id = t.ID
o.IMEI,
o.naam,
t.DATE_TIME_STAMP
FROM
(
SELECT
IMEI,
MAX(ID)max_id
FROM
data_tracking1
WHERE DATE_TIME_STAMP > NOW() - INTERVAL 1 DAY
GROUP BY IMEI
) s
JOIN
tr_objecten o ON s.IMEI = o.imei
JOIN
data_tracking1 t ON s.max_id = t.ID
Bedankt!