Profiellijst sorteren op afstand
Maar staan gesorteerd op id, dus wie eerste geregstreerd heeft staat als eerste in de lijst.
Nou wil ik een zoekfunctie maken, op basis van provincie, maar moet gesorteerd worden op afstand.
Bijv. gebruiker zoekt personen die dicht bij.. Zuid HOlland wonen.
Hoe kan nou ervoor zorgen dat ik nu de profielen zo gesorteerd krijg dat degene die het dichtsbij wonen bovenaan komen? Bijv. niemand in zuidholland, wel in noordholland en zeeland, deze moeten bovenaan komen
samen met utrecht, dan pas de provincies eromheen.
De provincies worden opgeslagen als integer, die staat voor een provincie in nederland (op alfabetische volgorge)
Moet ik nou een soort 'map' maken in multidemensionale array? Of hoe ga ik dit aanpakken?
Groetjes
Of iemand in de gekozen provincie woont kan je vervolgens met een virtuele kolom regelen:
Code (php)
1
2
3
2
3
SELECT naam, provincie, IF(provincie = 12, 0, 1) AS woont_in
FROM profielen
ORDER BY woont_in
FROM profielen
ORDER BY woont_in
de virtuele kolom 'woont_in' geeft nu 0 als de provincie gelijk is aan 12, 1 als dat niet zo is. Met andere woorden iedereen die in de provincie '12' woont (wat je dus kan aanpassen naar de provincie waarop gezocht moet worden) komt nu bovenaan te staan, want je sorteert nu op die kolom.
Voor naburige provincies zal je nog iets anders moeten bedenken, want de database moet wel weten welke provincies aan elkaar grenzen. Aan de hand van puur naam of id kan je dat niet krijgen.
Hoe wordt er dan gesorteerd? Elk profiel met prinvincie 12 wordt 0, (dus komen bovenin) want 0 gaat voor 1
Maar de rest sorteerd ie wel mee? Dus is iig beter dan where provincie = 12, want dan laat ie de rest niet zien :P
Zo begrijp ik het goed toch?
hmmmz, wat als ik aan de tabel provincies neighbours maak
en de aangrenzende provincies meegeef.
NH = 1,ZH = 2,ZL = 3,FR = 4,FL = 5,UT = 6,NB = 7,GR = 8,DR = 9,OV = 10,GL = 11,LM = 12
(Dit is ff simpel de provincies en ID
Als ik nu utrecht ( ID 5, als neighbours geef, 1,2,4,10 )
Noord holland, zuid holland, flevolan en gelderland
Zou ik dan aparte queries moeten uitvoeren? Ten eerste voor provincie=utrecht
Vervolgens voor elke 'neighbour' een nieuwe querie uitvoeren, totdat alle profielen er zijn
en dan voor elke neighbour weer opnieuw queries uitvoeren voor de neighbours op hun beurt?
Ik heb ergens gezien dat je afstand kan uitlezen uit googlemaps met een api ?
Is het niet makkelijker om de 'gezochte provincie' de afstanen te vergelijken met de andere provincies, deze opslaan en vervolgens sorteren en dan aparte queries ?
Ik weet niet wat beter is, of sneller werkt want mag natuurlijk niet teveel vergen van de server
Het enige wat 'IF(provincie = 12, 0, 1) AS woont_in' doet is een nieuwe (virtuele) kolom aanmaken met allemaal 0-en en 1-en erin. Iemand die in de gekozen provincie woont krijgt een 0, iemand die er niet in woont krijgt een 1. Dit geldt voor alle geselecteerde rijen. Vervolgens sorteer je hierop (nogmaals, in de ORDER BY) en zo krijg je dus de mensen in de provincie bovenaan.
Voor het tweede deel heb ik nu even geen tijd, maar als er niemand anders met een antwoord komt kan ik je daar later ook nog wel mee helpen. Op zich kan je het op een gelijke manier regelen als hierboven, in elk geval hoef je niet met meerdere queries gaan werken.
Maar ik had het dus goed begrepen, alle queries met provincie 12 worden 0, en dus bovenaan
alleen hij moet de rest (die dan 1 zijn, nog eens sorteren op afstand)
Ik zou misschien met google api een array kunnen maken van afstanden vanaf dit geval provincie 12
met de rest van de provincies
de array sorteren van klein naar groot en dan voor elke waarde opnieuw querie uitvoeren
Of is er een mogelijkheid om het in 1 querie te doen?
dus eerst where provincie = 12, dan provincie 4, 5, 9, 10 enz
Om te beginnen zou ik een extra tabel aanmaken met daarin de afstanden, of grenzen, tussen de provincies. Als je het alleen binnen Nederland houdt dan is dit zonder meer te doen, aangezien er niet zoveel provincies zijn. Wil je echter de hele wereld doen, of bijvoorbeeld tussen woonplaatsen dan zal je 'on-the-fly' berekeningen moeten gaan doen, omdat anders je tabel veel te groot wordt.
Je kan eventueel echte afstanden maken, met behulp van de Google API bijvoorbeeld, ik zou ervoor kiezen om een schematische afstanden tabel te maken. Provincies die aan elkaar grenzen geef je afstand 1, zit er 1 provincie tussen geef je het afstand 2, 2 provincies tussen afstand 3 etc.
- Noord-Holland - Zuid-Holland: afstand 1
- Noord-Holland - Zeeland: afstand 2
- Noord-Holland - Limburg: afstand 3
- etc.
Code (php)
1
2
3
4
5
6
2
3
4
5
6
CREATE TABLE afstanden(
van_provincie TINYINT NOT NULL DEFAULT 0,
naar_provincie TINYINT NOT NULL DEFAULT 0,
afstand TINYINT NOT NULL DEFAULT 0,
PRIMARY KEY(van_provincie, naar_provincie)
);
van_provincie TINYINT NOT NULL DEFAULT 0,
naar_provincie TINYINT NOT NULL DEFAULT 0,
afstand TINYINT NOT NULL DEFAULT 0,
PRIMARY KEY(van_provincie, naar_provincie)
);
Nu kan je natuurlijk elke combinatie dubbel opnemen (NH - ZH en ZH - NH), je kan ook een simpele view bouwen om dat te krijgen:
Code (php)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
CREATE VIEW v_afstanden AS
SELECT van_provincie, naar_provincie, afstand
FROM afstanden
UNION
SELECT naar_provincie, van_provincie, afstand
FROM afstanden
UNION
SELECT van_provincie, van_provincie, 0
FROM afstanden
SELECT van_provincie, naar_provincie, afstand
FROM afstanden
UNION
SELECT naar_provincie, van_provincie, afstand
FROM afstanden
UNION
SELECT van_provincie, van_provincie, 0
FROM afstanden
Dit scheelt je de helft aan rijen in je tabel.
Merk op dat ik de combinatie NH-NH (dus de afstand van een provincie naar zichzelf) niet heb opgenomen in de tabel, maar nu welk aanmaak in de view. Dit zorgt ervoor dat je altijd een waarde krijgt in de query hieronder.
Vervolgens join je deze view in je select statement, waarbij je dus voor elke profiel de afstand krijgt tot de provincie waarop je hebt geselecteerd:
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
SELECT naam, provincie, afstand
FROM profielen
LEFT JOIN v_afstanden ON (
van_provincie = 12
AND naar_provincie = provincie
)
ORDER BY afstand
FROM profielen
LEFT JOIN v_afstanden ON (
van_provincie = 12
AND naar_provincie = provincie
)
ORDER BY afstand
12 (in de join voorwaarde) is wederom de provincie waarop je selecteert. Afstand geeft nu voor elke rij de afstand tot de gekozen provincie. Als je daarop sorteert krijg je dus eerst alle profielen te zien binnen de provincie waarop je hebt geselecteerd, dan alle aangrenzende provincies en dan de rest.
Gewijzigd op 29/03/2013 16:17:15 door Erwin H
zodra die al 9 of 10 provincies gehad slaat ie op hol.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Van Drenthe naar Drenthe = 0 km
Van Drenthe naar Flevoland = 83.447646613132 km
Van Drenthe naar Friesland = 61.162425003624 km
Van Drenthe naar Gelderland = 112.49174867259 km
Van Drenthe naar Groningen = 30.369867403102 km
Van Drenthe naar Limburg = 300.6741717685 km
Van Drenthe naar Noord-Brabant = 188.43434857796 km
Van Drenthe naar Noord-Holland = 132.3234312833 km
Van Drenthe naar Overijssel = 57.164065708213 km
Van Drenthe naar Utrecht = 139.53400775791 km
Undefined offset: 0
Notice: Trying to get property of non-object in
Notice: Trying to get property of non-object in
Notice: Trying to get property of non-object in
Undefined offset: 0
Notice: Trying to get property of non-object in
Notice: Trying to get property of non-object in
Notice: Trying to get property of non-object in Van Drenthe naar Zeeland = 5919.2592537347 km
Undefined offset: 0
Notice: Trying to get property of non-object in
Notice: Trying to get property of non-object in
Notice: Trying to get property of non-object in Van Drenthe naar Zuid-Holland = 5919.2592537347 km
Van Drenthe naar Flevoland = 83.447646613132 km
Van Drenthe naar Friesland = 61.162425003624 km
Van Drenthe naar Gelderland = 112.49174867259 km
Van Drenthe naar Groningen = 30.369867403102 km
Van Drenthe naar Limburg = 300.6741717685 km
Van Drenthe naar Noord-Brabant = 188.43434857796 km
Van Drenthe naar Noord-Holland = 132.3234312833 km
Van Drenthe naar Overijssel = 57.164065708213 km
Van Drenthe naar Utrecht = 139.53400775791 km
Undefined offset: 0
Notice: Trying to get property of non-object in
Notice: Trying to get property of non-object in
Notice: Trying to get property of non-object in
Undefined offset: 0
Notice: Trying to get property of non-object in
Notice: Trying to get property of non-object in
Notice: Trying to get property of non-object in Van Drenthe naar Zeeland = 5919.2592537347 km
Undefined offset: 0
Notice: Trying to get property of non-object in
Notice: Trying to get property of non-object in
Notice: Trying to get property of non-object in Van Drenthe naar Zuid-Holland = 5919.2592537347 km
terwijl als ik na de foreach handmatig naar zuidholland check, dan geeft ie normaal de afstand aan
Maar dit kost dus allemaal tijd, ik gaat zeker proberen wat jij danet liet zien, gelijk aan de slag om de afstanden in te voeren
zal wel ff duren 144 records invoeren
Gewijzigd op 30/03/2013 08:36:36 door Dennis WhoCares
Alleen ik mis de afstand van 12 naar 12 = 0 in de view. :S de rest heeft ie wel 1 tot 11, maar goed.. Dat komt natuurlijk omdat heel de afstand vanaf provincie 12 niet meer voorkomt omdat alle combinaties al gemaakt zijn met 12
dus ik heb in de view ook dist_naar, dist_naar, 0 gedaan :)
(trouwens de eerste keer dat ik een view gebruik)
Ik stuit nou op een probleem, misschien ligt het aan mij.
Ik heb 24 profielen zelf aangemaakt om te testen
ze komen allemaal uit alle provincies, dus zou steeds 2 profielen op elke provincie moeten krijgen.
Ik doe nu als volgt
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
$listGigsQ = 'SELECT prof_name, prof_province, dist_afstand
FROM dns_profiles
LEFT JOIN v_afstanden ON (
dist_van = ' . $_POST['location'] . '
AND dist_naar = prof_province
)
ORDER BY dist_afstand ';
FROM dns_profiles
LEFT JOIN v_afstanden ON (
dist_van = ' . $_POST['location'] . '
AND dist_naar = prof_province
)
ORDER BY dist_afstand ';
en ik krijg nou alleen maar 1 record uit mijn profiel lijst (dat aansluit op provincie uit $_POST) overigens nieteens de 1e dat aansluit op de prof_province = $_POST
nee gewoon profiel id 23 uit provincie 4 :-/
alleen die zet ie er dan wel ... ik weet niet misschien wel 1000 keer in
Code (php)
1
Maximum execution time of 30 seconds
En dan heb ik nog nieteens gecontrolleerd op account level en de profielnaam erbij gezet
Och ik hoop dat ik dit snel aan de praat krijg, kheb hoofdpijn :-(
Gewijzigd op 30/03/2013 09:21:31 door Dennis WhoCares
Overigens kwam ik wel een klein probleempje tegen in mijn view. Die klopt niet helemaal. Ik had dit als afstanden tabel gedefinieerd:
Code (php)
1
2
3
4
2
3
4
van naar afstand
1 2 1
1 3 2
2 3 1
1 2 1
1 3 2
2 3 1
En in de view staat als laatste:
Code (php)
1
2
3
4
2
3
4
...
UNION
SELECT van_provincie, van_provincie, 0
FROM afstanden
UNION
SELECT van_provincie, van_provincie, 0
FROM afstanden
Omdat provincie 3 (of in zijn algemeenheid de laatste) niet als begin punt wordt genomen krijg je dus ook nooit de 0 afstand voor die provincie in je view. Als je het dus goed wilt doen zal je voor dit laatste stuk van je view de waardes uit je provincie tabel moeten halen (aannemende dat je die hebt). Bijvoorbeeld:
Code (php)
1
2
3
4
2
3
4
...
UNION
SELECT provincie, provincie, 0
FROM provincies
UNION
SELECT provincie, provincie, 0
FROM provincies
Ik krijg alle combinaties (die ik voorheen eigenlijk allemaal handmatig deed en later alle 'dubbele' eruit gehaald had om de tabel beetje klein te houden) in de view.
Ik krijg als ik de view gemaakt heb gewoon 144 resultaten, ook incl de laatste (12-12 = 0)
De php is gewoon simpel om te kijken of het daadwerkelijk werkte:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
$listProfilesQ = 'SELECT prof_name, prof_province, dist_afstand
FROM dns_profiles
LEFT JOIN v_afstanden ON (
dist_van = 12
)
ORDER BY dist_afstand ';
if(!@mysql_query($listProfilesQ)) {
echo 'Er is iets mis met de query... ' . mysql_error();
} else {
if(mysql_num_rows(mysql_query($listProfilesQ))>0) {
while($Profile = mysql_fetch_object(mysql_query($listProfilesQ))) {
echo $Profile->prof_name . ' vanuit ' . $Profile->prof_province . '<br>';
}
} else {
echo 'Geen resultaten gevonden!???';
}
}
FROM dns_profiles
LEFT JOIN v_afstanden ON (
dist_van = 12
)
ORDER BY dist_afstand ';
if(!@mysql_query($listProfilesQ)) {
echo 'Er is iets mis met de query... ' . mysql_error();
} else {
if(mysql_num_rows(mysql_query($listProfilesQ))>0) {
while($Profile = mysql_fetch_object(mysql_query($listProfilesQ))) {
echo $Profile->prof_name . ' vanuit ' . $Profile->prof_province . '<br>';
}
} else {
echo 'Geen resultaten gevonden!???';
}
}
(Kheb de zoekfunctie om zelf een provincie mee te geven weg gehaald.)
Gewijzigd op 30/03/2013 10:07:22 door Dennis WhoCares
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
<?php
if(!@mysql_query($listProfilesQ)) {
echo 'Er is iets mis met de query... ' . mysql_error();
} else {
if(mysql_num_rows(mysql_query($listProfilesQ))>0) {
while($Profile = mysql_fetch_object(mysql_query($listProfilesQ))) {
echo $Profile->prof_name . ' vanuit ' . $Profile->prof_province . '<br>';
}
} else {
echo 'Geen resultaten gevonden!???';
}
}
?>
if(!@mysql_query($listProfilesQ)) {
echo 'Er is iets mis met de query... ' . mysql_error();
} else {
if(mysql_num_rows(mysql_query($listProfilesQ))>0) {
while($Profile = mysql_fetch_object(mysql_query($listProfilesQ))) {
echo $Profile->prof_name . ' vanuit ' . $Profile->prof_province . '<br>';
}
} else {
echo 'Geen resultaten gevonden!???';
}
}
?>
Op de eerste regel voer je de query uit en check je of het gelukt is.
Op de vierde regel voer je de query uit en check je hoeveel rijen er zijn.
Op de vijfde regel voer je de query uit en fetch je het eerste record.... en weer... en weer... en weer... oneindig lang....
Elke keer dat je mysql_query aanroept voer je de query uit. Dat moet je dus echt maar 1 keer doen.
Zo zou je het moeten doen:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
<?php
$result = mysql_query($listProfilesQ);
if($result === false) {
echo 'Er is iets mis met de query... ' . mysql_error();
} else {
if(mysql_num_rows($result))>0) {
while($Profile = mysql_fetch_object($result)) {
echo $Profile->prof_name . ' vanuit ' . $Profile->prof_province . '<br>';
}
} else {
echo 'Geen resultaten gevonden!???';
}
}
?>
$result = mysql_query($listProfilesQ);
if($result === false) {
echo 'Er is iets mis met de query... ' . mysql_error();
} else {
if(mysql_num_rows($result))>0) {
while($Profile = mysql_fetch_object($result)) {
echo $Profile->prof_name . ' vanuit ' . $Profile->prof_province . '<br>';
}
} else {
echo 'Geen resultaten gevonden!???';
}
}
?>
Ik had dit snel gedaan om te testen, want ik haal t eigenlijk via een class op normaal gesproken.
Klein vraagje betreft die VIEW.
De view is niet tijdelijk he. Eenmaal aangemaakt staat ie in mn database.
Is het mogelijk om een tijdelijke view te maken?
Zodat ie alleen de 'van' provincie in de view zet die ik nodig heb op dat moment?
En dat de view daarna weer weg is ? (misschien werkt het dan sneller ? omdat ie min. 130 queries niet hoeft te lezen?
Wat je zou kunnen doen is een benchmark maken. Maak een query zoals boven met de view en maak een query die elke keer de afstand bepaalt in de query zelf (vanuit je afstanden tabel). Voer beide 100 keer of meer uit en kijk welke sneller was. Ik denk dat het niet veel uit zal maken.
Overigens, ik gaf je dit:
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
SELECT naam, provincie, afstand
FROM profielen
LEFT JOIN v_afstanden ON (
van_provincie = 12
AND naar_provincie = provincie
)
ORDER BY afstand
FROM profielen
LEFT JOIN v_afstanden ON (
van_provincie = 12
AND naar_provincie = provincie
)
ORDER BY afstand
Jij gebruikt nu dit:
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
SELECT prof_name, prof_province, dist_afstand
FROM dns_profiles
LEFT JOIN v_afstanden ON (
dist_van = 12
)
ORDER BY dist_afstand
FROM dns_profiles
LEFT JOIN v_afstanden ON (
dist_van = 12
)
ORDER BY dist_afstand
De tweede voorwaarde in de in de join is wel nodig. Laat je die weg, dan krijg je een boel meer records terug. Dan krijg je namelijk de afstand van dat profiel naar elke andere provincie.
Gewijzigd op 30/03/2013 11:52:57 door Erwin H
Over die view, die hoef ik alleen maar in database erin te zetten toch? Dus hoef hem niet steeds in php uit te voeren.
Nog klein dingetje :-$ Omdat het allemaal zo in elkaar zit.
Ik probeer nu dit:
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
SELECT dns_users.user_profile,dns_profiles.prof_name,dns_profiles.prof_gender, dist_afstand
FROM dns_users,dns_profiles
LEFT JOIN v_afstanden ON (
dist_van = ' . $_POST['location'] . '
dist_naar = dns_profiles.prof_province
)
ORDER BY dist_afstand,dns_profiles.prof_province
FROM dns_users,dns_profiles
LEFT JOIN v_afstanden ON (
dist_van = ' . $_POST['location'] . '
dist_naar = dns_profiles.prof_province
)
ORDER BY dist_afstand,dns_profiles.prof_province
Maar nu krijg ik de foutmelding
Unknown column 'dns_profiles.prof_province' in 'on clause'
Moet ik in de JOIN ook FROM dns_profiles doen ?
*Typo: tis geen joint, als php joint gaat roken dan zijn we helemaal ver zoek xD
___________________________
Ik heb nou alles in WHERE en AND gezet, incl de v_afstanden.dist_van = ' . $location . ' AND v_afstanden.dist_naar = dns_profiles.prof_province
Nou werkt het wel
AAAAAAHHHHH Ik ben zoooo blij danku danku danku danku Erwin!!!
Gewijzigd op 30/03/2013 12:13:10 door Dennis WhoCares
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
SELECT dns_users.user_profile,dns_profiles.prof_name,dns_profiles.prof_gender, dist_afstand
FROM dns_users
LEFT JOIN dns_profiles ON ....
LEFT JOIN v_afstanden ON (
dist_van = ' . $_POST['location'] . '
dist_naar = dns_profiles.prof_province
)
ORDER BY dist_afstand,dns_profiles.prof_province
FROM dns_users
LEFT JOIN dns_profiles ON ....
LEFT JOIN v_afstanden ON (
dist_van = ' . $_POST['location'] . '
dist_naar = dns_profiles.prof_province
)
ORDER BY dist_afstand,dns_profiles.prof_province
(vul de ON clause zelf in, want ik weet niet welke kolommen daarvoor nodig zijn)
Erwin H op 30/03/2013 12:12:41:
Afgezien van een mogelijk tikfout (provincie wellicht?), zou het kunnen liggen aan het mixen van impliciete en expliciete joins. Dit kan je beter zoveel mogelijk vermijden. Ik zou er dus dit van maken:
(vul de ON clause zelf in, want ik weet niet welke kolommen daarvoor nodig zijn)
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
SELECT dns_users.user_profile,dns_profiles.prof_name,dns_profiles.prof_gender, dist_afstand
FROM dns_users
LEFT JOIN dns_profiles ON ....
LEFT JOIN v_afstanden ON (
dist_van = ' . $_POST['location'] . '
dist_naar = dns_profiles.prof_province
)
ORDER BY dist_afstand,dns_profiles.prof_province
FROM dns_users
LEFT JOIN dns_profiles ON ....
LEFT JOIN v_afstanden ON (
dist_van = ' . $_POST['location'] . '
dist_naar = dns_profiles.prof_province
)
ORDER BY dist_afstand,dns_profiles.prof_province
(vul de ON clause zelf in, want ik weet niet welke kolommen daarvoor nodig zijn)
Nee was geen tikfout, in profielen had ik het in het 'engels' opgeslagen
Owwww wachtttt dus ipv alle where en and's achter elkaar te plakken, kunnen we dat met LEFT JOIN doen? (dan hoef ik ook de tabel niet bij FROM te plaatsen?)
Sorry dit begint nou allemaal acht voorbij mijn huidige kennis te gaan :D
Ik krijg het niet aan de praat met LEFT JOIN, INNER JOIN etc..
Toch bedankt de hoofdzaak is opgelost :D
(Ik ga van de week wel is ff kijken met die JOIN optie..)
Gewijzigd op 30/03/2013 12:33:58 door Dennis WhoCares