Nieuws:

We zijn er weer.

Na lange tijd van afwezigheid zijn we er weer  :laugh:
We hebben alle wachtwoorden gereset, je oude wachtwoord werkt niet meer.Je moet via het "wachtwoord vergeten"-linkje je wachtwoord resetten. Je krijgt hiervoor een mailtje op het adres dat je bij ons geregistreerd hebt.

De komende tijd zijn we nog druk bezig om de rest van de site op te bouwen, dus het kan zijn dat sommige onderdelen (tijdelijk) niet werken.

Welkom, Gast. Alsjeblieft inloggen of registreren.
Heb je de activerings-mail niet ontvangen?

Auteur Topic: [Opgelost] MySQL vraag forumdatabase  (gelezen 2219 keer)

Offline Johan van Dijk

  • Administrator
    • johanvandijk
  • Steunpunt: Nee
[Opgelost] MySQL vraag forumdatabase
« Gepost op: 2012/04/29, 15:43:53 »
Ik heb ook een vraag mbt MySQL.
Sinds we van forumsoftware veranderd zijn, is er in de database ook het één en ander veranderd. Er zijn tabel- en kolomnamen veranderd, bijgekomen enz. Dat is niet echt het probleem.
Er zijn ook waarden verplaatst en op een onhandigere manier ingedeeld.

Voor het maken van de steunpuntenkaart werd altijd onderstaande query gebruikt:
SELECT id_member, real_name, latitude, longitude, is_supportpoint FROM smf.smf_members WHERE latitude IS NOT NULL ORDER BY id_memberDit leverde dan ongeveer dit op:
+-----------+---------------------+---------------------+---------------------+-----------------+
| id_member | real_name           | latitude            | longitude           | is_supportpoint |
+-----------+---------------------+---------------------+---------------------+-----------------+
|         2 | Dennis Kaarsemaker  |  52.512864885511505 |   5.474442601116607 |               1 |
|         4 | nicolas             |  52.216600000000000 |   5.483300000000000 |               0 |
|        70 | kennywest           |  50.999253662381100 |   3.759126663208010 |               0 |

De hele kolom "is_supportpoint" is er nog wel, alleen ontbreekt in de forumsoftware de mogelijkheid om die weer te geven of aan te passen. De forumsoftware aanpassen kan wel, maar dan moeten een groot aantal bestanden ingrijpend veranderd worden. En dat is niet wat we willen.

Er is wel een tweede mogelijkheid bijgekomen om custom profielvelden te maken, alleen maakt dat de query's nogal onhandig. Die extra profielvelden worden in de tabel voor de thema-instellingen bijgehouden:
SELECT id_member, variable, value FROM smf.smf_themes WHERE id_member = 1830+-----------+----------------------+------------------------------------------------------+
| id_member | variable             | value                                                |
+-----------+----------------------+------------------------------------------------------+
|      1830 | show_board_desc      | 0                                                    |
|      1830 | show_children        | 0                                                    |
|      1830 | show_no_avatars      | 0                                                    |
|      1830 | show_no_signatures   | 0                                                    |
|      1830 | return_to_post       | 1                                                    |
|      1830 | cust_launch          | johanvandijk                                         |
|      1830 | cust_st              | Ja                                                   |
+-----------+----------------------+------------------------------------------------------+
Dit is de query om de voorkeuren van mijn account op te vragen (ik heb geknipt in de resultaten), het gaat dan om de variabele "cust_st".

De vraag is nu welke query er gedaan moet worden, zodat er van ieder lid zijn voorkeur gegeven wordt?
Wat ik graag aan resultaten wil is dit:
+-----------+---------------------+---------------------+---------------------+-----------------+
| id_member | real_name           | latitude            | longitude           | value           |
+-----------+---------------------+---------------------+---------------------+-----------------+
|         2 | Dennis Kaarsemaker  |  52.512864885511505 |   5.474442601116607 |              ja |

Het enige probleem is dat niet voor ieder lid de variabele "cust_st" bestaat. In dat geval zou de kolom "value" het liefst nee moeten bevatten, om nog meer veranderingen in bestaande scripts te voorkomen.
Verder lijkt het me dat de query op de extra database ook een stuk zwaarder zal zijn voor de server. Kan iemand aangeven hoeveel zwaarder?
Het voordeel van deze methode is dat de voorkeur ook zonder veel problemen in het profiel opgenomen kan worden en dat men er zelfs op zou kunnen zoeken.

