Include PHP code in Wordpress Page

I want to query a database and include the output in a table on a Wordpress page. I have the PHP code that queries the database, and it works correctly, but I can't figure out how to output that onto my Wordpress page.

I've looked into several widgets, like InsertPHP, but I can't figure out exactly how they work.
LVL 86
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David FavorLinux/LXD/WordPress/Hosting SavantCommented:
Best to inject your code into a simple plugin.

Also, if you're new to WordPress database access, be sure you're using the correct method of data access, else your code may fail in subtle ways (reading) or corrupt tables (writing).

https://wordpress.stackexchange.com/questions/53819/how-does-wpdb-differ-to-wp-query provides a good overview of the differences between $wpdb + wp_query().

There's also a list of external/additional resources which goes even deeper about when to use which approach.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareAuthor Commented:
I might be wrong, but I believe that has to do with accessing the Wordpress databases. I don't want to do that. I'm accessing data from a standard MySQL database that is hosted on the same machine as the Wordpress stuff.
0
David FavorLinux/LXD/WordPress/Hosting SavantCommented:
Then you'll write your code to adhere to the database tables you're working with (locking, etc...) + just pull the data from your database + inject into WordPress content, however seems best.

You're not really inserting PHP, you're inserting data from MySQL calls.

So...

You'll write a plugin to abstract access to this data + provide the data to WordPress. Think simple abstraction layer or API.

How you do this depends on... many factors...

Likely you'll just use your theme or other plugin hooks/filters to pull data from your plugin... or rather your plugin will insert itself via one of these hooks/filters into your site's data flow, so will just be another producer of data for some hook or filter.
0
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareAuthor Commented:
Right, I just want to output the data from my PHP script to the page.

Any tutorials on writing a plugin for Wordpress? Anybody already got one that does this?
0
Chris StanyonWebDevCommented:
Writing a simple plugin is very easy. Have a look at the following. It's a very basic example:

<?php
/**
 * Plugin Name: My Lovely Plugin
 * Description: This plugin will add a shortcode to WordPress to display a table of records.
 * Version: 1.0
 * Author: Chris Stanyon
 * Author URI: http://www.example.com
 */

add_shortcode( "cs-db", "display_my_db" );

function display_my_db() {

    $mydb = new wpdb('username','password','yourDb','localhost');
    $query = "SELECT firstname, lastname, email FROM yourTable";
    $results = $mydb->get_results($query);

    if (count($results)):
        echo "<table>";
        foreach ($results as $row):
        ?>
            <tr>
                <td><?= $row->firstname ?></td>
                <td><?= $row->lastname ?></td>
                <td><?= $row->email ?></td>
            </tr>
        <?php
        endforeach;
        echo "</table>";
    endif;
}

Open in new window

Create a folder under wp-content/plugins with a unique name for your plug-in: sm-table-display for example. Now create a file in that folder called something similar:  sm-table-display.php and drop in the code I've posted.

Now login to your site and head to the Plugins section. Find your plugin in the list and Activate it.

The code I posted simply gives you a shortcode to add to your pages (cs-db in my example), and when added to a page, it'll run the function and display your data. Add a new page to your site, and add the shortcode to it:

[cs-db]

save the page and view it - you should see your table displayed.

That's a very basic example and you'd likely want to add your own checks and balances in there. Google around - there's a lot of info out there on how to create plugins :)
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareAuthor Commented:
Hi Chris,

I tried to create the plugin but it's not recognized by Wordpress. I did the following:

1. In the "public_html/wp-content/plugins" section of the site I created a folder named "TOTY"
2. In that folder I added a simple file with the name "toty.php"
3. I went back to the Wordpress site and logged in
4. I checked for the plugin there, but didn't find it.

Did I do something wrong?
0
Chris StanyonWebDevCommented:
Hey Scott,

You need to make sure your toty.php has at least the bare minimum to be recognosed as a plugin. That's done with the comments at the start of the file, and you have to have at least a Plugin Name entry. You can call it what you like:

