Reading data from a MySQL database using PHP

There are a couple of ways to use PHP code in WordPress posts and pages. You could use a plugin such as exec-php. It allows PHP code to be included using shortcodes. The problem I found with this plugin is that in can be difficult (or at least not obvious) to figure out what directory the PHP file is in and how to link other PHP files together. Making a simple plugin makes it easier to see what is going on.

Its really simple to make a plugin. Just add a folder in the wp-content/plugins folder and call it whatever you want (In this case I called it test_plug). Adding folders and files can’t be done through the dashboard but you could use a FTP client like filezilla to upload files to your server or just use the file transfer client provided by your hosting company. Inside this folder make a new php file (doesn’t matter what its called). In order for WordPress to recognise the plugin there is some code to be included as shown below:

The file is named main.php
The directory of this file is wp-content/plugins/test_plug/main.php

<?php
/*
Plugin Name: 	Test plugin
Plugin URI: 	www.wattnotions.com
Description: 	Plugin for testing 
Version: 		1.0
Author: 		shane
Author URI:		https://www.wattnotions.com 
License: 		GPL3
*/

?>

Now if you go to the WordPress dashboard > Plugins > Installed plugins you should see the plugin on that list. So now we can make a basic hello_world PHP program that will print the some text to the the screen. To do this, make a new file ( I called mine hello_world.php ) and place it in the same directory as test.php

So the directory of this new file is wp-content/plugins/test_plug/helloworld.php

Now we need to tell wordpress where this file is located. This is done by including the directory of this new file in the main.php file. This is shown below:

<?php
/*
Plugin Name: 	Test plugin
Plugin URI: 	www.wattnotions.com
Description: 	Plugin for testing random shit
Version: 		1.0
Author: 		shane
Author URI:		https://www.wattnotions.com 
License: 		GPL3
*/

include ( plugin_dir_path( __FILE__ ) . 'hello_world.php');


?>

The hello_world.php file is a simple function that prints text to the screen. In order to call this function from a post or a page we need to link this function to a shortcode. In this case the shortcode is called hello__func.

<?php
	function say_hello() {
		return "Hello, this function can be run from a page or post by using the shortcode [hello__func]";      
	}   
	
	add_shortcode( 'hello_func', 'say_hello');
	
?>

This function can be run in any page or post by using the shortcode [hello__func] (with one underscore instead of two). There is a big difference between using echo and return. If echo is used then the the text would be displayed at the very top of the post. If you want the text to appear where the shortcode was typed, use return.

Ok so now we can move on to reading some data from a database. I created a basic MySQL table with two columns, data_id and data_val. Data_id is the primary key, it get incremented by one every time a new entry is made. To add some random numbers to the database I used this Python script:

import _mysql
from random import randint

db=_mysql.connect(host="66.127.245.233",user="new_user3",
                  passwd="123456",db="testdb")
                  
for i in range(20):
	db.query("insert into rand_data (data_val) values(" + str(randint(0,100)) + ");")
	
db.query("select * from rand_data;")
print db.store_result()
                  
print db

There is a really useful example of accessing a MySQL database using PHP available here.

We are going to need to make a new php file to take care of connecting to the database and grabbing some data. I called mine connect_to_db.php . The path of this file also needs to be included in the main.php file like the hello_world.php file was.

Here is the code used to connect to the MySQL database, grab some data and put it into a nice table:

<?php

function connect_to_db() {
	$con=mysqli_connect("localhost","wattnoti_user","physics","wattnoti_testdb");
	// Check connection
	if (mysqli_connect_errno()) {
	  echo "Failed to connect to MySQL: " . mysqli_connect_error();
	}

	$result = mysqli_query($con,"SELECT * FROM rand_data");

	$html_table = '
	<table border="1">
	<tr>
		<th>data_id</th>
		<th>data_val</th>
	</tr>';

	while($row = mysqli_fetch_array($result)) {
	$html_table .= '
  	<tr>
  	<td>' . $row["data_id"] . '</td>
  	<td> '. $row["data_val"] .' </td>
  	</tr>';
	}

	$html_table .= '</table>';

	return $html_table;

	mysqli_close($con);
	
}
add_shortcode( 'grab_data', 'connect_to_db' );

?>

Here is the table displayed :

Google+

Shane Ormonde