Nog weer een andere optie is om zelf een extra php-pagina te maken die dmv SSI.php die bij de forumsoftware zit die de oude vertrouwde "is_supportpoint"-kolom aanpast. Zo kan een lid zelf de functie aan- en uitzetten zonder dat de hele forumsoftware op de schop moet.
Het voordeel van deze manier is dat de externe scripts niet verder aangepast hoeven te worden.

Heeft iemand enig inzicht in wat de handigste manier is om dit probleem aan te pakken?
« Laatst bewerkt op: 2012/06/14, 09:07:24 door Johan van Dijk »

Offline Thomas de Graaff

  • Lid
  • Steunpunt: Nee
Re: MySQL vraag forumdatabase
« Reactie #1 Gepost op: 2012/04/30, 01:51:33 »
Ik denk dat je voor je eerste vraag over de query iets in deze richting nodig hebt, hoewel ik het niet getest heb, en het me niks zou verbazen als er een fout in de query zit. :)
SELECT a.id_member, a.real_name, a.latitude, a.longitude, REPLACE (b.value, NULL, 'Nee') FROM smf.smf_members a INNER JOIN smf.smf_themes b WHERE a.id_member = b.id_member AND b.variable = cust_st AND a.latitude IS NOT NULL ORDER BY a.id_member
Ik denk dat de extra belasting voor de server wel mee zal vallen. Het is volgens mij op deze wijze nog steeds met één query op te lossen.

Over de andere optie met een extra php pagina kan ik geloof ik niks zeggen.

Offline Rachid

  • Vertaalteam
    • rachidbm
    • Mijn blog
  • Steunpunt: Nee
Re: MySQL vraag forumdatabase
« Reactie #2 Gepost op: 2012/06/12, 08:33:50 »
Is het al opgelost?
Of heb je nog ergens hulp bij nodig ;)
Ben je ook blij dat Ubuntu zo toegankelijk en gratis is, en wil je graag net als ik iets terugdoen, kijk dan eens rond bij mwanzo, dé poort naar het bijdragen aan Ubuntu en haar gemeenschap!

Offline Johan van Dijk

  • Administrator
    • johanvandijk
  • Steunpunt: Nee
Re: MySQL vraag forumdatabase
« Reactie #3 Gepost op: 2012/06/12, 13:01:18 »
Het is nog niet opgelost helaas, dus als je nog ideeën hebt hoor ik ze graag :)

Offline Michael Anckaert

  • Lid
    • http://www.sinax.be
  • Steunpunt: Nee
Re: MySQL vraag forumdatabase
« Reactie #4 Gepost op: 2012/06/13, 11:08:19 »
De oplossing van Thomas ziet er goed uit, kan je vertellen wat hieraan niet werkt of goed is?
www.sinax.be
Open Source Integrators, consulting and custom development

Offline Johan van Dijk

  • Administrator
    • johanvandijk
  • Steunpunt: Nee
Re: MySQL vraag forumdatabase
« Reactie #5 Gepost op: 2012/06/13, 11:17:15 »
mysql> SELECT a.id_member, a.real_name, a.latitude, a.longitude, REPLACE (b.value, NULL, 'Nee') FROM smf.smf_members a INNER JOIN smf.smf_themes b WHERE a.id_member = b.id_member AND b.variable = cust_st AND a.latitude IS NOT NULL ORDER BY a.id_member;
ERROR 1054 (42S22): Unknown column 'cust_st' in 'where clause'

Dat is de output.

Offline erik1984

  • Lid
    • erik1984
  • Steunpunt: Nee
Re: MySQL vraag forumdatabase
« Reactie #6 Gepost op: 2012/06/13, 11:21:31 »
Misschien helpen quotes om "cust_st"? Schijnbaar ziet ie het nu als een kolom.

Offline Johan van Dijk

  • Administrator
    • johanvandijk
  • Steunpunt: Nee
