Join op kenteken en laatste kilometerstand nodig t.b.v. listbox query

Overzicht Reageren

Sponsored by: Vacatures door Monsterboard

Pagina: 1 2 volgende »

Gerrit broekhuis

gerrit broekhuis

03/02/2012 16:05:55
Quote Anchor link
Hoi,

Ik moet in een listbox kunnen kiezen voor een auto (veld "AUTO" bevat het kenteken). Via een join wil ik uit een kilometerregistratie (tabel "uren")de laatste kilometerstand erbij weergeven. Een selectie van één auto gaat wel wanneer ik op kenteken opvraag, maar het lukt me nog niet om een lijstje te krijgen met alle auto's met daarbij per auto de laatste kilometerstand.

In de praktijk gaat het om bijvoorbeeld 10 auto's, waarbij er in de kilometerregistratie duizenden ritten staan.

Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
<?php
$queryKM
="SELECT Uren.AUTO, Uren.KM, Auto.merk FROM Uren INNER JOIN Auto ON Uren.AUTO = Auto.kenteken ORDER BY Uren.KM DESC LIMIT 1";
?>


Wanneer de query goed gaat is de verdere verwerking geen probleem meer.

Groeten, Gerrit
 
PHP hulp

PHP hulp

01/12/2020 12:35:22
 
Reshad F

Reshad F

03/02/2012 16:14:59
Quote Anchor link
even een vraagje heb je een primaire sleutel die in beide tables zit? zo ja dan kan je het zo doen:

Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
SELECT Auto.auto, Uren.kilometerstand
FROM Auto
INNER JOIN Uren
ON Auto.P_Id=Uren.P_Id
ORDER BY Uren.kilometerstand
Gewijzigd op 03/02/2012 16:15:51 door Reshad F
 
Pieter Jansen

Pieter Jansen

03/02/2012 16:16:15
Quote Anchor link
Wat linkt een auto? Auto.id? of merk?

Je moet natuurlijk wel een onderscheid kunnen maken aan velden die gelinkt kunnen worden. Je selecteert bijvoorbeeld Auto.merk maar dan moet je ook auto.merk joinen op de uren.auto waar uren.auto dan ofwel het merk is of iets anders als id.
 
Jaron T

Jaron T

03/02/2012 16:16:33
Quote Anchor link
Reshadd farid op 03/02/2012 16:14:59:
even een vraagje heb je een primaire sleutel die in beide tables zit? zo ja dan kan je het zo doen:

Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
SELECT Auto.auto, Uren.kilometerstand
FROM Auto
INNER JOIN Uren
ON Auto.P_Id=Uren.P_Id
ORDER BY Uren.kilometerstand


Nee, de primary key 'moet' naar een foreign key verwijzen. Dus zou bijvoorbeeld Uren.auto_id kunnen zijn.
 
Erwin H

Erwin H

03/02/2012 16:19:28
Quote Anchor link
Als je er vanuit kan gaan dat de kilometerstand alleen maar oploopt en nooit afloopt (lijkt mij logisch, maar je weet nooit...) dan kan je met een MAX() functie alleen die uitlezen en met GROUP BY het splitsen per auto. Zoiets dus:

Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
SELECT Uren.AUTO, MAX(Uren.KM), Auto.merk
FROM Uren
INNER JOIN Auto ON Uren.AUTO = Auto.kenteken
GROUP BY Uren.auto, auto.merk
 
Reshad F

Reshad F

03/02/2012 16:19:56
Quote Anchor link
@jaron T dat bedoelde ik sorry :P

Toevoeging op 03/02/2012 16:22:04:

Erwin H op 03/02/2012 16:19:28:
Als je er vanuit kan gaan dat de kilometerstand alleen maar oploopt en nooit afloopt (lijkt mij logisch, maar je weet nooit...) dan kan je met een MAX() functie alleen die uitlezen en met GROUP BY het splitsen per auto. Zoiets dus:

Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
SELECT Uren.AUTO, MAX(Uren.KM), Auto.merk
FROM Uren
INNER JOIN Auto ON Uren.AUTO = Auto.kenteken
GROUP BY Uren.auto, auto.merk


je doet nu INNER JOIN Auto ON uren.AUTO = Auto.kenteken

dus dan heb je de uren en een kenteken toch?
maar moeten die velden niet matchen?
 
Gerrit broekhuis

gerrit broekhuis

