Dotaz na řešení pomocí SQL

Dotaz na řešení pomocí SQL
« kdy: 20. 02. 2020, 18:46:13 »
Používám Wamp 2.4, PHP 5 a Mysql 5.0. Používám jedno forum stažené z netu, které jsem trochu předělal. Původní verze pracovala takto - po rozkliknutí fora, se zobrazí příspěvky. Tzn. první příspěvek je vlastně "topic"/téma, které založil uživatel. Toto teď předělávám, tak, že při vytváření "diskuse" se vlastně nezakládá diskuse, ale nový článek (pod kterým pak budou komentáře). První příspěvek se tedy už nebude ukládat do tabulky posts (příspěvky ve foru), ale jen do topics (témata diskusí).

Teď nevím jestli sem mohu vložit přímo PHP kód, což by pro mě bylo jednodušší než generovat konkrétní SQL příkazy. Snad pochopíte oč mi jde. Jde mi o SQL příkazy, které pracují na prohlížení tématu (diskuse). Se změnou, kterou jsem udělal, se po rozkliknutí diskuse nezobrazí nic a má se zobrazit článek bez komentářů (žádné zatím nebyly přidány).

Původní navržení pro zobrazení diskuse pracuje takto:
Řádek 306
Kód: [Vybrat]
$query = array(
'SELECT' => 'p.id',
'FROM' => 'posts AS p',
'WHERE' => 'p.topic_id='.$id,
'ORDER BY' => 'p.id',
'LIMIT' => $forum_page['start_from'].','.$forum_user['disp_posts']
);
Říká: Vyber všechny záznamy s posts.id, k tomu topicu, který chci prohlédnout... Či-li má vybrat určitý počet komentářů se zadaným topic.id ... Pak se dotaz sestaví a provede. Do pole posts_id se uloží idéčka těch postů.

Kód: [Vybrat]
while ($row = $forum_db->fetch_assoc($result)) {
$posts_id[] = $row['id'];
}

A teď se dostávám k tomu klíčovému s čím potřebuju poradit. Jestliže existuje alespoň jedno idéčko, pak se provede další SQL dotaz:

Kód: [Vybrat]
$query = array(
'SELECT' => 'u.email, u.title, u.url, u.location, u.signature, u.email_setting, u.num_posts, u.registered, u.admin_note, u.avatar, u.avatar_width, u.avatar_height, p.id, p.poster AS username, p.poster_id, p.poster_ip, p.message, p.hide_smilies, p.posted, p.edited, p.edited_by, g.g_id, g.g_user_title, o.user_id AS is_online',
'FROM' => 'posts AS p',
'JOINS' => array(
array(
'INNER JOIN' => 'users AS u',
'ON' => 'u.id=p.poster_id'
),
array(
'INNER JOIN' => 'groups AS g',
'ON' => 'g.g_id=u.group_id'
),
array(
'LEFT JOIN' => 'online AS o',
'ON' => '(o.user_id=u.id AND o.user_id!=1 AND o.idle=0)'
),
),
'WHERE' => 'p.id IN ('.implode(',', $posts_id).')',
'ORDER BY' => 'p.id'
);

Z tabulky users se vyberou nějaké uživatelské informace jako email, title - název uživatele, avatar.
Z tabulky posts se vybere idéčko příspěvku, poster as username, id odesilatele, message - samotný text příspěvku, datum odeslání.
Z tabulky group se vybere funkce jakou plní uživatel (moderátor, správce, admin, uživatel).
WHERE - to platí pro všechny idečka z daného seznamu.

Napadá vás, jak tento příkaz modifikovat, aby v něm byly zahrnuty také data od uživatele, který napsal článek? Tj. od autora článku.

Opakuji, že původně autor diskuse byl v prvním příspěvku, což teď není, když první příspěvek začíná až od prvního komentujícího uživatele. ID autora už znám.

Jinak na řádku 124, existuje tento SQL příkaz na dohledání informací o topicu, a tam se to dohledává právě podle $id čísla topicu (t.id). ID autora je t.uid as poster_id.

