Twee tabellen samenvoegen, maar 1 rij uit 2e tabel halen

[MrDummy]

Mijn vraag
Ik heb na wat zoeken deze geprobeerd:

PHP:

$query="
    SELECT
        m.*, p.".$_POST['recordname']."
    FROM member m
        INNER JOIN progress p
            ON m.id = p.memberid
        INNER JOIN
        ( 
            SELECT memberid, MAX(timestamp) maxdate
            FROM progress
            GROUP BY memberid
        ) b ON p.memberid = b.memberid
            AND p.timestamp = b.maxdate
    ORDER BY p.".$_POST['recordname']." DESC, m.name ASC";

De recordname wordt op php pagina via SELECT lijst gekozen, dus het is aanpasbaar via keuze.
De tabel member bestaat alleen uit gebruikers gegevens.
De tabel progress is soort logboek, alle stats worden verzameld, steeds elke dag (is met timestamp ingedeeld) en dus groot tabel. Maar ik heb alleen laatste timestamp record nodig uit progress omdat deze recente stat waarde heeft.

Het is bedoeling dat er kan gesorteerd op gekozen naam uit progress (dus recordnaam kolom) van hoog naar laag en als het gelijke waarde is, op member naam gesorteerd. De rest van progress waarden worden dus niet meegeteld in sorteren.

Helaas krijg ik nul waarde terug, dit moet alle gebruikers worden (tabel members is volledig) maar wordt gesorteerd aan de hand van uitkomst met progress waarde via gekozen record naam kolom.

Relevante software en hardware die ik gebruik
Het is voor website, geprogrammeerd met php en mysql.

Wat ik al gevonden of geprobeerd heb
Ik heb diverse oplossingen bekeken en deze leek me simpelste want het heeft mijn antwoord met soortgelijk voorbeeld, alleen dan aangepast naar mijn versie.

Helaas krijg ik nul antwoord terug, maar er is geen foutmelding via mysqli_error.
Het is niet goed te zien wat er mis is gegaan. Misschien moet het net anders, er is ook LEFT JOIN mogelijkheid dat ook bij sommige sites te lezen is als voorbeeld.

Ik denk dat ergens een klein foutje is. Maar welke is het?
Alvast dank.

[DJMaze]

Je bent er bijna. Maak van je tweede INNER JOIN eens

code:

 AND p.timestamp = (SELECT MAX(timestamp) ....)

Oh, en $_POST['recordname'] is natuurlijk uit den boze.
Zorg ervoor dat $_POST['recordname'] niet onderhevig is aan SQL injection.

[ Voor 41% gewijzigd door DJMaze op 09-04-2018 23:54 ]

[ShitHappens]

Heb je al eens gekeken of en hoe je subqueries onafhankelijk van elkaar werken? Als je 0 resultaten terugkrijgt in het geheel, dan is er ook een subquery die 0 resultaten geeft, die dan niet in orde is.

Dit is inderdaad ook met een LEFT JOIN op te lossen, en daar wordt je query dan nog een stukje korter van ook.

[DJMaze]

*snip* was onzin wat ik schreef

[ Voor 85% gewijzigd door DJMaze op 09-04-2018 23:58 ]

[MrDummy]

DJMaze schreef op maandag 9 april 2018 @ 23:51:
Je bent er bijna. Maak van je tweede INNER JOIN eens

code:

 AND p.timestamp = (SELECT MAX(timestamp) ....)

Oh, en $_POST['recordname'] is natuurlijk uit den boze.
Zorg ervoor dat $_POST['recordname'] niet onderhevig is aan SQL injection.

Is dat stuk weghalen en vervangen door deze:

PHP:

SELECT
m.*, p.".$_POST['recordname']."
FROM member m
INNER JOIN progress p
ON m.id = p.memberid
    AND p.timestamp = (SELECT MAX(timestamp) FROM progress GROUP BY memberid)
ORDER BY p.".$_POST['recordname']." DESC, m.name ASC

De POST is fixed in SELECT table, er is geen edit veld aanwezig, maar bedankt voor aandachtspunt.

[AW_Bos]

DJMaze schreef op maandag 9 april 2018 @ 23:51:
Je bent er bijna. Maak van je tweede INNER JOIN eens

code:

 AND p.timestamp = (SELECT MAX(timestamp) ....)

Oh, en $_POST['recordname'] is natuurlijk uit den boze.
Zorg ervoor dat $_POST['recordname'] niet onderhevig is aan SQL injection.