Re: MySQL vraag forumdatabase
« Reactie #7 Gepost op: 2012/06/13, 11:41:03 »
Misschien is de vorm van de smf_themes tabel niet helemaal duidelijk.
Weer vanuit een test database:
mysql> select * FROM `smf_themes` WHERE `variable` = 'cust_st';
+-----------+----------+----------+-------+
| id_member | id_theme | variable | value |
+-----------+----------+----------+-------+
|      1830 |        1 | cust_st  | Ja    |
|     27768 |        1 | cust_st  |       |
+-----------+----------+----------+-------+
2 rows in set (0.00 sec)

Member 27768 is lid geworden na de invoering van het custom profielveld, maar heeft niets ingevuld. Van alle eerdere leden bestaat de variabele nog gewoon niet.

Offline Johan van Dijk

  • Administrator
    • johanvandijk
  • Steunpunt: Nee
Re: MySQL vraag forumdatabase
« Reactie #8 Gepost op: 2012/06/13, 11:43:11 »
En we komen weer verder:

mysql> SELECT a.id_member, a.real_name, a.latitude, a.longitude, REPLACE (b.value, NULL, 'Nee') FROM smf.smf_members a INNER JOIN smf.smf_themes b WHERE a.id_member = b.id_member AND b.variable = 'cust_st' AND a.latitude IS NOT NULL ORDER BY a.id_member;
+-----------+----------------+--------------------+-------------------+--------------------------------+
| id_member | real_name      | latitude           | longitude         | REPLACE (b.value, NULL, 'Nee') |
+-----------+----------------+--------------------+-------------------+--------------------------------+
|      1830 | Johan van Dijk | 52.194056336618000 | 3.985786825396600 | NULL                           |
|     27768 | johan-test     | 52.038976647644000 | 5.515136406250000 | NULL                           |
+-----------+----------------+--------------------+-------------------+--------------------------------+
2 rows in set (0.00 sec)

(inderdaad cust_st tussen ' ' gezet. :))

Offline Rachid

  • Vertaalteam
    • rachidbm
    • Mijn blog
  • Steunpunt: Nee
Re: MySQL vraag forumdatabase
« Reactie #9 Gepost op: 2012/06/13, 12:10:15 »
(inderdaad cust_st tussen ' ' gezet. :))
Dat zou je eigenlijk altijd moeten doen. Maar MySQL begint pas moeilijk te doen als er bijv. een _ in de naam zit.

edit: sorry, ik keek verkeerd. Je zocht op een string, dan moet het altijd tussen quotes.
« Laatst bewerkt op: 2012/06/13, 12:12:40 door Rachid »
Ben je ook blij dat Ubuntu zo toegankelijk en gratis is, en wil je graag net als ik iets terugdoen, kijk dan eens rond bij mwanzo, dé poort naar het bijdragen aan Ubuntu en haar gemeenschap!

Offline Rachid

  • Vertaalteam
    • rachidbm
    • Mijn blog
  • Steunpunt: Nee
Re: MySQL vraag forumdatabase
« Reactie #10 Gepost op: 2012/06/13, 12:16:44 »
Probeer ipv REPLACE anders eens
IFNULL(b.value, 'Nee');

Zie: http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_ifnull
Ben je ook blij dat Ubuntu zo toegankelijk en gratis is, en wil je graag net als ik iets terugdoen, kijk dan eens rond bij mwanzo, dé poort naar het bijdragen aan Ubuntu en haar gemeenschap!

Offline asphyxia

  • Forumteam
  • Steunpunt: Nee
Re: MySQL vraag forumdatabase
« Reactie #11 Gepost op: 2012/06/13, 14:02:27 »
Op het gevaar af dat ik hier onzin uitkraam:

Het probleem is, dat er een variabele bestaat ('cust_st') die voor een deel van het databestand niet bestaat. Is het mogelijk deze variabele te deleten, opnieuw te benoemen, en voor de hele database opnieuw te geven met als defaultwaarde 'nee'?
Dan hebben we in ieder geval een congruente database, het opnieuw invoeren van 'ja' bij de benodigde steunpunten is de volgende stap.

Zou kunnen door alle steunpunten opnieuw te laten invoeren, of het vanuit 'beste weten' met bv. het overzicht van femke98 handmatig te doen. Als ik dat lijstje eens beschouw, valt het wel mee met de grootte daarvan.