Kód: [Vybrat]
$query = array(
'SELECT' => 't.uid as poster_id, t.subject, t.desc_x, t.article as message, t.hide_smilies, t.posted, t.edited, t.first_post_id, t.closed, t.num_replies, t.sticky, f.id AS forum_id, f.forum_name, f.moderators, fp.post_replies, u.username',
'FROM' => 'topics AS t',
'JOINS' => array(
array(
'INNER JOIN' => 'forums AS f',
'ON' => 'f.id=t.forum_id'
),
array(
'INNER JOIN' => 'users AS u',
'ON' => 'u.id=t.uid'
),
array(
'LEFT JOIN' => 'forum_perms AS fp',
'ON' => '(fp.forum_id=f.id AND fp.group_id='.$forum_user['g_id'].')'
)
),
'WHERE' => '(fp.read_forum IS NULL OR fp.read_forum=1) AND t.id='.$id.' AND t.moved_to IS NULL'
);



Re:Dotaz na řešení pomocí SQL
« Odpověď #1 kdy: 20. 02. 2020, 19:39:21 »
Zkus UNION  :)

alex6bbc

  • *****
  • 1 432
    • Zobrazit profil
    • E-mail
Re:Dotaz na řešení pomocí SQL
« Odpověď #2 kdy: 20. 02. 2020, 20:26:03 »
ja bych nemenil chovani ukladani prispevku do jejich tabulky, jen bych kazdy uplne novy topic
ulozil do nove samostatne tabulky s id odpovidajicicho prispevku v prispevcich.
pak bych jen projel tabulku topic a vylistoval topicy a pro kazdy topic uz pak jen podle id dohledat dalsi prispevky.

Re:Dotaz na řešení pomocí SQL
« Odpověď #3 kdy: 20. 02. 2020, 23:25:45 »
ja bych nemenil chovani ukladani prispevku do jejich tabulky, jen bych kazdy uplne novy topic
ulozil do nove samostatne tabulky s id odpovidajicicho prispevku v prispevcich.
pak bych jen projel tabulku topic a vylistoval topicy a pro kazdy topic uz pak jen podle id dohledat dalsi prispevky.

Díky za odpověď. Nevím jestli jsem vás pochopil, protože tabulka topic tam existuje, jestli jsem vás pochopil správně (?) tak právě s tím co říkáte.

Re:Dotaz na řešení pomocí SQL
« Odpověď #4 kdy: 21. 02. 2020, 00:11:47 »
Zkus UNION  :)

Jak to myslíte?
SELECT t.uid as poster FROM topics as t
WHERE t.uid=$poster_id
UNION
SELECT p.id as poster FROM posts as p
WHERE p.topic_id=$id AND p.id=$poster_id
ORDER BY City;


while ($row = $forum_db->fetch_assoc($result)) {
   $posts_id[] = $row['poster_id'];
}

Nebo něco jiného. Vaše odpověď je příliš abstraktní a nenasměrovala mě. Něvím ohledně čeho a jaké použití máte na mysli.
« Poslední změna: 21. 02. 2020, 00:15:04 od exkalibr »


Re:Dotaz na řešení pomocí SQL
« Odpověď #5 kdy: 21. 02. 2020, 00:51:35 »
Asi jsem na to přišel. Mám teď něco takového:

Kód: [Vybrat]
SELECT u.email, u.url, u.signature, u.email_setting, u.num_posts, u.registered, u.admin_note, u.avatar, u.avatar_width, u.avatar_height, p.id, p.poster AS username, p.poster_id, p.poster_ip, p.message, p.posted, p.edited, p.edited_by, g.g_id, g.g_user_title, o.user_id AS is_online
FROM posts AS p
INNER JOIN users AS u ON u.id = p.poster_id
OR u.id =5
INNER JOIN groups AS g ON g.g_id = u.group_id
LEFT JOIN online AS o ON ( o.user_id = u.id
AND o.user_id !=1
AND o.idle =0 )
WHERE u.id =5
ORDER BY p.id
LIMIT 0 , 30

Ale nevrací mi to žádný výsledek. Nevíte proč?

Re:Dotaz na řešení pomocí SQL
« Odpověď #6 kdy: 21. 02. 2020, 07:40:57 »
Zkus UNION  :)

Jak to myslíte?
SELECT t.uid as poster FROM topics as t
WHERE t.uid=$poster_id
UNION
SELECT p.id as poster FROM posts as p
WHERE p.topic_id=$id AND p.id=$poster_id
ORDER BY City;


while ($row = $forum_db->fetch_assoc($result)) {
   $posts_id[] = $row['poster_id'];
}

