Accessing MySQL Data in MediaWiki

This tutorial demonstrates how to access MySQL data in MediaWiki on a LAMP server.

  1. AWS EC2 t2.micro running AL2023, Apache 2.4.59, mySQL 15.1 (10.5.23-MariaDB), PHP 8.2.17 (installed with this tutorial)
  2. Configured DNS records
  3. Install SSL/TLS using this tutorial
  4. Configured Apache vhost.conf
  5. I was unable to install Certbot using snapd, but I was able to install with PIP.
  6. Install phpMyAdmin
  7. Install PHP extension: sudo dnf install php-intl
  8. Downloaded and extracted MediaWiki 1.41.1
  9. Replaced this file to have my own logo: resources/assets/change-your-logo.svg
  10. Created a database in MySQL with sample data
  11. Configure the external data sources within LocalSettings.php
    • Add a prepared statement for each SQL query
  12. Create a wiki page called Offices to list the offices
    1. Start by defining the data source: {{#get_db_data:
       |db = DataDemo
       |query = officeQuery
       |data=officeAddress=office_address, officeCity=office_city, officeState=office_state, officeZip=office_zip
      }}
      • Each wiki page that displays data from the External Data Source must have one or more #get_db_data statement(s)
      • The db value must match the ExternalDataSource in LocalSettings.php
      • The query value must match the prepared statement for the External Data Source in LocalSettings.php
      • The data values are a comma-separated list with the value before the equal sign as the Page Variable Name and the value after the equal sign matching the column name in the data returned by the prepared statement.
      • You may have multiple #get_db_data statements, but be sure to use a unique Page Variable Name if two or more queries reference the same table column.
      • To test the database connection, enter {{#external_value:officeCity}} and it will display the first City value.
    2. Create a table to display the data: {| class="wikitable"
       ! Office Address !! City !! State !! Zip
        {{#for_external_table:
          |{{!}}-
          {{!}} {{{officeAddress}}} {{!}}{{!}} [[{{{officeCity}}}]] {{!}}{{!}} {{{officeState}}} {{!}}{{!}} {{{officeZip}}}
        }}
      |}
      • Be careful with the curly braces - you might need one, two, or three, depending on its use.
      • I added [[angled brackets]] around the officeCity variable to make it a link so each office could have its own wiki page.
  13. Now, let's make a template for the Infobox that will appear on each office page.
    1. Create a new wiki page called Template:OfficeInfobox
    2. You do not need a #get_db_data:, because the template contains only variables
    3. The variable names in this template must match the variable names used on each Office page.
    4. Enter the following wiki source code: <div style="float: right; clear: right; margin: 0 0 1em 1em; width: 300px;">
      {| class="infobox" style="width: 100%; font-size: 90%; text-align: left; border: 1px solid #aaa; background: #f9f9f9; padding: 0.5em; line-height: 1em;"
      |-
      ! colspan="2" style="text-align: center; background: #f2f2f2; font-size: 1.25em; padding: 12px 0" | {{{office_Name}}}
      |-
      | style="padding: 1.5em 0.5em 0.5em 0.5em;" | Address:
      | style="padding: 1.5em 0.5em 0.5em 0.5em;" | {{{office_Address}}}
      |-
      | style="padding: 0.5em;" | City, ST Zip:
      | style="padding: 0.5em;" | {{{office_City}}}, {{{office_State}}} {{{office_Zip}}}
      |-
      | style="padding: 0.5em;" | Employees:
      | style="padding: 0.5em;" | {{{emp_Count}}}
      |-
      | style="padding: 0.5em;" | Manager:
      | style="padding: 0.5em;" | {{{mgr_First}}} {{{mgr_Last}}}
      |-
      | style="padding: 0.5em;" | Receptionist:
      | style="padding: 0.5em;" | {{{rec_First}}} {{{rec_Last}}}
      |}
      </div>
  14. Acquire a Google Maps API Key
  15. Install the Maps wfLoadExtension
    • Install composer: sudo dnf install composer
    • Navigate to same directory as LocalSettings.php and enter:
      composer update --no-dev
      composer require mediawiki/maps
    • Add to the bottom of LocalSettings.php:
      wfLoadExtension( 'Maps' );
      $egMapsDefaultService = 'googlemaps3';
      $egMapsGMaps3ApiKey = 'your_key';
  16. Edit the source of the Offices page to include a map: {{#display_map:
     center=United States
     | zoom=4
     | addresses={{#for_external_table:
     {{!}} {{!}} {{{officeAddress}}}, {{{officeCity}}}, {{{officeState}}} {{{officeZip}}}~Popup title~Popup text~~Group~[[{{{officeCity}}}]];
     }}
    }}
  17. Create a page for each office.
    1. Click on each office name in the table on the Offices page.
    2. Paste this on each page; the same wiki source code an be used as a starting point for all offices.
    3. In the line office_Name = {{#external_value:officeCity}}, office_Name must match the value in the Infobox template and officeCity matches the name before the equal sign in the data field of #get_db_date.
    4. The PAGENAME variable allows the office name to be used as a parameter in the SQL query. {{#get_db_data:
       |db=DataDemo
       |query=oneOffice
       |format=auto
        |data=officeAddress=office_address, officeCity=office_city, officeState=office_state, officeZip=office_zip
       |parameters={{PAGENAME}}
      }}{{#get_db_data:
       |db=DataDemo
       |query=employeeCount
       |format=auto
       |data=empCount=employee_count
       |parameters={{PAGENAME}}
      }}{{#get_db_data:
       |db=DataDemo
       |query=officeManager
       |format=auto
       |data=mgrFirst=emp_first, mgrLast=emp_last
       |parameters={{PAGENAME}}
      }}{{#get_db_data:
       |db=DataDemo
       |query=officeReceptionist
       |format=auto
       |data=recFirst=emp_first, recLast=emp_last
       |parameters={{PAGENAME}}
      }}{{OfficeInfobox
       | office_Name = {{#external_value:officeCity}}
       | office_Address = {{#external_value:officeAddress}}
       | office_City = {{#external_value:officeCity}}
       | office_State = {{#external_value:officeState}}
       | office_Zip = {{#external_value:officeZip}}
       | mgr_First = {{#external_value:mgrFirst}}
       | mgr_Last = {{#external_value:mgrLast}}
       | rec_First = {{#external_value:recFirst}}
       | rec_Last = {{#external_value:recLast}}
       | emp_Count = {{#external_value:empCount}}
      }}
      This page contains notes about the '''{{#external_value:officeCity}}''' office. View the [[Offices|List of Offices]].
      {{#display_map:
       center={{#external_value:officeAddress}}, {{#external_value:officeCity}} {{#external_value:officeState}} {{#external_value:officeZip}}
       | zoom=13
       | addresses={{#external_value:officeAddress}}, {{#external_value:officeCity}} {{#external_value:officeState}} {{#external_value:officeZip}}
      }}
  18. Create a page to list employees. {{#get_db_data:
     |db = DataDemo
     |query = employeeQuery
     |data = empFirst=emp_first, empLast=emp_last, empOffice=office_city, empRole=emp_role
    }}
    <div align="center">
     {| class="wikitable"
      ! Name !! Office !! Role
      {{#for_external_table:
       |{{!}}-
       {{!}} {{{empFirst}}} {{{empLast}}} {{!}}{{!}} [[{{{empOffice}}}]] {{!}}{{!}}
       {{#switch: {{{empRole}}}
        | 1 = Manager
        | 2 = Receptionist
        | #default = {{#if: {{{empRole|}}} | Unknown | }}
       }}
      }}
     |}
    </div>