Misschien zijn we dan wel sneller klaar.
Disclaimer: ik heb geen IT-achtergrond, dus dit is zonder kennis van constraints die er ongetwijfeld zijn.
Als de werkelijkheid er niet was, zou de wereld er heel anders uitzien. [Theo Maassen]
Alles is te kraken, niets is veilig, zorg dus voor zoveel mogelijk niets. [Ramana]
It's always darkest before the dawn. [Florence Welch]

Offline Johan van Dijk

  • Administrator
    • johanvandijk
  • Steunpunt: Nee
Re: MySQL vraag forumdatabase
« Reactie #12 Gepost op: 2012/06/13, 14:29:24 »
Probeer ipv REPLACE anders eens
IFNULL(b.value, 'Nee');

Zie: http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_ifnull

mysql> SELECT a.id_member, a.real_name, a.latitude, a.longitude, IFNULL (b.value, 'Nee') FROM smf.smf_members a INNER JOIN smf.smf_themes b WHERE a.id_member = b.id_member AND b.variable = 'cust_st' AND a.latitude IS NOT NULL ORDER BY a.id_member;
+-----------+----------------+--------------------+-------------------+-------------------------+
| id_member | real_name      | latitude           | longitude         | IFNULL (b.value, 'Nee') |
+-----------+----------------+--------------------+-------------------+-------------------------+
|      1830 | Johan van Dijk | 52.194056336618000 | 3.985786825396600 | Ja                      |
|     27768 | johan-test     | 52.038976647644000 | 5.515136406250000 |                         |
+-----------+----------------+--------------------+-------------------+-------------------------+
2 rows in set (0.00 sec)

Ik heb je ook een opgeschoonde forumdatabase gemaild, met alleen de tabellen en kolommen die van belang kunnen zijn. Misschien dat dat iets makkelijker is.

@Asphyxia, dat is een idee ja. Ik ga eens kijken of het forum dat zelf kan, of dat we weer iets in de database moeten doen.

Offline Rachid

  • Vertaalteam
    • rachidbm
    • Mijn blog
  • Steunpunt: Nee
Re: MySQL vraag forumdatabase
« Reactie #13 Gepost op: 2012/06/13, 17:09:24 »
@Asphyxia, dat is een idee ja. Ik ga eens kijken of het forum dat zelf kan, of dat we weer iets in de database moeten doen.
Asphyxia kraamt geen onzin uit. Het is mogelijk en zo'n standaard waarde is in de praktijk zelfs makkelijker te realiseren dan Asphyxia beschrijft. Of dit handig is weet ik nog niet.

Ik ga de eerste post van Johan nog een keer rustig doorlezen en zal de verschillende mogelijkheden nagaan. Ook bijv. dat SSI.php verhaal.

Hier een beetje offtopic. Maar ik heb ook iemand horen roepen of kaart.ubuntu-nl.org niet helemaal los getrokken kan worden van het forum. Dus dat je je ook op dat domein ergens kunt aanmelden. Persoonlijk zie ik dat ook wel zitten. Dat is misschien ietsje meer werk. Maar dan is de kaart niet meer afhankelijk van een profiel op dit forum, en technisch gezien niet afhankelijk van SMF.
Ben je ook blij dat Ubuntu zo toegankelijk en gratis is, en wil je graag net als ik iets terugdoen, kijk dan eens rond bij mwanzo, dé poort naar het bijdragen aan Ubuntu en haar gemeenschap!

Offline SeySayux

  • Lid
    • SeySayux.net
  • Steunpunt: Nee
Re: MySQL vraag forumdatabase
« Reactie #14 Gepost op: 2012/06/13, 17:55:15 »
SELECT a.id_member, a.real_name, a.latitude, a.longitude, CASE WHEN b.value IS NULL THEN 'Nee' ELSE 'Ja' END FROM smf.smf_members a INNER JOIN smf.smf_themes b WHERE a.id_member = b.id_member AND b.variable = 'cust_st' AND a.latitude IS NOT NULL ORDER BY a.id_member;
I use a Unix-based system, that means I'll get laid as often as I have to reboot.
LibSylph
SeySayux.net

