4 tabellen combineren en resultaat weergeven

Oke.
Ik heb een paar vragen:
- geen user in landing_views: Wat is dan de waarde van user_id in deze tabel? Is dat NULL?
Mogelijk los je dit al op door regel 19 JOIN te vervangen door LEFT OUTER JOIN.


- Wat is de status van een anonieme user? Als je voorgaande verandert wordt status voor een anonieme user ook NULL.
En die vergelijken we nog nier op regel 3-7 en hebben we niet in de CROSS JOIN

De waarde van user_id in de tabel landingviews is dan 0 (niet NULL).
Er hangt op dit moment dus ook geen contact aan want het is nog niet gekend. Door een download te doen zet ik een cookie die de user_id gelijk gaat stellen aan de net ge-inserte ID bij de contacten. Zo krijgen we dus de user_id in de downloads. Gaat iemand daarachter naar een andere landingspagina krijgt die terug een landingview en dan heeft die dus wel een user_id.

Een anonieme user heeft dus geen cont_status (om op je 2de vraag te antwoorden).
Zoals je kan zien wil ik in mijn html tabel hier ook een aantal (via een SUM) op 'plakken' (voor de waarde waar nu 'x' staat).
Hoe pak ik dit het best aan?

Een rij aan contacten toevoegen met id=0 en status='anoniem', mocht id een auto-increment zijn dan eerst toevoegen en met phpMyAdmin de id wijzigen naar 0.
De subquery van landings_view geeft dan ook een regel terug voor status anoniem.
Voor regel 3 kun je dan
SUM(CASE WHEN (lv.cont_status='anoniem') THEN lv.aantal ELSE 0 END) AS SumLvAnoniem,
toevoegen.

Dit is het eenvoudigst, wil je dit niet dan is er misschien nog wel een mogelijkheid maar die is iets ingewikkelder.

@Jan: ik heb de html tabel nog wat geoptimaliseerd en de query aangepast. Het schijnt te lukken. Ik moet wel de resultaten nog controleren of alles mooi geteld is. Bedankt tot nu toe.
Ik kom er op terug na controle. Ik ben ook bezig met een bron toe te voegen hoe mensen op een bepaalde landingspagina zijn terechtgekomen. Hiervan zou ik ook een tabel willen maken of de 2 samenvoegen. Daar kom ik later op terug.

[size=xsmall]Toevoeging op 12/12/2015 19:36:19:[/size]

Ken jij al die mogelijkheden in de queries uit je hoofd? Dit is toch een heel ingewikkelde volgens mij. Of heb je daar een bepaald progje voor?

Mooi dat het lukt en graag gedaan.
Ik ken deze constructies wel uit het hoofd, maar dat is dan wel na heel veel jaren ervaring met allerlei varianten van sql. En deze constructies zijn niet ongebruikelijk.

Ik ben nu ook weg, misschien treffen we elkaar morgen nog.

Ik ben al even aan het zoeken naar hoe ik de bron kan mee opnemen in de query. Het zit zo dat de tabel met landingviews en de tabel met de downloads beiden een bronvermelding kunnen hebben maar dat is niet altijd zo. Buiten de SUM's heb ik ook regel 31 en regel 25 aangepast met het veld bron.

Dus had ik volgende query geprobeerd:

SELECT 
    id, titel, type,
    SUM(CASE WHEN (lv.cont_status='anoniem')  THEN lv.aantal ELSE 0 END) AS SumLvAnoniem,
    SUM(CASE WHEN (lv.cont_status='anoniem' AND lv.bron='facebook')  THEN lv.aantal ELSE 0 END) AS SumLvAnoniemFacebook,
	SUM(CASE WHEN (lv.cont_status='koud')     THEN lv.aantal ELSE 0 END) AS SumLvKoud,
    SUM(CASE WHEN (lv.cont_status='koud' AND lv.bron='facebook')  THEN 1 ELSE 0 END) AS SumLvKoudFacebook,
	SUM(CASE WHEN (lv.cont_status='lead')     THEN lv.aantal ELSE 0 END) AS SumLvLead,
    SUM(CASE WHEN (lv.cont_status='lead' AND lv.bron='facebook')  THEN 1 ELSE 0 END) AS SumLvLeadFacebook,
    SUM(CASE WHEN (lv.cont_status='prospect') THEN lv.aantal ELSE 0 END) AS SumLvProspect,
    SUM(CASE WHEN (lv.cont_status='prospect' AND lv.bron='facebook')  THEN 1 ELSE 0 END) AS SumLvProspectFacebook,
    SUM(CASE WHEN (lv.cont_status='klant')    THEN lv.aantal ELSE 0 END) AS SumLvKlant,
    SUM(CASE WHEN (lv.cont_status='klant' AND lv.bron='facebook')  THEN 1 ELSE 0 END) AS SumLvKlantFacebook,
    SUM(CASE WHEN (lv.cont_status='pool')     THEN lv.aantal ELSE 0 END) AS SumLvPool,
    SUM(CASE WHEN (lv.cont_status='pool' AND lv.bron='facebook')  THEN 1 ELSE 0 END) AS SumLvPoolFacebook,
	
    SUM(CASE WHEN (dl.cont_status='koud')     THEN dl.aantal ELSE 0 END) AS SumDlKoud,
    SUM(CASE WHEN (dl.cont_status='lead')     THEN dl.aantal ELSE 0 END) AS SumDlLead,
    SUM(CASE WHEN (dl.cont_status='prospect') THEN dl.aantal ELSE 0 END) AS SumDlProspect,
    SUM(CASE WHEN (dl.cont_status='klant')    THEN dl.aantal ELSE 0 END) AS SumDlKlant,
    SUM(CASE WHEN (dl.cont_status='pool')     THEN dl.aantal ELSE 0 END) AS SumDlPool
FROM academy
 JOIN (SELECT DISTINCT cont_status FROM contacten) AS c

LEFT OUTER JOIN 
    (SELECT academy_id, cont_status, count(*) as aantal, bron
     FROM academy_landingviews alv
     LEFT OUTER JOIN contacten c ON c.id=alv.user_id
     GROUP BY academy_id, cont_status) AS lv
   ON academy.id=lv.academy_id and c.cont_status=lv.cont_status
LEFT OUTER JOIN
   (SELECT academy_id, cont_status, count(*) as aantal, bron
     FROM academy_download adl
     JOIN contacten c ON c.id=adl.user_id
     GROUP BY academy_id, cont_status) AS dl
   ON academy.id=dl.academy_id and c.cont_status=dl.cont_status
GROUP BY id, titel

Maar dan zijn de waarden bij SumLvAnoniemFacebook bvb dezelfde als die uit de SumLvAnoniem. Waarschijnlijk omdat ik geen count heb van de waarden in de sum met de bron.
Ik heb geprobeerd om een count te maken maar dat wil maar niet lukken.

Maar dit is niet helemaal wat ik eigenlijk wou. De bron kan namelijk alles bevatten en niet alleen facebook. Denk dat het geen goed idee is om die waarden vast te zetten omdat ik nu nog niet weet wat de bronnen allemaal kunnen zijn in de toekomst.

Even vooraf, bij gebruik van GROUP BY is het belangrijk om daar alle velden te noemen die niet in SUM of COUNT zitten. Je hebt hier en daar bron toegevoegd in de SELECT, die moet je dan ook in de GROUP BY zetten.

Maar ik denk dat je bron anders moet oplossen, gezien je laatste opmerking.
Ik denk dat het beter is om de query op te splitsen: eerst een met de totalen op status niveau en dan een aparte (nieuwe) query voor de totalen per bron voor die status.

