[php/mysql] 'dubbele' adressen samenvoegen

[StephanVierkant]

Ik ben bezig met een ledenadministratie van een vereniging. Alle adressen zitten in een database, leden kunnen zelf info aanpassen, etc. Werkt allemaal perfect. Maar nu heeft de redactie van het clubblad gevraagd om een lijst met alle adressen. Als er meerdere leden op één adres wonen (de hele familie is soms lid), moet er maar één keer zo'n adres tevoorschijn komen op het stickervel.

Ik begin zo:

PHP:

$stickers = array();
$query = mysql_query("SELECT * FROM `leden` ORDER BY achternaam");
while($list = mysql_fetch_object($query))
{
    //kijken of adres al in de array staat, anders in array plaatsen
    if (!in_array($list->adres, $stickers))
    {
        array_push($stickers, $list->adres);
    }
}
foreach ($stickers as $key => $value)
{
    //namen bij het adres vinden
    $query2 = mysql_query("SELECT * FROM leden WHERE adres = '$value'");

Nu heb ik dus geen enkel adres dubbel. Tot zover prima. Indien er in de laatste query maar 1 lid uit komt, is het gewoon $voornaam $achternaam uitpoepen. Echter, als er meerdere leden zijn op dat adres moet er komen te staan 'Familie $achternaam'. Ook dat is geen probleem.

PHP:

if (mysql_num_rows($query2) == 1)
{
    echo "$voornaam $achternaam";
}
else //er zijn hier meerdere personen op het adres
{
    //tja, wat moet hier?
}
}

Het ingewikkelde is dat er meerdere achternamen kunnen zijn op één adres. De vader en kinderen heten Jansen en de moeder De Vries. Ik wil dus zorgen dat er 'Fam. Jansen' komt te staan, en niet 'Fam. De Vries'. Iemand die mij op weg kan helpen?

[P.O. Box]

tip: ga je eens verdiepen in de GROUP BY functionaliteit

P&W FAQ - SQL

[ Voor 37% gewijzigd door P.O. Box op 16-10-2007 12:09 ]

[YakuzA]

Ook met een group by loopt hij tegen hetzelfde probleem aan.

Het Fam. Jansen vs. Fam. De Vries verhaal kun je een aanpak verzinnen dat je kijkt wie er allemaal wonen, en de meest voorkomende pakt. Als er niet iets van voorkeursnaam om geaddresseerd te worden in de Database staan, lijkt het me niet makkelijk (of onmogelijk) om te weten waar je het naar moet addresseren.
Hier kan je slechts naar gissen.

[ Voor 6% gewijzigd door YakuzA op 16-10-2007 12:15 ]

[Janoz]

'Group by' gaat hier absoluut niet helpen. Het eerste probleem is dat de topicstarter voor zichzelf nog geen duidelijke regel gesteld heeft wat de familie naam wordt.

Wordt het de achternaam die het meeste voorkomt? Wat gebeurt er dan wanneer de kinderen de achternaam van de moeder hebben? Is dat wenselijk?
Wat gebeurt er wanneer er meerdere leden in hetzelfde studentenhuis wonen?

En aangezien leden zelf hun gegevens aan kunnen passen:
Wat gebeurt er met verschillende schrijfwijzen van hetzelfde adres (bedoeld en onbedoeld)?
Worden er straks 2 clubbladen afgeleverd waarbij de ene geadresseerd is naar de Boslaan en de ander naar de Bosln?

Ikzelf zou de entiteit 'gezin' toevoegen aan het leden administratie systeem. Daarin kun je de aanhef opnemen en deze kun je aan adres koppelen. Verder kun je dit gebruiken om de facturering te vergemakkelijken (1 rekening per gezin ipv per lid) en eventueel later zelfs bepaalde gezinskortingen toepassen.

[StephanVierkant]

De meest voorkomende achternaam moet de familienaam worden. Van het geval van studentenhuizen, kinderen met de naam van de moeder, etc. ga ik niet uit. Je hoeft daar dus geen rekening mee te houden.