Offline profoX

  • Lid
    • wesley
    • Lionslink
  • Steunpunt: Nee
Re: MySQL vraag forumdatabase
« Reactie #15 Gepost op: 2012/06/13, 21:42:44 »
voor zover ik daar aan de output kan zien is de output de lege string "" in plaats van NULL?
dan zou je eerder REPLACE (b.value, '', 'Nee') willen gebruiken denk ik
in plaats van REPLACE (b.value, NULL, 'Nee') of IFNULL(b.value, 'Nee')
je mag me altijd een kleine SQL sturen dat ik het even fatsoenlijk uitwerk als dit niet klopt..
Human Knowledge Belongs To The World -- Antitrust (2001)
Nederlandstalige Ubuntu documentatie van Ubuntu-NL (wiki)

Offline SeySayux

  • Lid
    • SeySayux.net
  • Steunpunt: Nee
Re: MySQL vraag forumdatabase
« Reactie #16 Gepost op: 2012/06/13, 22:53:51 »
Hmm, inderdaad, b.value is niet NULL, maar gewoon de lege string, dus

SELECT a.id_member, a.real_name, a.latitude, a.longitude, CASE WHEN b.value == '' THEN 'Nee' ELSE 'Ja' END FROM smf.smf_members a INNER JOIN smf.smf_themes b WHERE a.id_member = b.id_member AND b.variable = 'cust_st' AND a.latitude IS NOT NULL ORDER BY a.id_member;

Of eventueel met replace zoals profoX voorstelt.
I use a Unix-based system, that means I'll get laid as often as I have to reboot.
LibSylph
SeySayux.net

Offline Johan van Dijk

  • Administrator
    • johanvandijk
  • Steunpunt: Nee
Re: MySQL vraag forumdatabase
« Reactie #17 Gepost op: 2012/06/14, 02:38:50 »
Bedankt allemaal!
Ik ga er weer even verder mee knutselen.

Offline Johan van Dijk

  • Administrator
    • johanvandijk
  • Steunpunt: Nee
Re: MySQL vraag forumdatabase
« Reactie #18 Gepost op: 2012/06/14, 05:26:00 »
Okee, we komen ergens :)
Wat Asphyxia al als probleem aangaf, is dat de waarden voor het custom profielveld in de smf_themes tabel niet automatisch voor alle leden aangemaakt wordt.
Ik heb dus even een query gebouwd die dat voor alle leden aanmaakt en de waarde van is_supportpoint uit de smf_members tabel gebruikt:

INSERT INTO `smf_themes` (`id_member`, `variable`, `value`)
SELECT `id_member`,'cust_st', CASE is_supportpoint WHEN '0' THEN 'Nee' ELSE 'Ja' END
FROM `smf_members`;

De kolom is_supportpoint bevat altijd een waarde voor ieder lid (default 0), dus dit lijkt me wel ok.

Nu ieder lid een waarde heeft voor cust_st, is het probleem in de query's van bovenstaande antwoorden ook opgelost, namelijk dat wanneer er geen waarde is voor cust_st er geen resultaat is voor dat lid.

Offline profoX

  • Lid
    • wesley
    • Lionslink
  • Steunpunt: Nee
Re: MySQL vraag forumdatabase
« Reactie #19 Gepost op: 2012/06/14, 07:41:24 »
Okee, we komen ergens :)
Wat Asphyxia al als probleem aangaf, is dat de waarden voor het custom profielveld in de smf_themes tabel niet automatisch voor alle leden aangemaakt wordt.
Ik heb dus even een query gebouwd die dat voor alle leden aanmaakt en de waarde van is_supportpoint uit de smf_members tabel gebruikt:
Is het probleem zo opgelost? Je had volgens mij trouwens ook gewoon een LEFT of RIGHT JOIN kunnen doen (afhankelijk van wat de exacte bedoeling eigenlijk was) om dat 'probleem' te overkomen. Maar als het nu allemaal prima werkt is dat wellicht ook goed! :)
Human Knowledge Belongs To The World -- Antitrust (2001)
Nederlandstalige Ubuntu documentatie van Ubuntu-NL (wiki)

Offline Johan van Dijk

  • Administrator
    • johanvandijk
  • Steunpunt: Nee
