Plugins or Tutorials for displaying data from SQL-db on WP-page?

Question

Plugins or Tutorials for displaying data from SQL-db on WP-page?

Phish

2017年3月28日 20:27

I have a custom weather-station on a Raspberry Pi 3 with a local LAMP-setup running. The sensordata is stored in it's own database. The job for Wordpress is just to have a easy (somewhat) WYSIWYG-possibility to edit the design of the display-page that is shown on a secondary Pi by using a fullscreen-browser.

I need now 3 somewhat "unique" functions that I couldn't find a proper solution for:

Shortcodes for the latest (real-time) data-entry of the database (like temp, humidity, windspeed, rainsensordata, rain-volume, etc) to make it noob-friendly to change the actuall placement of the data inside of the page.
I need a admin-backend (plugin, adminpanelentry, whatever) to chose either grabing the latest data from the database or entering a fixed value in the case of sensor-errors or other occasions that may need it. Would be nice to have: Saving of the manual entries in the database and having a dropdown-menu to chose from those. Nothing fancy, something like http://i.imgur.com/TbZMFMK.png
a "somewhat" easy way to add new data-values (if a new sensor is added)

Data-aquesition is done with Python writing to the database. Would it be more easy to let Python write the data into the Wordpress-DB directly in it's own table? Though perhaps there is already software in case of plugins that do what I need already?

Thanks!

Topic get-plugin-data database plugins Wordpress

Category Web

brianjohnhanna · Accepted Answer · 2017年3月28日 20:27

This is a pretty vague and broad question, but I'll give it a shot. You can add the following code into a functionality plugin or if you're running a theme, you could place it in your theme's functions.php file. (Obviously you'll have to change the SQL, attributes, etc - just a proof of concept)

add_shortcode( 'wpse_weather_data', 'wpse_weather_data_shortcode_cb' );
function wpse_weather_data_shortcode_cb( $atts ) {
    $atts = shortcode_atts( array(
        'fallbackValue' => '',
        'sensor' => 0
     ), $atts );

     extract( $atts );

     // Make the connection to a separate database using PHP. 
     // You might want to store these credentials as constants in wp-config.php 
     // to keep all the db credentials in one place.
     $db = mysqli_connect("127.0.0.1", "my_user", "my_password", "my_db");
     if ( !$db ) {
         return $fallbackValue;
     }

     // Make the query
     $query = "SELECT * FROM table_name WHERE sensor = {$sensor}";
     $result = mysqli_query($db, $query);
     if ( !$result ) {
         return $fallbackValue;
     }

     $row = mysqli_fetch_array($result);
     // Put the data on the page... FYI you have to return the data from a shortcode, not echo it.
}

Building an options page, storing past values in a database, etc are way beyond the scope of this forum, but this would give you a way to get started. You can customize it (per #3) by adding different attributes to the shortcode. For example, here you can add additional sensors and specify a fallback value by placing the shortcode somewhere in the content and calling it like so: [wpse_weather_data sensor="0" fallbackValue="Could not fetch sensor data"]

Hope this steers you in the right direction.

Plugins or Tutorials for displaying data from SQL-db on WP-page?

About