Verschillende schrijfwijzen van hetzelfde adres zijn uitgesloten, aangezien er een postcode-aanvulling is. Postcode en huisnummer kan natuurlijk lastig fout worden geschreven. (dmv een javascriptje wordt de spatie al uit de postcode gehaald.

[P.O. Box]

dat zou ik dan ook nog maar even serverside checken...

verder denk ik dat je eventueel met een group by nog wel een aardig eindje kunt komen.. .zeker als je het zo in stapjes doet zoals jij het doet...
doe bijv. een selectie op postcode, huisnummer, count(*) en je weet al op welke adressen meerdere personen wonen....doe een selectie op postcode, huisnummer, naam, count(*) en je weet welke naam het meest voorkomt...

pseudo beginnetje: zoiets?

code:

selecteer postcode, huisnummer, naam, count(*) order by postcode, huisnummer, count(*) desc
loop door de resultaten... als postcode, huisnummer combinatie hetzelfde is als die van de vorige, dan niet weergeven, anders wel...

[ Voor 24% gewijzigd door P.O. Box op 16-10-2007 13:43 ]

[StephanVierkant]

Edwardvb, leuk dat je meedenkt, maar volgens mij heb je oplossing voor een probleem dat ik zelf ook al opgelost heb.

[Rainmaker1987]

Even snel uit mijn hoofd kan je het op de volgende manier doen:

SELECT count( achternaam ) AS aantal, achternaam
FROM `tabel`
GROUP BY achternaam
ORDER BY `aantal` DESC
LIMIT 0 , 1

Hierbij krijg je de naam die het meest aantal keren voorkomt. Een WHERE erbij zetten met het adres en je bent klaar

[ Voor 3% gewijzigd door Rainmaker1987 op 16-10-2007 14:25 ]

[Verwijderd]

Lijkt mij dat je dit beter andersom kunt benaderen, dus kijken naar adres en niet naar naam. Naam zegt helemaal niets; mijn vriendin en ik wonen ook op hetzelfde adres maar we hebben een andere achternaam.

Ik zou een query doen op uniek adres (SELECT DISTINCT adres ....), volgens mij kom je daar een heel eind mee.

Daarnaast wil ik nog even opmerken dat ik de constructie in je OP niet zo geweldig vind. Je haalt eerst alle namen op en gaat dan per naam nog eens een query uitvoeren om de adresgegevens op te halen! Hoeveel queries worden dat wel niet? Dat kan vast wel in 1 query met een JOIN.

[Verwijderd]

Even denken, mijn familie heeft ongeveer 50 oom's, opa's, neefjes, kleinkinderen en achterkleinkinderen op de voetbal met elk de achternaam ******.
Hwt lijkt me dat je beter kunt kijken naar postcode - huisnummer combinaties. Binnen deze combinaties op achternaam.

[mocean]

Voor post verzending is er bij TNT zoiets als een KIX code. Die kan je printen op adreslabels maar ook gebruiken voor ontdubbelingen.

De KIX code is gedefinieerd als POSTCODE + HUISNUMMER. En die is natuurlijk uniek per adres. Als je bijv. een extra veld aanmaakt voor de KIX code kan je daar gemakkelijk op ontdubbelen.

En als extratje, TNT geeft ook korting als je de KIX code meeprint (vanaf bepaalde aantallen), zie:
http://www.tntpost.nl/zak...loads/kIX_code/index.aspx
Ze hebben ook software voor het maken van KIX codes uit samengestelde adresvelden (als straat + huisnummer in 1 veld zit).

Zie ook bijv:
http://www.tntpost.nl/zak...rankeren/esort/index.aspx

[ Voor 7% gewijzigd door mocean op 16-10-2007 16:19 ]

[Verwijderd]

mocean schreef op dinsdag 16 oktober 2007 @ 16:13:
De KIX code is gedefinieerd als POSTCODE + HUISNUMMER. En die is natuurlijk uniek per adres. Als je bijv. een extra veld aanmaakt voor de KIX code kan je daar gemakkelijk op ontdubbelen.

Daar hoef je geen extra veld voor aan te maken, je kunt ze on-the-fly samen laten voegen door MySQL (m.b.v. CONCAT).

[mocean]

Verwijderd schreef op dinsdag 16 oktober 2007 @ 16:27:
[...]

Daar hoef je geen extra veld voor aan te maken, je kunt ze on-the-fly samen laten voegen door MySQL (m.b.v. CONCAT).

Ja, Behalve als straat + huisnr in 1 veld zitten.
En de speciale sortering die TNT wil bij bulk aanlevering moet weer met hun software pakketje.

[Voutloos]

Straat + huisnr in 1 veld is sowieso niet ideaal. Maar dit is allemaal het probleem van de ts niet, want die geeft aan adresinformatie op orde te hebben, maar vraagt zich gewoon af welke naam hij moet gebruiken als er meerdere personen op 1 adres bekend zijn.

[P.O. Box]

en er is al aangegeven hoe hij de meest voorkomende naam kan vinden... dus lijkt me dat de TS weer verder kan....

[StephanVierkant]

Rainmaker1987 schreef op dinsdag 16 oktober 2007 @ 14:24:
Even snel uit mijn hoofd kan je het op de volgende manier doen:

SELECT count( achternaam ) AS aantal, achternaam
FROM `tabel`
GROUP BY achternaam
ORDER BY `aantal` DESC
LIMIT 0 , 1

Hierbij krijg je de naam die het meest aantal keren voorkomt. Een WHERE erbij zetten met het adres en je bent klaar

PHP:

$query = mysql_query("SELECT count( achternaam ) AS aantal, achternaam, adres, naam FROM `leden` GROUP BY adres");

while($list = mysql_fetch_object($query))
{
    if($list->aantal == 1)
    {
        echo $voornaam . " ";
        echo $list->achternaam;
    }
    else
    {
        echo "Familie " . $list->achternaam;
    }
}

Zo werkt 'ie volgens mij prima.

[Voutloos]

Je pakt nu een random achternaam om als familienaam te gebruiken. Zie P&W FAQ - SQL en de note over mysql.
Velden selecteren welke niet in de group by clause staan, of niet het resultaat van een aggregate function zijn is bad practice. mysql staat het toe, maar je krijgt in weze random data en dat kan nooit de bedoeling zijn bij een db.

offtopic:
Overigens is $query een slechte variabelenaam, wnat het bevat niet de query, maar het resultaat, de resource cq. resultset. Zelfde geldt voor $list, want die variabele bevat geen lijst, maar slechts een row.

[ Voor 23% gewijzigd door Voutloos op 17-10-2007 13:20 ]

[Janoz]

In dit geval valt het mee. Het klopt wel wat je zegt, maar in dit geval slaat het niet op achternaam, maar op voornaam. Voornaam wordt echter alleen gebruikt wanneer er slechts 1 resultaat is. De gebrekkige implementatie van MySQL gaat in dit geval dus geen problemen opleveren.

[stappel_]

Het hele idee van meerdere personen op 1 adres en dan "blind" samenvoegen is totaal ongelovelijk.

Wat doe je met een zorgflat of bejaardentehuis? Die mensen hebben allemaal dezelfde postcode en huisnummer maar hebben totaal geen band met elkaar die jij zoekt. En als lid van de vereniging willen ze toch elk hun blaadje ontvangen. (sommige tehuizen gebruiken nog kamernr als toevoeging, maar niet allemaal).

Je moet terug naar de opdrachtgever (die redactie) met dit probleem, want zij verzinnen iets zonder echt door te denken (waarschijnlijk om verzendkosten te samen). Laat ze er maar eerst eens zelf over nadenken. Nooit zomaar zelf aannames maken.

[Voutloos]

Janoz schreef op woensdag 17 oktober 2007 @ 13:25:
Het klopt wel wat je zegt, maar in dit geval slaat het niet op achternaam, maar op voornaam.

In de ts staat iets anders, en je hebt zelf ook al over meerdere achternamen gehad:

Stephan4kant schreef op dinsdag 16 oktober 2007 @ 12:02:
Het ingewikkelde is dat er meerdere achternamen kunnen zijn op één adres. De vader en kinderen heten Jansen en de moeder De Vries. Ik wil dus zorgen dat er 'Fam. Jansen' komt te staan, en niet 'Fam. De Vries'. Iemand die mij op weg kan helpen?

Janoz schreef op dinsdag 16 oktober 2007 @ 12:16:
Wordt het de achternaam die het meeste voorkomt?

[StephanVierkant]

stappel_ schreef op woensdag 17 oktober 2007 @ 13:38:Wat doe je met een zorgflat of bejaardentehuis? Die mensen hebben allemaal dezelfde postcode en huisnummer maar hebben totaal geen band met elkaar die jij zoekt. En als lid van de vereniging willen ze toch elk hun blaadje ontvangen. (sommige tehuizen gebruiken nog kamernr als toevoeging, maar niet allemaal).

Dat soort mensen is niet lid van een hockeyclub

[Janoz]

Nu ik het scherm nog wat verder opzij schuif zie ik inderdaad dat Voutloos gelijk heeft. Ik had niet gezien dat achternaam niet opgenomen was bij de group by. Nu ik er nog langer over nadenk gaat het zelfs helemaal niet meer werken.

Ik blijf nog steeds bij mijn originele idee waarbij gezin gewoon als extra entiteit toegevoegd moet worden.

[dik_voormekaar]

Je hebt ook nog gezinnen waarbij de hockeyende moeder samenwoont met haar nieuwe vriend samen met de 2 kinderen van haar ex.
De moeder heeft naam1, de vriend naam2, de 2 kinderen naam3.
De naam van de kinderen wordt dan de familienaam...

[jvdmeer]

Stephan4kant schreef op woensdag 17 oktober 2007 @ 13:48:
[...]
Dat soort mensen is niet lid van een hockeyclub

offtopic:
Je zou eens moeten weten hoeveel van die mensen nog lid blijven. Er zijn legio verenigingen die draaien op de giften van rijke niet-spelende leden.

Hiermee bedoel ik dus dat dit wel een reëele mogelijkheid is.

[ Voor 9% gewijzigd door jvdmeer op 18-10-2007 11:11 . Reden: spelfout en afgemaakt. ]

[Verwijderd]

Punt is, of je het huidige adressenbestand wilt behouden, of dat je het wilt "corrigeren".
Ik zag al het begrip entiteit voorbij vliegen.
Dat zal waarschijnlijk inhouden, dat je wat velden moet verplaatsen naar, zeg maar de andere tabel

offtopic:
(als ik fout zit, corrigeer me maar)

Uit ervaring kan ik vertellen, dat het rete-lastig is om per lid het adres op te slaan en er dan achter te komen, dat de hele familie op hetzelfde adres woont (logisch).
De workarounds, die daaruit zijn gekomen zijn echt ongelofelijk. (Je wilt het liever in 1 keer goed doen).

We hebben nu een tabel Families met daarnaast een tabel "Leden per Familie"
Hierin staat ieder lid en bij welke familie hij hoort. In de familie tabel hebben we behalve achternaam ook de familienaam. Tevens is er 1 persoon van de familie het hoofdfamilielid. Daar worden dus de brieven naar toe gestuurd.

Zorg dat de familienaam uniek is! Je hebt heel veel families "Janssen" en dan kan je bij de familienaam "Janssen1" of "Janssen3672AA13" (Kixcode achter de achternaam dus) maken.
Ik zou dan persoonlijk de familie aan het adres koppelen. Straat los van huisnummer en bij de weergave het weer aan elkaar plakken.