Re: MySQL vraag forumdatabase
« Reactie #20 Gepost op: 2012/06/14, 08:12:59 »
Ik heb niet zo heel veel kaas gegeten van databases en hoe ik exact de juiste resultaten krijg die ik wil.
Door handmatig bij iedereen de juiste waarde in te stellen lijkt het nu allemaal opgelost.
mysql> SELECT a.id_member, a.real_name, a.latitude, a.longitude, b.value FROM smf.smf_members a INNER JOIN smf.smf_themes b WHERE a.id_member = b.id_member AND b.variable = 'cust_st' AND a.latitude IS NOT NULL ORDER BY a.id_member LIMIT 0,10;
+-----------+---------------------+---------------------+---------------------+-------+
| id_member | real_name           | latitude            | longitude           | value |
+-----------+---------------------+---------------------+---------------------+-------+
|         2 | Dennis Kaarsemaker  |  52.512864885511505 |   5.474442601116607 | Ja    |
|         4 | nicolas             |  52.216600000000000 |   5.483300000000000 | Nee   |
|        70 | kennywest           |  50.999253662381100 |   3.759126663208010 | Nee   |
|        77 | J de vries          |  53.185156075782800 |   5.783915519714360 | Nee   |
|        79 | Niels van Everdinck |  52.088472389489100 |   4.352216720581050 | Nee   |
|        94 | Lfys                |  51.028844918408600 |   4.478430747985840 | Nee   |
|       106 | Dennis Laumen       |  50.988800413995500 |   5.850863456726070 | Nee   |
|       107 | Hein                |  51.723338405417470 |   5.499155044817599 | Ja    |
|       133 | Peter Lieverdink    | -37.799696442595800 | 144.946274757385000 | Nee   |
|       158 | mcgyver             |  51.576400000000000 |   4.644800000000000 | Nee   |
+-----------+---------------------+---------------------+---------------------+-------+

Offline Johan van Dijk

  • Administrator
    • johanvandijk
  • Steunpunt: Nee
Re: MySQL vraag forumdatabase
« Reactie #21 Gepost op: 2012/06/14, 08:43:45 »
Zo, je kan nu aangeven of je steunpunt wil zijn of niet :)

Volgende puntje op de lijst: de scripts die de kaart op kaart.ubuntu-nl.org maken aanpassen zodat ze de nieuwe velden gebruiken voor steunpunten.

Offline Rachid

  • Vertaalteam
    • rachidbm
    • Mijn blog
  • Steunpunt: Nee
Re: MySQL vraag forumdatabase
« Reactie #22 Gepost op: 2012/06/14, 08:50:22 »
Good busy!
Ben je ook blij dat Ubuntu zo toegankelijk en gratis is, en wil je graag net als ik iets terugdoen, kijk dan eens rond bij mwanzo, dé poort naar het bijdragen aan Ubuntu en haar gemeenschap!

Offline Johan van Dijk

  • Administrator
    • johanvandijk
  • Steunpunt: Nee
Re: MySQL vraag forumdatabase
« Reactie #23 Gepost op: 2012/06/14, 08:58:12 »
De scripts maken natuurlijk weer gebruik van de 0 of 1 waardes, maar dat staat niet mooi op het forum. Dus vandaar dat er gekozen is voor Ja/Nee.
De originele query:
SELECT id_member, real_name, latitude, longitude, is_supportpoint FROM smf.smf_members WHERE latitude IS NOT NULL ORDER BY id_member
Wordt nu dus dit:
SELECT a.id_member, a.real_name, a.latitude, a.longitude, CASE b.value WHEN 'Nee' THEN '0' ELSE '1' END FROM smf.smf_members a INNER JOIN smf.smf_themes b WHERE a.id_member = b.id_member AND b.variable = 'cust_st' AND a.latitude IS NOT NULL ORDER BY a.id_member;

En uiteraard is de naam van cust_st op de testserver door wat eerdere pogingen en tests natuurlijk weer anders dan op de echte server... zal je altijd zien...

Edit: scripts aangepast, kaart werkt nog. Mission accomplished ;)
« Laatst bewerkt op: 2012/06/14, 09:06:19 door Johan van Dijk »