Nebo něco jiného. Vaše odpověď je příliš abstraktní a nenasměrovala mě. Něvím ohledně čeho a jaké použití máte na mysli.
Ano, tak jsem to myslel.

Logik

  • *****
  • 1 022
    • Zobrazit profil
    • E-mail
Re:Dotaz na řešení pomocí SQL
« Odpověď #7 kdy: 21. 02. 2020, 11:09:16 »
- ukládat každý topic do své tabulky je zvěrstvo, fuj. Kdo to poradil, tak by měl bejt danej na pranýř a každejch pět minut bičovanej skriptama na SQLko.
A proč? Co když budu chtít např. zjistit celkovej počet příspěvků danýho uživatele?


- pokud chci záznamy, kde je autor z daného seznamu, anebo autorem článku, tak je to přeci jednoduchý. Nevymejšlet  koniny. Co chci, všechny uživatele, kteří jsou buďto autorem článku, NEBO z danýho seznamu. Nebo je

OR

Tedy

'WHERE'      => 'p.id IN ('.implode(',', $posts_id).') OR ....',

Pokud na toho autora potřebuju ještě joinout tabulku, tak ji joinout leftjoinem.Řešit to celé unionem jde taky, ale proč se drbat levou nohou za pravym uchem, když mám drbátko.... (je pravda, že v některých případech je union i rychlejší, protože některý DB optimalizátory neumí převádět JOIN na UNION, ale to na týdle úrovni znalostí neřeš).

- To implode v SQL dotazu se mi nelíbí, smrdí SQL injekcí. Sice tady to asi nehrozí, ale obecně bys mělmít bezpečnej mechanismus, jak vkládat do dotazu seznamy hodnot.
« Poslední změna: 21. 02. 2020, 11:10:53 od Logik »

Re:Dotaz na řešení pomocí SQL
« Odpověď #8 kdy: 21. 02. 2020, 15:50:50 »
Můj pokus zjednodušeně:
Kód: [Vybrat]
SELECT u.email, p.id, p.poster_id
FROM posts AS p
INNER JOIN users AS u ON u.id = p.poster_id
OR u.id =5
WHERE u.id =5
ORDER BY p.id
LIMIT 0 , 30
Problém je klauzule WHERE. Zatímco mám FROM posts AS p, WHERE odkazuje na tabulku users.

Jak to opravit?

Re:Dotaz na řešení pomocí SQL
« Odpověď #9 kdy: 21. 02. 2020, 15:56:52 »
Zkus UNION  :)

Jak to myslíte?
SELECT t.uid as poster FROM topics as t
WHERE t.uid=$poster_id
UNION
SELECT p.id as poster FROM posts as p
WHERE p.topic_id=$id AND p.id=$poster_id
ORDER BY City;


while ($row = $forum_db->fetch_assoc($result)) {
   $posts_id[] = $row['poster_id'];
}

Nebo něco jiného. Vaše odpověď je příliš abstraktní a nenasměrovala mě. Něvím ohledně čeho a jaké použití máte na mysli.
Ano, tak jsem to myslel.

To si myslím že nemohu udělat, protože když vybírám všechny uživatele, kteří v tabulce posts jsou evidováni, že přispěli do daného tématu, tyto data jsou jiné, než data z tabulky users. Není tam kompatibilita. Jediná kombatibilita je mezi u.id a p.uid ...

Logik

  • *****
  • 1 022
    • Zobrazit profil
    • E-mail
Re:Dotaz na řešení pomocí SQL
« Odpověď #10 kdy: 21. 02. 2020, 16:40:42 »
Odpověď je v tom, co vlastně chceš za data:
Takhle si napsal:
"Dej mi čísla postů a k nim přiřaď takové emaily lidí, kteří buďto napsali post, anebo maj id = 5, a to dále omez na lidi, kteří mají id = 5"
To nechceš. Už proto, že ti to ke každýmu postu přiřadí i člověka s id 5, ted id=5 tam bude opravdu hodněkrát. Problém tohodle dotazu je vlastně, že je sám nesmyslnej: výsledek toho dotazu má bejt seznam [post, autor_postu]. Ale k jakýmu postu by tendle dotaz
měl přiřadit autora článku, kterej nenapsal žádnej post?