<?php
/**
 * Plugin Name: TOTY
 */

// now your code goes here :)

Open in new window

You can add more fields to that, and if you look at my example from ealier you'll see I added in a Description and Version etc. This will all show up on the Plugin screen.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareAuthor Commented:
That was the reason my plugin wasn't showing up - I didn't include those items in the comments (I thought they were just comments).

It seems to be working to some degree now. In my Wordpress page I see the table I'm returning from my plugin, but the page is formatted oddly. I want to have some text on my page, and then show the output from my plugin, but the page is showing the data from the plugin, and then the text AFTER that data:

http://www.bbqga.org/team-of-the-year-2/

On that page, I see the table for TOTY points, and AFTER that I see the heading of the page (i.e. the logo and verbiage should show above that table). Here's the markup I've put in on my Wordpress edit page:
<h5 style="text-align: center;"><strong><span style="color: #ff0000;"><a href="http://www.bbqga.org/wp-content/uploads/2011/09/logored-e1316922077847.jpg"><img class="alignleft size-thumbnail wp-image-304" title="logored" src="http://www.bbqga.org/wp-content/uploads/2011/09/logored-144x150.jpg" alt="" width="121" height="126" /></a>
A.K. Saville Team of the Year for 2018-2019</span></strong></h5>
<h2 style="text-align: center;"><strong><span style="color: #ff0000;">Sponsored by:</span></strong></h2>
<img class="aligncenter size-medium wp-image-665" src="http://www.bbqga.org/wp-content/uploads/2012/01/stumplogo-300x76.jpg" alt="stumplogo" width="300" height="76" />

The GBA presents awards to the top 20 teams for each contest year. The top 10 teams are also awarded a cash prize, and the top 3 teams in each Category (Loin, Pork, and Ribs) are also awarded banners and cash.

<h5>Current Standings</h5>
[show-toty]

Open in new window


Here's what in the show-toty plugin:

<?php
/**
 * Plugin Name: TOTY
 */
add_shortcode( "show-toty", "ShowTOTY" );
Function ShowTOTY() {
$db = mysqli_connect('localhost', 'xxxxxx','zzzzz','yyyyyyyy') or die('Error connecting to MySQL server.');
$query ="select t.Team_Name, SUM(cp.Points) AS TotalPoints from Contest_Points cp inner join Teams t on cp.Team_ID=t.Team_ID GROUP BY Team_Name ORDER BY SUM(cp.Points) DESC, Team_Name";
mysqli_query($db, $query) or die('Error querying database.');
$result = mysqli_query($db, $query);

echo "<table border='1'>";
while ($row = mysqli_fetch_array($result)) {
 echo "<tr>";
 echo "<td>" . $row['Team_Name'] . "</td>";
 echo "<td>". $row['TotalPoints'] . "</td>";
 echo "</tr>";
}
echo "</table>";
mysqli_close($db);
}
?>

Open in new window

0
Chris StanyonWebDevCommented:
Ah right. I forgot about that. Basically, a shortcode function should return the data, not echo it directly. We need to use output buffering to effectively store the output in a buffer and then return that.

function ShowTOTY() {
    $db = mysqli_connect('localhost', 'xxxxxx','zzzzz','yyyyyyyy') or die('Error connecting to MySQL server.');
    $query ="select t.Team_Name, SUM(cp.Points) AS TotalPoints from Contest_Points cp inner join Teams t on cp.Team_ID=t.Team_ID GROUP BY Team_Name ORDER BY SUM(cp.Points) DESC, Team_Name";
    $result = mysqli_query($db, $query) or die('Error querying database.');

    ob_start();

    echo "<table border='1'>";
    while ($row = mysqli_fetch_array($result)) {
        echo "<tr>";
        echo "<td>" . $row['Team_Name'] . "</td>";
        echo "<td>". $row['TotalPoints'] . "</td>";
        echo "</tr>";
    }
    echo "</table>";
    mysqli_close($db);

    return ob_get_clean();
}

