[PHP + SQL] Aantal chatters in rooms tellen

[CH4OS]

Ik ben bezig om samen met iemand een chatserver te maken die (deels) gebaseerd is op SQL.
Allemaal heel mooi natuurlijk, maar stuit wel tegen een klein probleempje aangaande de roomlijst.
We willen dat we hem op elk veld kunnen sorteren, zowel 'gewoon' als af tellend (descending).

Heb hem op zich ook helemaal af, behalve die voor het aantal. Ik heb wel een manier klaar, om te tellen hoeveel rooms dat er zijn, maar dat is een query die ik laat lopen in de al bestaande while-loop.

Eerst maar eens even de (benodigde) DB structuur schetsen:

SQL:

CREATE TABLE `user_rooms` (
  `user_id` int(10) NOT NULL,
  `room_id` int(3) default NULL,
  `mode` tinytext,
  `status` tinyint(1) default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `rooms` (
  `id` int(3) NOT NULL auto_increment,
  `roomnaam` varchar(25) default NULL,
  `roomhex` tinytext,
  `roommodes` varchar(50) default NULL,
  `pass_gold` tinytext,
  `pass_brown` tinytext,
  `categorie` varchar(2) default NULL,
  `language` varchar(2) default NULL,
  `registered` tinyint(1) default NULL,
  `topic` varchar(50) default NULL,
  `onjoin` varchar(100) default NULL,
  `ip_creation` varchar(15) default NULL,
  `date_creation` int(15) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `roomnaam` (`roomnaam`),
  UNIQUE KEY `topic` (`topic`),
  UNIQUE KEY `onjoin` (`onjoin`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Ik kan nu twee dingen doen: een aantal veld in de rooms tabel plaatsen en die elke keer updaten als iemand een room joint, maar dan is de mogelijkheid er, dat er redundantie ontstaat, aangezien er dan ook een record word weggeschreven in de user_rooms tabel. Hiervandaan is het dus ook gewoon mogelijk lijkt me, om met een simpele count query achter het aantal mensen in een bepaalde room te komen.

Over dat aantal veld in de rooms tabel, dat is weliswaar de makkelijkste oplossing, het is dus (mijns inziens) niet redundant, vandaar dat ik het wil proberen op te lossen door het te tellen vanuit koppeltabel user_rooms... Dus oplossing 1 valt eigenlijk af, tenzij er echt niets anders op zit...

Een collega op mijn werk, vertelde me, dat ik dan met joins moet werken, maar dat maakt niet uit, aangezien je dan maar 1 resultaat terug krijgt, namelijk die van het totaal aantal users die zich in rooms bevinden, en daar is eigenlijk geeneens een (left) join voor nodig.

Wat ik dus zelf heb geprobeert, is een query maken die (dat dacht ik toen althans) het aantal chatters per room optelt:

SQL:

SELECT COUNT(`room_id`) as aantal 
FROM `user_rooms` 
LEFT JOIN `rooms` ON user_rooms.room_id = rooms.id

Maar dit geeft dus, zoals ik al vaker zei, het totaal aantal chatters van alle rooms tezamen terug, wat niet de bedoeling is.

Ben ook nog niet helemaal thuis in SQL, dus andere / betere zoektermen die ik zou kunnen gebruiken weet ik helaas niet te bedenken.

Hopelijk snappen jullie mijn probleem een beetje, anders lees ik de vragen wel
Een voorbeeld is overigens te zien op http://www.appieskamertjuh.nl/chat/roomlist_test.php alwaar het sorteren op alles (behalve aantal dus) werkt...

Ik gebruik overigens PHP5 en MySQL 5...

[ Voor 7% gewijzigd door CH4OS op 04-08-2006 22:45 ]

[Janoz]

Laat hier nu juist 'group by' voor bedoeld zijn. Nu tel je inderdaad alle gebruikers (en mochten gebruikers in 2 rooms zitten, dan tel je ze dubbel). Omdat je ze per room wilt tellen zul je ze moeten groeperen op bv rooms.id.

Zet dus eens " GROUP BY rooms.id" achter de query.

[remcotolsma]

MySQL:

SELECT r.id AS room_id, r.roomnaam AS room_name, COUNT(u.user_id) AS number_users
FROM user_rooms u
LEFT JOIN rooms r ON u.room_id = r.id
GROUP BY r.id

GROUP BY dus zoals hierboven ook genoemd werd...

[ Voor 14% gewijzigd door remcotolsma op 04-08-2006 23:11 ]

[CH4OS]

Het GROUP BY werkt inderdaad, maar kan ik nu nog steeds sorteren op bijvoorbeeld registered, de roomnaam en de topic?

Dat gebruikers in meerdere rooms kunnen zitten, dat klopt, dat mag ook gewoon... Het is immers een roomlijst, die dingen weergeeft over verschillende rooms... Waar aantal chatters (in die room) ook een van is...

Heb net van alles geprobeerd, maar dan krijg ik de aantal chatters in een room niet meer terug...
Enige wat ik dan had gedaan, was de GROUP BY clause aanpassen...

Heb de query van Remcotolsma omgebouwd:

SQL:

SELECT r.registered AS registered, r.roomnaam AS roomnaam, r.topic AS topic, COUNT( u.user_id ) AS aantal
FROM user_rooms u
LEFT JOIN rooms r ON u.room_id = r.id
GROUP BY r.id

Soms... Soms is zelfs PHPMyAdmin handig

Hmmm... Alles werkt nu, behalve het sorteren op Aantal... En dat is eigenlijk ook wel de bedoeling
Heb nu wel de aantallen erbij, dus ben al wel opgeschoten...

Maar geloof dat dat niet kan... Dus zal wel een aantal column moeten toevoegen in de rooms tabel...
Heb het al opgelost, ondanks dat het wel wat omslachtig is...

En het is opgelost! http://www.appieskamertjuh.nl/chat/roomlist_test.php voor uiteindelijk resultaat...

[ Voor 215% gewijzigd door CH4OS op 05-08-2006 01:23 ]

[CH4OS]

Daar ben ik weer, met wederom een vraag.
Ik probeer nu een query te bouwen, waarmee ik kan zien welke chatters / users in een bepaalde room zijn. Ik bepaal dit aan de hand van de afgevangen variabele $_GET['roomid'].

Ik heb al naar dingen gezocht en waarschijnlijk moet ik een left join en een right join gebruiken, punt is alleen dat ik (wederom) de query niet voor elkaar krijg...

Heb momenteel de volgende query, maar ik weet bij voorbaat dat deze query niet gaat werken, simpelweg omdat ik (nog) niet weet hoe JOINS werken in MySQL:

SQL:

SELECT r.roomnaam AS roomnaam, u.nick AS nick FROM users u, rooms r 
LEFT JOIN rooms rm ON u.room_id = r.id 
GROUP BY r.id DESC

Ik heb overigens al wel wat gezocht over MySQL en joins, kwam onder andere deze pagina tegen, snap nu (gelukkig) al wel wat meer van JOINS, maar krijg schijnbaar nog steeds de goede query niet voor elkaar...

Ziet iemand wat ik fout doe, zeg dan gewoon wat ik fout doe, dan leer ik er van...

[truegrit]

ik neem aan dat je tabel users met tabel rooms wilt joinen? dan kan je beter iets doen als:

SQL:

... from users left join rooms on room_id = id ...

[CH4OS]

truegrit schreef op woensdag 09 augustus 2006 @ 00:01:
ik neem aan dat je tabel users met tabel rooms wilt joinen? dan kan je beter iets doen als:

SQL:

... from users left join rooms on room_id = id ...

Sorry, was ik erbij vergeten te vermelden, ik heb nu 3 tabellen:
• users
• rooms
• user_rooms
Die derde tabel, is de koppeltabel, omdat je als chatter in meerdere rooms tegelijk kan en mag zijn. Dus moet er ergens ook bijgehouden worden, wie er in welke room aanwezig is... Ik geef wel even de DB structuur van die drie tabellen:

SQL:

CREATE TABLE `users` (
  `id` tinyint(10) NOT NULL auto_increment,
  `smf_id` int(10) default NULL,
  `nick` varchar(50) default NULL,
  `ip` varchar(15) default NULL,
  `gate` tinytext,
  `profilmode` varchar(2) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `nick` (`nick`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `rooms` (
  `id` int(3) NOT NULL auto_increment,
  `roomnaam` varchar(25) default NULL,
  `roomhex` tinytext,
  `roommodes` varchar(50) default NULL,
  `pass_gold` tinytext,
  `pass_brown` tinytext,
  `categorie` varchar(2) default NULL,
  `language` varchar(2) default NULL,
  `registered` tinyint(1) default NULL,
  `secret` tinyint(1) NOT NULL default '0',
  `topic` varchar(100) default NULL,
  `onjoin` varchar(100) default NULL,
  `ip_creation` varchar(15) default NULL,
  `date_creation` int(15) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `roomnaam` (`roomnaam`),
  UNIQUE KEY `topic` (`topic`),
  UNIQUE KEY `onjoin` (`onjoin`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `user_rooms` (
  `user_id` int(10) NOT NULL,
  `room_id` int(3) default NULL,
  `mode` tinytext,
  `status` tinyint(1) default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Waar uiteraard de chatnaam (nick) uit de users tabel moet komen en de roomnaam uit de rooms tabel. Het 'filter' is dus de user_rooms tabel, waar de koppeling(en) gemaakt kunnen worden...

[ Voor 4% gewijzigd door CH4OS op 09-08-2006 00:22 ]

[truegrit]

wat ik dan zou doen is 2 joins, tussen de koppeltabel en de 2 andere tabellen. iets van:

SQL:

... from a left join b where x = y left join c where x2 = y2 ...

Maar dat had je zelf ook wel kunnen bedenken (mag ik hopen dan)

[ Voor 16% gewijzigd door truegrit op 09-08-2006 00:14 ]

[CH4OS]

truegrit schreef op woensdag 09 augustus 2006 @ 00:13:
wat ik dan zou doen is 2 joins, tussen de koppeltabel en de 2 andere tabellen. iets van:

SQL:

... from a left join b where x = y left join c where x2 = y2 ...

Maar dat had je zelf ook wel kunnen bedenken (mag ik hopen dan)

Dan hoop je helaas wel verkeerd Ben wat SQL betreft eigenlijk nogal een groentje...

[ Voor 6% gewijzigd door CH4OS op 09-08-2006 00:27 ]

[truegrit]

maar werkt het nou?

[CH4OS]

truegrit schreef op woensdag 09 augustus 2006 @ 02:41:
maar werkt het nou?

Ik heb het voor alsnog niet getest, ik probeer vanavond even als ik thuis ben van mijn werk...

[CH4OS]

Ik heb het volgende geprobeerd, aan de hand van de gegeven tips hier:

SQL:

SELECT ur.nick AS nick, r.roomnaam AS roomnaam
FROM user_rooms ur, rooms r 
LEFT JOIN users WHERE users.id = user_rooms.user_id 
LEFT JOIN rooms WHERE user_rooms.room_id = rooms.id 
WHERE rooms.room_id = 1

Maar ik krijg nu deze foutcode:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where users . id = user_rooms . user_id LEFT JOIN rooms where user_rooms . room' at line 1

Ik denk eigenlijk dat het weer iets te maken heeft met een GROUP of ORDER by, maar weet dat niet zeker, omdat ik nu maar van 1 room een resultaat krijg en wil, via de WHERE-clause, dus GROUP by heb ik sowieso niet nodig lijkt me dit maal...

[ Voor 19% gewijzigd door CH4OS op 09-08-2006 18:14 ]

[user109731]

Moet je geen ON hebben ipv WHERE?

[CH4OS]

Grote prutser schreef op woensdag 09 augustus 2006 @ 18:27:
Moet je geen ON hebben ipv WHERE?

Idd, dat ik daar overheen kijk...

Ik heb de query aangepast naar

SQL:

SELECT u.nick AS nick, r.roomnaam AS roomnaam FROM users u, rooms r 
LEFT JOIN users ON users.id = user_rooms.user_id 
LEFT JOIN rooms ON user_rooms.room_id = rooms.id 
WHERE rooms.id = 1

Maar ook deze werkt niet, krijg een

1054 - Unknown column 'user_rooms.user_id' in 'on clause'

foutmelding, terwijl deze er wél is?

[ Voor 51% gewijzigd door CH4OS op 09-08-2006 20:32 ]

[Janoz]

Als we bij elk van je pogingen alles voor moeten gaan kauwen wordt dit niks meer dan een bij het handje neem topic. Aan je queries te zien heb je de join syntax nog niet helemaal door. Aangezien dit keurig in de handleiding (en vele anderesql documentatie en tutorials) voorkomt lijkt het mij daarom ook handiger wanneer je daar eerst eens in gaat kijken.

mbt je foutmelding? user_rooms komt helemaal nergens in je query voor. Daarnaast vind ik het vreemd dat je al je tabellen dubbel doet. Eerst achter de from en vervolgens nog een keer hieraan gejoined.