Jedinej smysl by mělo vrátit takovýho člověka s post_id NULL. To by Ti ale už mělo napovědět, že chceš listovat uživatele, a k nim přiřazovat posty. Tedy něco jako
"Dej mi uživatele, který buďto napsali post, anebo maj id=5, a k nim dej ten případnej post"Tedy "primární" tabulka, to první v SELECTU (to je jen takovej myšlenkovej konstrukt, ne, že by první tabulka v selectu měla nějakej reálnej význam) tedy budou uživatelé.To, že tam je "případnej" znamená, že uživatel může (postovatel) a nemusí (autor) mít žádnej post, takže použiješ ne INNER,ale....

... když nebudeš dál vědět, tak se zeptej dál. :-)

Logik

  • *****
  • 1 022
    • Zobrazit profil
    • E-mail
Re:Dotaz na řešení pomocí SQL
« Odpověď #11 kdy: 21. 02. 2020, 16:42:43 »
Ještě k
"První příspěvek se tedy už nebude ukládat do tabulky posts (příspěvky ve foru), ale jen do topics (témata diskusí)."
To bych nedoporučoval. Protože pak budeš mít problém např. když budeš chtít vyhledávat ve všech příspěvcích.Daleko lepší je i první příspěvek nechat v postech, ale nějak si ho speciálně označit. Tak můžeš jak pracovat s topicy a posty
odděleně, tak i najednou.

Re:Dotaz na řešení pomocí SQL
« Odpověď #12 kdy: 21. 02. 2020, 17:50:14 »
Ještě k
"První příspěvek se tedy už nebude ukládat do tabulky posts (příspěvky ve foru), ale jen do topics (témata diskusí)."
To bych nedoporučoval. Protože pak budeš mít problém např. když budeš chtít vyhledávat ve všech příspěvcích.Daleko lepší je i první příspěvek nechat v postech, ale nějak si ho speciálně označit. Tak můžeš jak pracovat s topicy a posty
odděleně, tak i najednou.

Tahle situace nehrozí. Chci to mít striktně oddělené, protože články, které píšu já jako autor webu jsou něco úplně jiného než příspěvky běžných uživatelů, kteří nemají právo zakládat a psát články. Jde tam i o to, že moje články mají mnohonásobně vyšší hodnotu a důležitost než samotné komentáře. Pro mě je s tím spojena přehlednost. V phpmyadminu kliknu na tabulku a vidím všechny moje články, které mohu snadno zazálohovat, než když bych je měl hledat mezi desítkami komentářů.
« Poslední změna: 21. 02. 2020, 17:52:43 od exkalibr »

Re:Dotaz na řešení pomocí SQL
« Odpověď #13 kdy: 21. 02. 2020, 17:54:06 »
Jedinej smysl by mělo vrátit takovýho člověka s post_id NULL. To by Ti ale už mělo napovědět, že chceš listovat uživatele, a k nim přiřazovat posty. Tedy něco jako
"Dej mi uživatele, který buďto napsali post, anebo maj id=5, a k nim dej ten případnej post"Tedy "primární" tabulka, to první v SELECTU (to je jen takovej myšlenkovej konstrukt, ne, že by první tabulka v selectu měla nějakej reálnej význam) tedy budou uživatelé.To, že tam je "případnej" znamená, že uživatel může (postovatel) a nemusí (autor) mít žádnej post, takže použiješ ne INNER,ale....

... když nebudeš dál vědět, tak se zeptej dál. :-)

Já vůbec nemám šajnu jak to sestavit.

Re:Dotaz na řešení pomocí SQL
« Odpověď #14 kdy: 21. 02. 2020, 19:36:00 »
Myslím, že se mi to povedlo:

Kód: [Vybrat]
INNER JOIN users AS u ON u.id = p.poster_id
OR u.id =5
INNER JOIN groups AS g ON g.g_id = u.group_id
LEFT JOIN online AS o ON ( o.user_id = u.id
AND o.user_id !=1
AND o.idle =0 )
WHERE t.uid =5
ORDER BY p.id
LIMIT 0 , 30

Je to tak správně?

Výsledek jeden záznam:
Kód: [Vybrat]
id uid topic_id poster_id
41 5 NULL NULL
topic_id a poster_id je null, protože článek nemá přiřazený post. Zatím tam nejsou komentáře.

Ještě jsem to chtěl zpřehlednit pomocí t.uid  AS authors_id, ale to píše chybu "unknown column t.authors_id