03/02/2012 16:43:02
Quote Anchor link
Uren.AUTO bevat net als Auto.kenteken het kenteken als uniek kenmerk. Dit is in geen van beide tabellen de primaire sleutel.

Met de laatste code van Reshadd krijg ik de kentekens in beeld, zonder de kilometerstanden. Verder zijn de kentekens die nog niet in de kilometerregistratie staan vermeld NIET meer zichtbaar. Een nieuwe auto kun je die dus niet kiezen.
 
Reshad F

Reshad F

03/02/2012 16:47:01
Quote Anchor link
@gerrit heb je wel het e.e.a. aangepast aan je database? de p_id enz ? heb je misschien een online voorbeeld?
 
Pieter Jansen

Pieter Jansen

03/02/2012 16:52:08
Quote Anchor link
Om te beginnen zou ik dan eerst Uren.AUTO hernoemen naar Kenteken. Maar wat Reshadd heeft verteld over ID`s is wel raadzaam. Zorg ervoor dat tabellen een unieke primary key hebben. Dat scheelt een berg.
 
Gerrit broekhuis

gerrit broekhuis

03/02/2012 16:55:59
Quote Anchor link
In de database kan ik niets aanpassen omdat een ander programma hier ook gebruik van maakt. Ik ben bezig om een al jaren bestaande database van een bestaand pakket nu via PHP extern toegankelijk te maken.

De tabel "auto" heeft "AUTOINCREMENT" als primary key (autoincrementing...); voor "uren" is dit eveneens het veld "AUTOINCREMENT". Deze zijn int(11). Het KM veld kan in sommige records ook null waarden bevatten of 0 zijn (urenregel zonder gebruik auto).

Join kan m.i. op uren.auto en auto.kenteken (bevat steeds het kenteken).
 
Erwin H

Erwin H

03/02/2012 17:02:09
Quote Anchor link
Gerrit broekhuis op 03/02/2012 16:43:02:
Met de laatste code van Reshadd krijg ik de kentekens in beeld, zonder de kilometerstanden.

Post svp de code die je daadwerkelijk gebruikt. Hier hebben we weinig aan, omdat de ervaring leert dat mensen nogal eens belangrijke punten vergeten of tikfouten maken bij het copieren. Als je de code post die jij gebruikt hebben we het altijd over hetzelfde.
 
Gerrit broekhuis

gerrit broekhuis

03/02/2012 18:49:36
Quote Anchor link
PHP query:

$queryKM="SELECT Uren.AUTO, MAX(Uren.KM3), Auto.merk FROM Uren INNER JOIN Auto ON Uren.AUTO = Auto.kenteken GROUP BY Uren.auto, auto.merk";
// query toont 2 van de 10 kentekens van "auto", geen km's

De tabel uren wordt als volgt gemaakt:

CREATE TABLE `uren` (
`TECHNICUS` char(4) DEFAULT NULL,
`WERKDATUM` date DEFAULT NULL,
`AANV_WERK` char(5) DEFAULT NULL,
`EINDE_WERK` char(5) DEFAULT NULL,
`WERKUUR` decimal(6,2) DEFAULT NULL,
`DECLA_1` tinyint(4) DEFAULT NULL,
`REISUUR` decimal(6,2) DEFAULT NULL,
`DECLA_2` tinyint(4) DEFAULT NULL,
`KM` int(11) DEFAULT NULL,
`DECLA_3` tinyint(4) DEFAULT NULL,
`UURSOORT` char(8) DEFAULT NULL,
`WERKBONNR` int(11) DEFAULT NULL,
`WERKPERIOD` int(11) DEFAULT NULL,
`WERKJAAR` int(11) DEFAULT NULL,
`AANV_REIS` char(5) DEFAULT NULL,
`EINDE_REIS` char(5) DEFAULT NULL,
`PDA_ID` char(40) DEFAULT NULL,
`LEN_UITV` int(11) DEFAULT NULL,
`LEN_MAT` int(11) DEFAULT NULL,
`RANDOMNR` bigint(11) DEFAULT NULL,
`OPLOSSING` text,
`MATERIAAL` text,
`KM1` int(11) DEFAULT NULL,
`KM2` int(11) DEFAULT NULL,
`KM3` int(11) DEFAULT NULL,
`AUTO` char(10) DEFAULT NULL,
`STRIPNR` int(11) DEFAULT NULL,
`AUTOINCREMENT` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`AUTOINCREMENT`)

De tabel auto is als volgt opgebouwd:

CREATE TABLE `auto` (
`kenteken` char(8) DEFAULT NULL,
`merk` char(20) DEFAULT NULL,
`type` char(20) DEFAULT NULL,
`apkdatum` date DEFAULT NULL,
`beheer` char(5) DEFAULT NULL,
`ACTIEF` tinyint(4) DEFAULT NULL,
`AUTOINCREMENT` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`AUTOINCREMENT`)
)
 
Erwin H

Erwin H

03/02/2012 19:07:09
Quote Anchor link
Hmm, ik denk dat er iets anders aan de hand is. Laat eens de code zien waarmee je de data die je uit de database haalt naar het scherm schrijft.
 
Gerrit broekhuis

gerrit broekhuis

03/02/2012 19:22:21
Quote Anchor link
Dit is het relevante stuk (jammer dat het het in stukjes wordt weergegeven):


Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
7
8
9
10
<?php
$con
= mysql_connect("","","");
 if (!$con)
   {

   die('Could not connect: ' . mysql_error());
   }

mysql_select_db("my_db", $con);
$queryKM="SELECT Uren.AUTO, MAX(Uren.KM3), Auto.merk FROM Uren INNER JOIN Auto ON Uren.AUTO = Auto.kenteken GROUP BY Uren.auto, auto.merk";
$resultKM=mysql_query($queryKM,$con);
?>


<table border="1"><tr><td>Auto met KM-stand:&nbsp;</td><td>
<select name="id_auto">
<option value="">Kies een auto!</option>
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
<?php
while($getuserrow4 = mysql_fetch_array($resultKM))
{

?>

<option value="
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
<?php echo $getuserrow4['kenteken'] . " || " . $getuserrow4['KM3']; ?>
">
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
<?php echo $getuserrow4['kenteken'] . " " . $getuserrow4['KM3']; ?>
</option>
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
<?php }
mysql_close($con)
?>

</select></td></tr></table>
Gewijzigd op 03/02/2012 19:24:44 door gerrit broekhuis
 
Erwin H

Erwin H

03/02/2012 19:29:36
Quote Anchor link
als je om het hele stuk [.code.][/.code.] zet (zonder de puntjes) dan gaat het wel goed.

Maar ik heb het probleem al gevonden zoals ik dacht :-)

Je probeert de km stand met $getuserrow4['KM3'] weg te schrijven. Dat werkt alleen niet, omdat door de MAX() functie de kolom naam in MySQL nu "MAX(KM3)" wordt. Geef daar een alias mee en het werkt wel. Dus:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
SELECT Uren.AUTO, MAX(Uren.KM3) AS KM3, Auto.merk
FROM Uren
INNER JOIN Auto ON Uren.AUTO = Auto.kenteken
GROUP BY Uren.auto, auto.merk
 
Gerrit broekhuis

gerrit broekhuis

03/02/2012 19:59:42
Quote Anchor link
Yes, super! Ik zie nu kilometers achter het kenteken, waarvoor dank!

Maar ik zie nog steeds maar 2 van de 10 auto's van de auto tabel. M.a.w. auto's waarvan nog nooit kilometers geboekt zijn worden niet getoond. Deze zou dus wel getoond moeten worden, maar dan zonder kilometers (of met 0 kilometers).

Kan ik die auto's ook zichtbaar maken?
 
Erwin H

Erwin H

03/02/2012 20:08:45
Quote Anchor link
Ja. Een INNER JOIN pakt alleen maar rijen die in beide tabellen bestaan. Een LEFT JOIN daarintegen pakt alle rijen in de linker tabel (de hoofdtabel zeg maar die in je FROM clause) staat en plakt daar rijen uit de andere tabel (in de JOIN) tegenaan, of geeft alle kolommen een NULL als er geen rij in de rechter tabel is.

Dus als je gewoon INNER JOIN vervangt door LEFT JOIN dan krijg je alle auto's te zien. Wat je dan ook nog kan doen is het maximum een mooie waarde geven door het volgende:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
SELECT Uren.AUTO, COALESCE(MAX(Uren.KM3),'geen waarde') AS KM3, Auto.merk
FROM Uren
LEFT JOIN Auto ON Uren.AUTO = Auto.kenteken
GROUP BY Uren.auto, auto.merk

De functie COALESCE() pakt de eerste waarde die is meegegeven die niet NULL is. Als er dus een kilometerstand bekent is wordt die gegeven, anders de andere waarde (in dit geval 'geen waarde', maar dat kan je geven wat je wilt natuurlijk).
 
Gerrit broekhuis

gerrit broekhuis

03/02/2012 20:31:11
Quote Anchor link
Je zal me ondertussen wel een zeur vinden, maar het gaat ondanks je hulp nog niet goed.

Ik krijg nu drie regels te zien (en geen 10), de 1e regel is gevuld met "geen waarde" en dan 2 regels met kentekens. Dit is logisch, want in mijn voorbeeld tabel uren zijn er 2 auto's gebruikt en zijn er een paar regels zonder kenteken.

Volgens mij moet de linker tabel de auto zijn, en moet deze gejoind worden met de uren. Of raak ik nu helemaal het spoor bijster?


Toevoeging op 03/02/2012 20:37:07:

$queryKM="SELECT Auto.kenteken, Auto.merk, COALESCE(MAX(Uren.KM3),'geen waarde') AS KM3 FROM Auto LEFT JOIN Uren ON Auto.kenteken = Uren.AUTO GROUP BY Auto.kenteken, auto.merk";

Nu gaat het wel goed!

Toevoeging op 03/02/2012 21:13:50:

Nog één kleine aanpassing nodig, ik heb van de table "auto" alleen de kentekens nodig waarvan het veld "actief" de waarde 1 heeft.

Ik heb al geprobeerd om "where auto.actief = 1" aan de query toe te voegen, maar dan geeft de query geen records weer.
 
Erwin H

Erwin H

03/02/2012 22:17:48
Quote Anchor link
Gerrit broekhuis op 03/02/2012 20:31:11:
Volgens mij moet de linker tabel de auto zijn, en moet deze gejoind worden met de uren. Of raak ik nu helemaal het spoor bijster?

Klinkt inderdaad wel logisch en dat was dus ook de oplossing :-)
Gerrit broekhuis op 03/02/2012 20:31:11:
Nog één kleine aanpassing nodig, ik heb van de table "auto" alleen de kentekens nodig waarvan het veld "actief" de waarde 1 heeft.

Ik heb al geprobeerd om "where auto.actief = 1" aan de query toe te voegen, maar dan geeft de query geen records weer.

Lijkt me dat dat wel zou moeten werken. Dus enige domme vraag die ik kan stellen is.... zitten er nu dan wel auto's in de database met actief = 1?
 
Gerrit broekhuis

gerrit broekhuis

04/02/2012 15:37:42
Quote Anchor link
$queryKM="SELECT Auto.kenteken, Auto.type, Auto.merk, COALESCE(MAX(Uren.KM3),'0') AS KM3 FROM Auto WHERE ACTIEF=1 LEFT JOIN Uren ON Auto.kenteken = Uren.AUTO GROUP BY Auto.kenteken, auto.merk";

Er zijn 5 auto's met voor het "ACTIEF" de waarde 1 (bij een andere query met listbox werkt de selectie wel).

Heb ik 't op de verkeerde plek in de query opgenomen?
 
Erwin H

Erwin H

04/02/2012 15:41:25
Quote Anchor link
Daarmee kom ik dus weer terug op mijn eerdere opmerking:
Erwin H op 03/02/2012 17:02:09:
Post svp de code die je daadwerkelijk gebruikt.

Want ja, je statement klopt op deze manier niet.
Het zou moeten zijn:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
SELECT Auto.kenteken, Auto.type, Auto.merk, COALESCE(MAX(Uren.KM3),'0') AS KM3
FROM Auto
LEFT JOIN Uren ON Auto.kenteken = Uren.AUTO
WHERE ACTIEF=1
GROUP BY Auto.kenteken, auto.merk

Een JOIN statement moet je eigenlijk zien als een onderdeel van je FROM statement. In dat blok (FROM en JOINs) bepaal je namelijk uit welke tabellen je data selecteert. Daarna pas ga je in een WHERE statement zeggen onder welke voorwaarden dat moet gebeuren.
Gewijzigd op 04/02/2012 15:41:51 door Erwin H
 

Pagina: 1 2 volgende »



Overzicht Reageren

 
 

Om de gebruiksvriendelijkheid van onze website en diensten te optimaliseren maken wij gebruik van cookies. Deze cookies gebruiken wij voor functionaliteiten, analytische gegevens en marketing doeleinden. U vindt meer informatie in onze privacy statement.