In je html tabel tabel krijg je dan:
id1 titel1 --totalen landing-views per status-- --totalen download per status
bron 1 --totalen landing-views per status voor deze bron--
bron 2 --totalen landing-views per status voor deze bron--
enz
id2 titel2 --totalen landing-views per status-- --totalen download per status
bron 1 --totalen landing-views per status voor deze bron--
bron 2 --totalen landing-views per status voor deze bron--
enz
enz

Dus uit de 1e query haal je alles wat verwijst naar bron weg en binnen de lus waar je deze 1 voor 1 verwerkt voer je een nieuwe query uit die de getallen per bron voor die titel ophaalt. Hoe dat moet hangt een beetje af hoe je dit nu in php doet.

Die 2e query zou iets kunnen zijn als:

SELECT 
    id, titel, type, bron,
    SUM(CASE WHEN (lv.cont_status='anoniem')  THEN lv.aantal ELSE 0 END) AS SumLvAnoniem,
    SUM(CASE WHEN (lv.cont_status='koud')     THEN lv.aantal ELSE 0 END) AS SumLvKoud,
    SUM(CASE WHEN (lv.cont_status='lead')     THEN lv.aantal ELSE 0 END) AS SumLvLead,
    SUM(CASE WHEN (lv.cont_status='prospect') THEN lv.aantal ELSE 0 END) AS SumLvProspect,
    SUM(CASE WHEN (lv.cont_status='klant')    THEN lv.aantal ELSE 0 END) AS SumLvKlant,
    SUM(CASE WHEN (lv.cont_status='pool')     THEN lv.aantal ELSE 0 END) AS SumLvPool,
FROM academy
 JOIN (SELECT DISTINCT cont_status FROM contacten) AS c

LEFT OUTER JOIN 
    (SELECT academy_id, cont_status, bron, count(*) as aantal
     FROM academy_landingviews alv
     LEFT OUTER JOIN contacten c ON c.id=alv.user_id
     GROUP BY academy_id, cont_status) AS lv
   ON academy.id=lv.academy_id and c.cont_status=lv.cont_status
WHERE id= -hier de id die je nu verwerkt-
GROUP BY id, titel, bron

Jan, ik zie wat je bedoeld. Ik zal eens proberen.
Op regel 8 mag er geen komma meer staan (er zat een mysql foutje in) achteraan ;-)

[size=xsmall]Toevoeging op 14/12/2015 14:26:05:[/size]

Jan, er zit toch iets raar in.
Hieronder de mysql die ik gebruik:

SELECT 
    id, titel, type, bron,
    SUM(CASE WHEN (lv.cont_status='anoniem')  THEN lv.aantal ELSE 0 END) AS SumLvAnoniem1,
    SUM(CASE WHEN (lv.cont_status='koud')     THEN lv.aantal ELSE 0 END) AS SumLvKoud1,
    SUM(CASE WHEN (lv.cont_status='lead')     THEN lv.aantal ELSE 0 END) AS SumLvLead1,
    SUM(CASE WHEN (lv.cont_status='prospect') THEN lv.aantal ELSE 0 END) AS SumLvProspect1,
    SUM(CASE WHEN (lv.cont_status='klant')    THEN lv.aantal ELSE 0 END) AS SumLvKlant1,
    SUM(CASE WHEN (lv.cont_status='pool')     THEN lv.aantal ELSE 0 END) AS SumLvPool1
FROM academy
 JOIN (SELECT DISTINCT cont_status FROM contacten) AS c

LEFT OUTER JOIN 
    (SELECT academy_id, cont_status, bron, count(*) as aantal
     FROM academy_landingviews alv
     LEFT OUTER JOIN contacten c ON c.id=alv.user_id
     GROUP BY academy_id, cont_status) AS lv
   ON academy.id=lv.academy_id and c.cont_status=lv.cont_status
WHERE id= '$academy_id'
GROUP BY id, titel, bron

Als ik nu $row['SumLvAnoniem1'] doe in een html tabel:

<table width="40%" > 
  <tr>
    <td colspan="2">Landingviews door anoniem via <?php echo $row['bron']; ?></td>
    <td colspan="2"><?php echo $row['SumLvAnoniem1']; ?></td>
  </tr>
  <tr>
    <td colspan="2">Landingviews door koud via <?php echo $row['bron']; ?></td>
    <td colspan="2"><?php echo $row['SumLvKoud1']; ?></td>
  </tr>
</table>

Dan krijg ik telkens 2 resultaten bij de eerste <tr> en ook 2 resultaten bij de 2de <tr> maar dat is niet juist, ook de cijfers zijn niet juist. Van waar komt dat 2de cijfers (dat trouwens ook 0 is iedere keer)?
En de bron krijg ik ook niet te zien als ik $row['bron']; doe.

Brecht, je moet altijd als je GROUP BY gebruikt zorgen dat je velden bij en SELECT en GROUP BY overeen komen.

In de subquery ontbreekt bron in GROUP BY (mijn fout)
In de omsluitende query heb je type in SELECT toegevoegd. Dit moet je weghalen of ook toevoegen bij GROUP BY onderaan.

Als jet goed is, is de uitkomst van de deze query iets als (ik heb type even weggelaten):
id titel bron SumLvAnoniem1 SumLvKoud1 SumLvLead1 SumLvProspect1 SumLvKlant1 SumLvPool1
1 T1 facebook 12 3 2 6 1 8
1 T1 website 8 12 3 5 5 2
enz

Ik zou deze dan ook in 1 <tr> weergeven, maar het kan ook in meerdere.
Probeer de query eens uit in iets als phpMyAdmin en kijk of je het gewenste resultaat krijgt.

Ik zou oppassen met de veldnaam type want dat is een gereserveerd woord in sql, je kunt beter een andere veldnaam kiezen als je dit veld nodig hebt.

Mijn query:

SELECT 
    id, titel, bron,
    SUM(CASE WHEN (lv.cont_status='anoniem')  THEN lv.aantal ELSE 0 END) AS SumLvAnoniem1,
    SUM(CASE WHEN (lv.cont_status='koud')     THEN lv.aantal ELSE 0 END) AS SumLvKoud1,
    SUM(CASE WHEN (lv.cont_status='lead')     THEN lv.aantal ELSE 0 END) AS SumLvLead1,
    SUM(CASE WHEN (lv.cont_status='prospect') THEN lv.aantal ELSE 0 END) AS SumLvProspect1,
    SUM(CASE WHEN (lv.cont_status='klant')    THEN lv.aantal ELSE 0 END) AS SumLvKlant1,
    SUM(CASE WHEN (lv.cont_status='pool')     THEN lv.aantal ELSE 0 END) AS SumLvPool1
FROM academy
 JOIN (SELECT DISTINCT cont_status FROM contacten) AS c

LEFT OUTER JOIN 
    (SELECT academy_id, cont_status, bron, count(*) as aantal
     FROM academy_landingviews alv
     LEFT OUTER JOIN contacten c ON c.id=alv.user_id
     GROUP BY academy_id, cont_status, bron) AS lv
   ON academy.id=lv.academy_id and c.cont_status=lv.cont_status
WHERE id= '$academy_id'
GROUP BY id, titel, bron

Ik heb nog steeds dubbele waarden in de <tr>'s waarvan de bron niet is gekend. Uiteraard is er niet altijd een bron meegegeven. Of is dit hier verplicht?


Ik was even een test aan het doen en ik zie toch dat de cijfers precies wel juist zijn alleen is de weergave precies raar. Vb van een resultaat bij 1 ebook met id 11:

11 - Landingviews door anoniem via	0
11 - Landingviews door koud via	0
11 - Landingviews door anoniem via	1
11 - Landingviews door koud via	0
11 - Landingviews door anoniem via facebook	2
11 - Landingviews door koud via facebook	0