En dat niet alleen, ook zonder escaping zal je query struikelen over single-quotes.
Voer maar eens 's-Hertogenbosch in. Dit is waar SQL-injection dus gebruik van maakt.

* AW_Bos hoort regelmatig de 'smoes': "Dit in het beheerpaneel, dus het kan geen kwaad!"

[MrDummy]

Wat ben ik beetje dom. Ik heb wel if check routine gemaakt maar is verkeerd ingesteld waardoor de query code steeds overgeslagen wordt. Ik vond al vreemd dat het niks doet, even debug regels ertussen gedaan en dan zie ik wat er niet goed gaat.
Zo zie ik nooit de resultaat en pruts ik hele tijd mee om niks.

Nu komt er wel resultaat op scherm en ook foutmeldingen zodat ik het kan corrigeren.

Edit: na wat zoeken met juiste opstelling kom ik op:

PHP:

    $query="
    SELECT
        m.*, CAST(p.".$_POST['recordname']." as decimal(10,2)) as result
    FROM member m
        INNER JOIN progress p
            ON m.id = p.memberid
        INNER JOIN
        ( 
            SELECT memberid, MAX(timestamp) maxdate
            FROM progress
            GROUP BY memberid
        ) b ON p.memberid = b.memberid
            AND p.timestamp = b.maxdate
    WHERE clanid=".$clanid."
    ORDER BY result DESC, m.name ASC";

Vergat wel te zeggen dat waarden als VARCHAR opgeslagen zijn, maar dat gaat fout bij hoog-laag sorteren en dus moet ik even naar decimal overzetten zodat het wel goed gaat.

Of dit code korter en simpeler kan, dat kan. Op dit moment geeft het resultaat goed weer zoals ik het wil.
Met tweede INNER JOIN naar LEFT JOIN krijg ik veel te veel rows terug (dubbele waarden) dus ik hou nog op INNER JOIN. Het moet wel 1 row pakken en tonen.

[ Voor 55% gewijzigd door MrDummy op 10-04-2018 18:28 ]

[DJMaze]

@MrDummy ik hoop niet dat je de code op internet zet.
I.v.m. de GDPR zou je een dikke boete kunnen krijgen en een rapportering bij de NCSC

[Verwijderd]

Moet echt overal GDPR bijgehaald worden?

[MrDummy]

DJMaze schreef op dinsdag 10 april 2018 @ 19:25:
@MrDummy ik hoop niet dat je de code op internet zet.
I.v.m. de GDPR zou je een dikke boete kunnen krijgen en een rapportering bij de NCSC

De site bevat geen persoonlijke data, alleen maar game data. De gamers komen niet hiermee in de gevaar, er zijn ook geen login gegevens aanwezig.
De game data is ook gebruikt op andere websites dus het is vrijwel hetzelfde situatie als andere sites.
Ik ga wel wat paar checks toevoegen om minder problemen te geven.

Ik heb gekeken wat je zegt, het gaat dus om bescherming persoonlijke gegevens. Maar op site is geen data aanwezig die voor ernstige problemen kan zorgen voor gamers. De data zelf is allang openbaar via andere sites, dus het maakt weinig uit. De data zelf is openbaar op de game servers via API.
De site verwerkt game data tot statistieke pagina.

Moet ik hier nog steeds zorgen om maken?

[DJMaze]

MrDummy schreef op dinsdag 10 april 2018 @ 21:01:
Moet ik hier nog steeds zorgen om maken?

Je zegt dat de gamers niet kunnen inloggen en er geen details zijn, dan hoef je je inderdaad daar geen zorgen over te maken.

Echter kan men nog wel je hele database uitlezen door die $_POST['recordname'].
In de browser:

1. Rechtermuisknop op je <select>
2. Wijzig waarde van een input in "field UNION SELECT ...... UNION SELECT m.*, p."

Verwijderd schreef op dinsdag 10 april 2018 @ 21:00:
Moet echt overal GDPR bijgehaald worden?

Tegenwoordig helaas wel, ook al was mijn aanname in dit geval bij "SELECT * FROM member" iets overtrokken.
De GDPR is in 2016 ingegaan en blijkbaar weet nog steeds bijna niemand wat het is en moet je per 25 mei 2018 er toch echt volledig aan voldoen.
https://ictrecht.nl/facts...gistreren-van-datalekken/

[ Voor 31% gewijzigd door DJMaze op 10-04-2018 22:26 ]

[MrDummy]