Open in new window

By starting the output buffering ( ob_start() ) before your actual html, we can then return the contents of the buffer by calling ob_get_clean() at the end.

Couple of points about your code. Firstly, you should really be using the built in WordPress wpdb class to access your database. Look at my example from ealier - you'll see it uses wpdb instead of a direct connection using mysqli. It will ensure full compatibility with the WordPress platform. Also, the border property of  <table> is not supported in HTML 5 - you should really use CSS for that instead
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareAuthor Commented:
I understand what you mean. I could modify my function to return my data only (perhaps in an array??)? If so, how would I manage that in my Wordpress page, if I can't run PHP code on that page?

Thanks for your other points. I'm just learning about Wordpress, and appreciate any input that makes my code more compliant. I'll work on modifying those items.
0
Chris StanyonWebDevCommented:
Hey Scott,

You don't need to worry about returning an array - you just need to return the output (the HTML that you want to display), which is what we've acheived by using the output buffering functions. By using those, we store the html output into the buffer and then return it at the end of the function, instead of echoing it directly to the page.

You could just as easily store the HTML in a variable and return that instead:

$html = "<table">;

foreach ($results as $row):

    $html .= sprintf("<tr><td>%s</td><td>%s</td></tr>", $row->Team_Name, $row->TotalPoints);

endforeach;

$html .= "</table>";

return $html;

Open in new window

0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareAuthor Commented:
Thanks for your help Chris and David. I have one last question.

When I add my plugin and edit my page on Wordpress, the code is executed, and I see the updated values. However, if I make changes to the data in the database, the data on my Wordpress page does not update until I go into the Wordpress editor and actually update and publish it.

New data will be added to the database periodically, and I'd like for the page to show the new data - that is, run the query each time the page is loaded so that I don't have to manually update and publish when new data is added.

How can I do that? Or should I ask this in a new question?
0
Chris StanyonWebDevCommented:
That could be down to a caching issue. Do have any caching plugins installed? Under 'normal' circumstances you plugin code should run each time you load the page and therefore pull fresh info from the DB.
1
David FavorLinux/LXD/WordPress/Hosting SavantCommented:
Tip: During development completely disable PHP Opcode caching + all WordPress caching plugins.

After your code is working, enable Opcache + test thoroughly. If this works, move on to enabling your WordPress caching plugin + retest.

Keep in mind, if you're operating outside normal WordPress DB activities, you may have to write your own custom cache invalidation functions, to invalidate the cache whenever new data is available.

I suggest you close out this question now + do the testing I suggested + if problems still occur, then open another question about having your custom code interact with your specific caching plugin.
0
David FavorLinux/LXD/WordPress/Hosting SavantCommented:
Another random tip.

Be sure to choose a caching plugin which provides plumbing for manual cache invalidation. Some plugins do + some don't.

For example, https://www.wpfastestcache.com/tutorial/delete-the-cache-by-calling-the-function/ describes how to accomplish manual cache invalidation using WP Fastest Cache.

After you're ready to enable your caching plugin again, choose a plugin which provides granular support for interacting with your cached data.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareAuthor Commented:
It was cached using Comet Cache. I disabled that, and it looks that was my issue. I'll have to learn more about caching and such, and figure out how to best setup my caching - but that's another question, of course.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareAuthor Commented:
Thanks to both of you. I took this over recently for a volunteer organization and I'm learning about Wordpress, so I'm sure I'll have more questions!
0
David FavorLinux/LXD/WordPress/Hosting SavantCommented:
Keep in mind Comet Cache uses pure PHP caching, so no mod_rewrite usage.

This means Comet Cache can only be used on very low traffic sites, as every request (cached or not) involves the entire LAMP Stack.

Other cache plugins, like WP Fastest Cache, provide true caching, where new requests generate static HTML files, then serve those files via mod_rewrite... so... best to use a true caching plugin, if you ever expect to see traffic on this site.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development

From novice to tech pro — start learning today.