[PHP/SQL] A.d.h.v. koppeltabel vrienden bepalen

[radem205]

Hey,

Aan de hand van een koppeltabel wil ik leden op een website de mogelijkheid geven om bevriend met elkaar te worden.

Koppeltabel:

id	vrienden_in	vrienden_uit	actief
1	21313	15321	1
2	26341	21313	1

Zoals je ziet worden de id's uit de tabel "leden" met elkaar gekoppeld middels bovenstaande tabel. Nu wil ik van een bepaald lid (in dit geval met id '21313') de vrienden tonen.
Nu is het zo dat in deze opzet het id van het lid (21313) zowel in de kolom 'vrienden_in' als in de kolom vrienden_uit' kan voorkomen.

Nu weet ik niet goed hoe ik juist steeds het andere id middels een query uit de database kan halen. Ik heb het geprobeerd met onderstaande query (en varianten hierop), maar het wil niet echt lukken.

PHP:

<?
$sql_vrienden = mysql_query("
        SELECT
            COUNT(vrienden.id) AS aantal,
            vrienden.vriend_in,
            vrienden.vriend_uit,
            leden.voornaam AS vriendvoornaam,
            leden.gebruikersnaam AS vriendachternaam,
            leden.id AS vriendid,
            leden.foto AS vriendfoto
        FROM
            vrienden
        LEFT JOIN
            leden
        ON
            vrienden.vriend_in = leden.id OR vrienden.vriend_uit = leden.id
        WHERE
            (
            vrienden.vriend_in = '".$f_lid['id']."'
        OR
            vrienden.vriend_uit = '".$f_lid['id']."'
            )
        AND
            vrienden.actief = 1
        ORDER BY
            leden.gebruikersnaam ASC,
            leden.voornaam ASC  
    ") or die(mysql_error());
    
    while($f_vrienden = mysql_fetch_assoc($sql_vrienden)) {
        
        echo $f_vrienden['vriendvoornaam'].'<br />';
        
    }
?>

Weet iemand hoe ik in 1 query de bevriende id uit de koppeltabel kan halen?

Alvast bedankt!

[pedorus]

Al eens gedacht aan UNION?

[RobIII]

Allereerst: Vanwaar nog dat "id" veld en de "actief"? De combinatie in/uit maakt het record uniek, dus het ID kun je weglaten en als actief 0 zou zijn kun je natuurlijk ook gewoon 't record verwijderen.

Verder;

SQL:

select pi.naam as vriendA, pu.naam as vriendB, ..., ...
from koppeltabel as kt
inner join personen as pi on kt.vrienden_in = pi.id
inner join personen as pu on kr.vrienden_uit = pu.id
where kt.vrienden_in = 21313 or kt.vrienden_uit = 21313

Zoiets; uit de losse pols.

[ Voor 8% gewijzigd door RobIII op 17-11-2009 22:14 ]

[radem205]

Het veld 'Id' heb ik toegevoegd om ook de laatst aangemaakte vrienden van een lid te kunnen weergeven....
En het veld 'actief' is bedoeld wanneer de ander de vriendschap heeft geaccepteerd. Wanneer een persoon de uitnodiging niet accepteert dan wordt de record verwijderd, maar zo lang hij het niet geaccepteerd heeft blijft actief op 0 (om zodoende het onderscheid te kunnen maken).

[HuHu]

Maak voor afwachtende vriendschappen dan een tabel wannabee_vrienden.

[radem205]

RobIII schreef op dinsdag 17 november 2009 @ 22:12:
Allereerst: Vanwaar nog dat "id" veld en de "actief"? De combinatie in/uit maakt het record uniek, dus het ID kun je weglaten en als actief 0 zou zijn kun je natuurlijk ook gewoon 't record verwijderen.

Verder;

SQL:

select pi.naam as vriendA, pu.naam as vriendB, ..., ...
from koppeltabel as kt
inner join personen as pi on kt.vrienden_in = pi.id
inner join personen as pu on kr.vrienden_uit = pu.id
where kt.vrienden_in = 21313 or kt.vrienden_uit = 21313

Zoiets; uit de losse pols.

Volgens mij moet ik middels deze query nog steeds in php danwel uit 'vrienden_in' danwel uit 'vrienden_uit' de id halen van de vriend toch? Is het niet mogelijk om in 1 query danwel uit 'vrienden_in' danwel uit 'vrienden_uit' de id van de 'tegenpartij' te halen en dan met een join de naam van deze persoon te voorschijn halen? Want anders moet ik in php nog heel moeilijk gaan doen om de juiste id te filteren en met een extra query de naam van deze persoon weer te geven.

Ik hoop dat het duidelijk is wat ik bedoel, anders hoor ik dat graag!

[RobIII]

SQL:

select case when pi.id = 21313 then pu.id else pi.id end as tegenpartij_id
...

[pedorus]

radem205 schreef op dinsdag 17 november 2009 @ 22:45:
[...]


Volgens mij moet ik middels deze query nog steeds in php danwel uit 'vrienden_in' danwel uit 'vrienden_uit' de id halen van de vriend toch? Is het niet mogelijk om in 1 query danwel uit 'vrienden_in' danwel uit 'vrienden_uit' de id van de 'tegenpartij' te halen en dan met een join de naam van deze persoon te voorschijn halen?

Ja, dat kan dus met een union....

SQL:

create view vrienden_van as 
        select vrienden_in as van, vrienden_uit as vriend from vrienden 
    union all 
        select vrienden_uit, vrienden_in from vrienden;

[radem205]

RobIII schreef op dinsdag 17 november 2009 @ 22:50:

SQL:

select case when pi.id = 21313 then pu.id else pi.id end as tegenpartij_id
...

Helemaal super, dit is precies wat ik bedoel en het werkt! Thanks!!

Edit: Maar dit in combinatie met een join om de naam uit de tabel 'leden' te halen gaat niet echt lukken volgens mij he? Want dan moet ik tegenpartij_id refereren aan de tabel 'leden', maar iets uit de select halen en gebruiken in de query gaat moeilijk...

[ Voor 31% gewijzigd door radem205 op 17-11-2009 23:05 ]

[RobIII]

radem205 schreef op dinsdag 17 november 2009 @ 22:59:
Edit: Maar dit in combinatie met een join om de naam uit de tabel 'leden' te halen gaat niet echt lukken volgens mij he?

Waarom niet? En heb je al eens gewoon een poging gedaan? Mag ik daar de query even van zien dan?

SQL:

select case when pi.id = 21313 then pu.id else pi.id end as tegenpartij_id,
    case when pi.id = 21313 then pu.naam else pi.naam end as tegenpartij_naam
    ...

Maar in het geval van veel velden kan je query nogal eens lomp worden en is een view wel even het overwegen waard

[ Voor 31% gewijzigd door RobIII op 17-11-2009 23:06 ]

[radem205]

code:

SELECT
            case when vrienden.vriend_in = '".$f_lid['id']."' then vrienden.vriend_uit else vrienden.vriend_in end as tegenpartij_id, 
            vrienden.vriend_in,
            vrienden.vriend_uit,
            leden.voornaam AS ledenvoornaam
        FROM
            vrienden
        LEFT JOIN
            leden
        ON
            tegenpartij_id = leden.id
        WHERE
            (
            vrienden.vriend_in = '".$f_lid['id']."'
        OR
            vrienden.vriend_uit = '".$f_lid['id']."'
            )
        AND
            vrienden.actief = 1

[RobIII]

Waarom staan je id's tussen quotes? Het zijn toch, mag ik hopen, gewoon integers en geen strings? Verder gaf ik in mijn vorige post (na een edit) nog een hint...

[ Voor 24% gewijzigd door RobIII op 17-11-2009 23:07 ]

[radem205]

Zijn integers inderdaad.

Het zijn maar een paar velden die uit de query gehaald moeten worden. Maar in jouw toevoeging op je eigen post ga je er vanuit dat de naam van een lid in de tabel 'vrienden' staat, maar die staat dus in de tabel 'leden'.

[ Voor 172% gewijzigd door radem205 op 17-11-2009 23:11 ]

[RobIII]

radem205 schreef op dinsdag 17 november 2009 @ 23:08:
Zijn integers inderdaad.

Vanwaar die quotes dan?
Je hebt nu:

code:

... WHERE ( vrienden.vriend_in = '".$f_lid['id']."' OR ...

Wat je dus feitelijk dient te schrijven als:

code:

... WHERE ( vrienden.vriend_in = ".$f_lid['id']." OR ...

(los van het feit dat php tussen "-quotes zelf al variabelen vervangt en dat hele geneuzel)

radem205 schreef op dinsdag 17 november 2009 @ 23:08:
Maar in jouw toevoeging op je eigen post ga je er vanuit dat de naam van een lid in de tabel 'vrienden' staat

Nee pi en pu zijn in mijn voorbeeld gewoon aliassen van "personen" (in jouw geval 'leden').

[ Voor 63% gewijzigd door RobIII op 17-11-2009 23:16 ]

[radem205]

RobIII schreef op dinsdag 17 november 2009 @ 23:13:
[...]

Vanwaar die quotes dan?
Je hebt nu:

code:

... WHERE ( vrienden.vriend_in = '".$f_lid['id']."' OR ...

Wat je dus feitelijk dient te schrijven als:

code:

... WHERE ( vrienden.vriend_in = ".$f_lid['id']." OR ...

(los van het feit dat php tussen "-quotes zelf al variabelen vervangt en dat hele geneuzel)

[...]

Nee pi en pu zijn in mijn voorbeeld gewoon aliassen van "personen" (in jouw geval 'leden').

Ok, sorry. Wat betreft de integers, dit heb ik al opgelost. Ik denk dat ik er nu wel uit kan komen met jouw voorbeelden en adviezen. Super bedankt voor je tijd!

[4VAlien]

Als je het altijd het laagste id in de eerste kolom opslaat en het hogere id in de tweede dan heb je maar 1 regel per vriendschap nodig.

[mocean]

4VAlien schreef op woensdag 18 november 2009 @ 15:44:
Als je het altijd het laagste id in de eerste kolom opslaat en het hogere id in de tweede dan heb je maar 1 regel per vriendschap nodig.

Maar dan is niet duidelijk wie de vriendschap geïnitieerd heeft en dat lijkt me nodig voor het 'actief' worden.

[4VAlien]

Je kan een extra 'actief' veld toevoegen als je dat per se wilt weten, gebruikt nog steeds maar 1 record en 4 ipv 6 velden. Je kan ook bij het aanmaken van de vriendschap de bevestiging versturen en zodra deze bevestigd wordt de vriendschap op active zetten. Of zie de opmerking van Huhu (aparte tabel met 'vriendschappen in afwachting' )

[ Voor 13% gewijzigd door 4VAlien op 18-11-2009 17:57 ]

[radem205]

Er wordt nu ook maar 1 record gebruikt per vriendschap die bestaat uit 4 velden (id, vriend_in, vriend_uit, actief). Het is inmiddels allemaal gelukt, bedankt voor de reacties!!

[radem205]

Nu heb ik toch nog even een probleem. Tijdens het zoeken binnen de profielen is het van belang om te kijken of de leden die matchen met de trefwoorden bevriend zijn met degene die zoekt. Dit is omdat wanneer iemand op woonplaats zoekt en een lid heeft aangegeven dat niemand haar woonplaats mag zien, hierop bij dit lid dus niet gezocht mag worden.

Nu wil ik alles het liefst in 1 query zetten, aangezien het anders te omslachtig wordt.

PHP:

<?

$sql = mysql_query("
SELECT 
leden.id, 
leden.profielnaam 
FROM 
leden 
WHERE 
(
leden.woonplaats LIKE '%".mysql_escape_string($_POST['woonplaats'])."%' 
AND 
(
leden.woonplaatskeuze = 1 OR (leden.woonplaatskeuze = 2 AND // Nu moet hier dus gecontroleerd worden of iemand bevriend is) )
)
");

Nu is het dus de bedoeling dat in bovenstaande query wordt gecontroleerd aan de hand van de koppeltabel (zie bovenstaande posts) of een lid bevriend is of niet.

Zit ik op de goede weg, en kan iemand mij verder helpen om in 1 query dit te kunnen realiseren? Volledige codes zijn niet nodig, maar ik heb geen idee waar ik op moet zoeken.

Bedankt!

[radem205]

Nou, dan toch maar weer een andere vraag. Ik vind het zonde om een nieuw topic te moeten aanmaken hierover, dus bij deze:

Ik heb de volgende mysql query:

PHP:

$sql = mysql_query("SELECT 
            leden.id,
            leden.profielnaam,
            leden.woonplaats,
            leden.woonplaatskeuze,
            leden.foto,
            FLOOR(DATEDIFF(CURRENT_DATE(),leden.leeftijd )/365) AS leeftijd,
            leden.leeftijdkeuze,
            COUNT(SELECT id FROM vrienden WHERE (vriend_in = ".$f_lid['id']." AND vriend_uit = ".$f['id'].") OR (vriend_in = ".$f['id']." AND vriend_uit = ".$f_lid['id'].") LIMIT 0,1) AS vriend
        FROM
            leden
        LEFT JOIN
            vrienden AS vriendeni
        ON
            leden.id = vriendeni.vriend_in
        LEFT JOIN
            vrienden AS vriendenu
        ON
            leden.id = vriendenu.vriend_uit");

Achter bovenstaande code zit nog een where statement, maar dat is nu niet relevant.

Nu werkt bovenstaande query niet, aangezien het COUNT() gedeelte in de SELECT niet goed is, maar ik zie niet wat ik fout doe. Ik wil dus aan de hand van deze count controleren of het geselecteerde lid bevriend is met mij (als ingelogde gebruiker).

In de where clause wil ik, in combinatie met andere controles, elke keer controleren of diegene bevriend is met mij. Is het mogelijk om de uitkomst van de COUNT(), binnen de SELECT statement, te gebruiken in de WHERE clause (bijvoorbeeld: WHERE vriend = 1)?

Alvast bedankt voor je reactie!

[Gomez12]

Vervang je count(select ... ) eens door een normale subquery

Op dit moment zie ik nog niet eens wat je probeert te bereiken, je telt het aantal id's van vrienden waarop je een limit van 1 zet?

En kijk eens naar date/time functies, je manier om een leeftijd is niet bepaald elegant te noemen. Gebruik gewoon van het verschil enkel het jaar door formatting...

[!GN!T!ON]

is die count dan niet een subquery en moet er dan niet een set ( en ) bij?

code:

COUNT((SELECT id FROM vrienden WHERE (vriend_in = ".$f_lid['id']." AND vriend_uit = ".$f['id'].") OR (vriend_in = ".$f['id']." AND vriend_uit = ".$f_lid['id'].") LIMIT 0,1)) AS vriend

overigens een foutmelding o.i.d. zou ook handig zijn!

[Gomez12]

Btw.

radem205 schreef op dinsdag 24 november 2009 @ 22:51:
In de where clause wil ik, in combinatie met andere controles, elke keer controleren of diegene bevriend is met mij. Is het mogelijk om de uitkomst van de COUNT(), binnen de SELECT statement, te gebruiken in de WHERE clause (bijvoorbeeld: WHERE vriend = 1)?

Op zich is het kopieren van de inhoud van je count in je where mogelijk.

Maar bedoel je niet gewoon een inner join hiervoor?
Ipv count kan je ook gewoon left/right joinen en dan : where !isnull(id)

Ik denk dat ik je huidige constructie zie ( volgens mij zou hij een 1 moeten geven in geval van vriend en een 0 in geval van geen vriend ) maar als je daarna weer die 1 gaat gebruiken in je where lijkt het mij een heel moeilijke constructie die enkel een inner join moet voorstellen...

[pedorus]

Gomez12 schreef op dinsdag 24 november 2009 @ 22:57:
Vervang je count(select ... ) eens door een normale subquery

Op dit moment zie ik nog niet eens wat je probeert te bereiken, je telt het aantal id's van vrienden waarop je een limit van 1 zet?

Ik denk dat dit een nieuwe manier is van EXISTS. Maar voor de rest denk ik dat dit project zonder een simpele VIEW die ik al heb gegeven een soort les in zelfpijniging wordt.

Ik zou het dus ongeveer zo doen:

SQL:

select l.id, l.profielnaam, l.woonplaats, l.woonplaatskeuze, l.foto, l.leeftijd,
       l.leeftijdkeuze, not v.van is null as is_vriend
    from leden as l left join vrienden_van as v on (v.van=? and l.id=v.vriend) 
    where l.id=?;

(met de 2 vraagtekens in te vullen bij een prepared statement, helaas is dit niet ondersteund met mysql_*-functies, maar wel met pdo of mysqli)