Ja op dat punt heb je gelijk in, ik weet zelf ook dat zulke velden aardig wat risico's meenemen en dan kun je via string aanpassing wat uitlezen.

In dat geval moet ik dus code ertussen zetten die verboden tekens eruit gooit en alleen bekende waarden accepteert met hardcoded regels.

Gelukkig is database oninteressant om uit te lezen, er zijn geen wachtwoorden en er is alleen maar openbare game data aanwezig die iedereen net zo kan lezen via game API. Er valt dus niks mee te scoren behalve om clan mee te pesten met rare stats of spamteksten.

Wanneer er persoonlijke zaken erbij komen zoals login en wachtwoord, dan moet ik zeker wel meer beveiliging checks ertussen doen om misbruik en uitlezen te voorkomen. Dat zal ik zeker rekening mee houden.

De Member tabel is alleen maar characters uit het spel zelf, dus fictieve wereld. Geen echte namen.

Toch bedankt voor je attentie. Alleen de woorden met dreiging dat je me aangeeft bij de dienst is wel beetje te hard en niet nodig. Je zou anders kunnen zeggen - want je weet zelf eerst niet wat voor site ik heb - moet je dus anders zeggen om vriendelijkere manier.
Je kunt dus zeggen dat als mijn website persoonlijke data bevat, zulke dingen niet open moeten staan en beveiliging erbij moet komen om injectie te voorkomen. Mocht dat niet zo is, dan geen paniek.

[ Voor 4% gewijzigd door MrDummy op 11-04-2018 15:30 ]

[Harrie_]

MrDummy schreef op woensdag 11 april 2018 @ 15:28:
[...]
In dat geval moet ik dus code ertussen zetten die verboden tekens eruit gooit en alleen bekende waarden accepteert met hardcoded regels.
[...]

Geen probleem met prepared statements

PHP:

$sql =    "SELECT column FROM TABLE WHERE id = :id";
$query =  $dbh->prepare($sql);
$query -> bindParam(':id', $_GET['id'], PDO::PARAM_INT);
$query -> execute();

[ Voor 5% gewijzigd door Harrie_ op 11-04-2018 15:57 ]

[Merethil]

MrDummy schreef op woensdag 11 april 2018 @ 15:28:

Toch bedankt voor je attentie. Alleen de woorden met dreiging dat je me aangeeft bij de dienst is wel beetje te hard en niet nodig. Je zou anders kunnen zeggen - want je weet zelf eerst niet wat voor site ik heb - moet je dus anders zeggen om vriendelijkere manier.
Je kunt dus zeggen dat als mijn website persoonlijke data bevat, zulke dingen niet open moeten staan en beveiliging erbij moet komen om injectie te voorkomen. Mocht dat niet zo is, dan geen paniek.

Hij zegt ook nergens dat 'ie je zou gaan aangeven, hij zegt alleen dat GDPR je hier een dikke boete voor zou kunnen geven als er een audit gebeurt of iemand het idee heeft dat jouw site niet voldoet en hierom een verzoek tot audit laat plaatsvinden.
Hoe dan ook; er zijn gewoon bepaalde standaarden te volgen, zelfs als jijzelf de data niet kritiek acht, al is het maar om de privacy van je gebruikers te waarborgen.

[MrDummy]

Ik heb geen beste antwoord gemarkeerd, omdat mijn eerste code blijkbaar nog werkt.

Wel heb ik op nuttige antwoorden een duimpje gegeven. Deze variaties en goede aanvullingen zal ik meenemen in mijn coding. Die van Harrie ga ik gebruiken. (al eerder gedaan bij login/password check routines in andere scripts)

Verder iedereen hartelijk dank. Ik ben alweer stuk verder gekomen.

[AW_Bos]

Ik hoop dat er nu wel escaping of parametered queries zijn toegevoegd
Als het een belangrijke applicatie is,laat deze door externen goed auditten.

[ Voor 35% gewijzigd door AW_Bos op 13-04-2018 11:12 ]

[Harrie_]

@MrDummy Goed dat je prepared statements gebruikt, mijn tip is om je aan te wennen of vanaf nu alleen maar prepared statements te gebruiken en geen onderscheid maken in waar je het wel of niet nodig vindt. Gewoon _nooit_ meer variabelen hard in je query plakken dan zit je altijd veilig en heb je ook geen ellende met speciale karakters, apostrofs en dat soort geneuzel

[scosec]

-- edit niet relevant gaat om mysql

[ Voor 90% gewijzigd door scosec op 13-04-2018 11:24 ]