[Opgelost] MySQL vraag forumdatabase

[Johan van Dijk]

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:

Code: [Selecteer]

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:

Code: [Selecteer]

+-----------+---------------------+---------------------+---------------------+-----------------+
| 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:

Code: [Selecteer]

SELECT id_member, variable, value FROM smf.smf_themes WHERE id_member = 1830

Code: [Selecteer]

+-----------+----------------------+------------------------------------------------------+
| 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:

Code: [Selecteer]

+-----------+---------------------+---------------------+---------------------+-----------------+
| 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?

[Thomas de Graaff]

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.

Code: [Selecteer]

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.

[Rachid]

Is het al opgelost?
Of heb je nog ergens hulp bij nodig

[Johan van Dijk]

Het is nog niet opgelost helaas, dus als je nog ideeën hebt hoor ik ze graag

[Michael Anckaert]

De oplossing van Thomas ziet er goed uit, kan je vertellen wat hieraan niet werkt of goed is?

[Johan van Dijk]

Code: [Selecteer]

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.

[erik1984]

Misschien helpen quotes om "cust_st"? Schijnbaar ziet ie het nu als een kolom.

[Johan van Dijk]

Misschien is de vorm van de smf_themes tabel niet helemaal duidelijk.
Weer vanuit een test database:

Code: [Selecteer]

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.

[Johan van Dijk]

En we komen weer verder:

Code: [Selecteer]

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. )

[Rachid]

(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.

[Rachid]

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

[asphyxia]

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.

[Johan van Dijk]

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

Code: [Selecteer]

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.

[Rachid]

@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.

[SeySayux]

Code: [Selecteer]

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;


[profoX]

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..

[SeySayux]

Hmm, inderdaad, b.value is niet NULL, maar gewoon de lege string, dus

Code: [Selecteer]

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.

[Johan van Dijk]

Bedankt allemaal!
Ik ga er weer even verder mee knutselen.

[Johan van Dijk]

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:

Code: [Selecteer]

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.

[profoX]

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!

[Johan van Dijk]

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.

Code: [Selecteer]

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   |
+-----------+---------------------+---------------------+---------------------+-------+

[Johan van Dijk]

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.

[Rachid]

Good busy!

[Johan van Dijk]

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:

Code: [Selecteer]

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:

Code: [Selecteer]

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