PHP with WordPress, MySQL, and Forms

PHP with WordPress is not a common thing. WordPress is designed for developers as much as its designed for designers. So it becomes very interesting diving in to PHP with WordPress, not too terribly complicated after you figure out all the functions WordPress has built in,  though. The key to success, seems to be, having the right set of tools. There is so much available for wordpress, it’s unbelievable.

I’ve run into countless times already in WordPress where I’m unable to perform the PHP Functions or Database Operations that I’d like to on forms that are submitted. I’m going to walk you through one way how this can be done. The example code is going to be real life code that I’m currently working on, and it is unfinished, but it’s functional.

PHP WordPress

Plugins Needed

  • Contact Form 7
  • Advanced CF7 DB
  • Insert PHP Code Snippet

The Vision

In this case, I want to create a debt/ budget management system. This will be a simple system(at first), that assists the user with planning their budget. The user will fill out forms to add income, bills, and additional expenses. The data will be collected in a database, and displayed for the user and calculations will be performed on the data.

The Scope of This Post

This post will cover one of the modules of the system. This post will only explain the income portion including:

  • Installing and configuring plugins
  • The roles of each plugin
  • How the data is stored in the database
  • Adding income
  • Displaying income
  • Limiting display to only what the currently logged in user added
  • Calculating total weekly and monthly income
  • Creating a reusable set of functions

I plan on adding posts in the future to tie things together. For now, we’ll look at the getting started portion.

The Plugins

The plugins you’ll be using are Contact Form 7, Advanced CF7 DB, and Insert PHP Code Snippet. I tried multiple plugins, before landing on these, and none of them worked well in combination like these plugins do. So I wouldn’t recommend straying away from the listed plugins.

Setup of these is self-explanatory, if you’ve never installed plugins before – look for a post about it by me, I just added it to the to do list.

Configuration, may be something that you need assistance with, though.

Contact Form 7 Install

Contact Form 7 configuration starts with simply installing the plugin. Search for it in the WordPress Plugins area, and click , then click . As far as actual configuration, there’s none needed, you simply install this one. We’ll go over form creation, in a minute. This plugin is the one we use to create the actual forms

Advanced CF7 DB also starts with installation. Search for it in WordPress Plugins, and click , then . There is no additional configuration with this either. You do need to know that nothing is available in this plugin, until you submit a form. So you must create a form, then submit a test submission. This plugin adds the submitted forms to the database, Contact Form 7 alone does not do this.

Insert PHP Code Snippet Install

Insert PHP Code Snippet is the exact same procedure. Add new plugin, search for Insert PHP Code Snippet, click , then click . This plugin adds the ability to insert PHP code, that you create, anywhere in a page or post. This is super nice, if you have little functions you’d like to place in specific places. Otherwise using PHP with WordPress is much more manual, and complicated.

Where do we access these plugins?

In the image above, there are 3 options highlighted in yellow. These are the 3 plugins that we just installed. Throughout this tutorial, we pretty much start at the top of the 3 plugins highlighted in the picture, and work our way down. Lets dive right in, with Contact Form 7!

Contact Form 7

Setting up the form will be made as simple as possible here. Unfortunately Contact Form 7 isn’t a drag and drop interface, but this does allow us to copy and paste a form into the editor. Here is the code:

[hidden user-name default:user_login]
<label> Income Name (required)
    [text* income-name] </label>

<label> Take Home  (required)
[number* income-amount] </label>

<label> Note to Self
[text notes] </label>

[submit "Add"]

to a snippet of more short codes. Pretty smart, if you ask me. I’ll only explain 2 of them in some depth, the rest follow the same pattern.

[hidden user-name default:user_login]

What does that do? Let’s take it word for word.

hidden means just that, it’s a hidden field. Hidden fields are hidden from the user, they exist with whatever value you’d like it to hold. You could have it hold a time or time zone value for the person filling it out, so you’re not using server time for example. In this case, we’re having it fill in the currently logged in user’s username. This is what we’ll use to make sure we only display data that this user has entered.

user-name is simply the name that we’re giving the field. This is what it will be named in the database, so it’s a very important part of our code.

default:user_login is a global variable that is handled by WordPress. This is a very simple way of saying:here’s a session id, what is the use id associated with that, and what is the user name associated with that id? K set that as the default value of this field. I prefer the 3 word short code. Imagine all the words I just typed multiplied by PHP and SQL, that’s the beauty of short codes. PHP with WordPress is amazing!

[text* income-name]

I bet you can guess what this one does. Yup, text field. The significance of text* is the asterisk. Marking it with an asterisk sets it as a required field. In this case all of our fields are required, except the hidden one. This is to avoid errors caused by a field that the user cannot see.

[number* income-amount]

The last one we’re looking at before we move on, is the number field. I specify number, so the form will not allow anything but numbers to go in this area. This will avoid any errors in the future with calculations. If somebody decides they want to put a dollar sign, or anything extra, in this area – it would mess up the PHP script, and they would blame you.

Make the form

Before you get sick of me typing, let’s get on to the form! Copy the code above and paste it in the box, just like the following image:

new contact form

After pasting your code in there, click the save button on the bottom left. This will add your first form!

An error?

There will be an error in the mail tab. It’s a very very simple fix, though. Let’s click the mail tab at the top:

mail tab error

The following image is where you’ll find the error, within the mail tab.

additional headers error

Why are we getting this error?

We get the error in Contact Form 7, because we have modified the default template for the form. The [your-email] portion is trying to pull data from a field that doesn’t exist! So we’ll get rid of that issue, by simply putting in our own email.

additional headers fixed

And the error is gone! Let’s click save again, to make sure this error stays gone, and we can move on to the next step

Adding the form to a page

Adding the form is a pretty simple procedure. We’re not even to the point of using any PHP with WordPress yet. WordPress is an incredible creature, and we’ll be simply adding a short code to the page, to generate the form.

So where’s the shortcode?

You may have noticed, after the contact form was saved, that a short code popped up in a purple area on top of the form editor. This is going to be the short code that we will copy and paste to our new page, yours may have a different ID than mine, so don’t copy mine. Here’s where you’ll find it:

contact form short code

Find that code, and copy it for the next step.

Let’s create a new page! This can be done by mousing over the new button, in the top navigation bar of your WordPress site, and clicking page.

new page

If you’re this far into it, you probably already know how to make a new page. I’m just including these images to avoid any confusion, or misinterpretation of what I’m saying. Now that we’re in the create a page area, simply paste the short code in to the page editor area and click Publish. This is what mine looks like:

add form to page

Now that we have published it, let’s preview it, and add our first entry! We will need an entry to work with, before we can have any data displayed in a table. After you input a Income Name, Income Amount, and Notes if you want to add them, click  add. This is what your form should look like:

add weekly income

After you click the add button, you’ll get a message saying thank you, your message has been sent. That’s because this is normally a subscriber form, we’ll learn how to remove this and redirect to a new page in a different post. If you’ve gotten this far, success! You’re super close to being done.

Advanced CF7 DB

Here’s the tough part, Advanced CF7 DB. Database work is terrifying! Not with this addon, though. Seriously, there’s nothing to do. If you want to verify that the entry went in the database, you can do it here. That’s all we have to do with it, it does it’s own thing! Here’s my DB entry, you can check it out to see if it went in, just like I did.

verify database entry

That’s it, for real. Nothing to do. Next!

PHP Code Snippets

K, here we’ll finally add some PHP code to WordPress. Click on XYZ PHP Code, on your left hand navigation. When that loads up, we’ll click add new php code snippet. That will get the ball rolling. We’re going to start with the Table Styling. That will put some color to our table. Here’s what the input looks like:

add php code

You’ll want to copy the exact code, and names that I use. The functionality depends on that. Feel free to ask questions, if you have any. Here’s the code we’ll use, in between each chunk of code, you will click  and go start a new one. This code is released under the 2-Clause BSD license, details can be found here. Here are the blocks of code:

Tracking Name: Table-Style

//Copyright 2018 Your Epic Tech
//Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:
//1. Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.
//2. Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.
//THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

<?php
?>
<style>
.datagrid table { border-collapse: collapse; text-align: left; width: 100%; } .datagrid {font: normal 12px/150% Arial, Helvetica, sans-serif; background: #fff; overflow: hidden; border: 1px solid #006699; -webkit-border-radius: 3px; -moz-border-radius: 3px; border-radius: 3px; }.datagrid table td, .datagrid table th { padding: 3px 10px; }.datagrid table thead th {background:-webkit-gradient( linear, left top, left bottom, color-stop(0.05, #006699), color-stop(1, #00557F) );background:-moz-linear-gradient( center top, #006699 5%, #00557F 100% );filter:progid:DXImageTransform.Microsoft.gradient(startColorstr='#006699', endColorstr='#00557F');background-color:#006699; color:#FFFFFF; font-size: 15px; font-weight: bold; border-left: 1px solid #0070A8; } .datagrid table thead th:first-child { border: none; }.datagrid table tbody td { color: #00496B; border-left: 1px solid #E1EEF4;font-size: 12px;font-weight: normal; }.datagrid table tbody .alt td { background: #E1EEF4; color: #00496B; }.datagrid table tbody td:first-child { border-left: none; }.datagrid table tbody tr:last-child td { border-bottom: none; }.datagrid table tfoot td div { border-top: 1px solid #006699;background: #E1EEF4;} .datagrid table tfoot td { padding: 0; font-size: 12px } .datagrid table tfoot td div{ padding: 2px; }.datagrid table tfoot td ul { margin: 0; padding:0; list-style: none; text-align: right; }.datagrid table tfoot  li { display: inline; }.datagrid table tfoot li a { text-decoration: none; display: inline-block;  padding: 2px 8px; margin: 1px;color: #FFFFFF;border: 1px solid #006699;-webkit-border-radius: 3px; -moz-border-radius: 3px; border-radius: 3px; background:-webkit-gradient( linear, left top, left bottom, color-stop(0.05, #006699), color-stop(1, #00557F) );background:-moz-linear-gradient( center top, #006699 5%, #00557F 100% );filter:progid:DXImageTransform.Microsoft.gradient(startColorstr='#006699', endColorstr='#00557F');background-color:#006699; }.datagrid table tfoot ul.active, .datagrid table tfoot ul a:hover { text-decoration: none;border-color: #006699; color: #FFFFFF; background: none; background-color:#00557F;}div.dhtmlx_window_active, div.dhx_modal_cover_dv { position: fixed !important; }
</style>

Tracking Name: Functions

//Copyright 2018 Your Epic Tech
//Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:
//1. Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.
//2. Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.
//THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

<?php
//****************************************************************************
//***This function gets all id's for things the user submitted with forms.***
//***This function also filters id's that aren't for the table being built.******
//****************************************************************************
	function dbgetdataids($username,$filter){
                global $wpdb;
		$results= $wpdb->get_results ( "SELECT DISTINCT data_id FROM " . $wpdb->prefix . "cf7_vdata_entry WHERE name='user-name' AND value='" .  $username . "'");
$i=0;
foreach ($results as $result) {
$wpdb->get_results ( "SELECT data_id FROM " . $wpdb->prefix . "cf7_vdata_entry WHERE name='" . $filter . "' AND data_id='" . $result->data_id . "'");
if ($wpdb->num_rows == 0){
unset($results[$i]);
}
$i++;
}
return $results;
	}
//****************************************************************************
//***************This function gets individual entries by id.*****************
//****************************************************************************
	function dbgetentry($name,$id){
                global $wpdb;
		$name = $wpdb->get_results ( "SELECT value FROM " . $wpdb->prefix . "cf7_vdata_entry WHERE name='" . $name . "' AND data_id='" .  $id . "'" );
return $name;
	}
	
//****************************************************************************
//***This function builds the 3 column table, with as many rows as exist.**
//****************************************************************************
function buildtable($username,$namevar,$amountvar,$notesvar,$tablelabel1,$tablelabel2,$tableheader1,$tableheader2,$tableheader3){
                $c = true;		
//**Building Columns
	        $dataid = dbgetdataids($username,$amountvar);
		echo "<div class=\"datagrid\"><table><thead><tr><th>" . $tableheader1 . "</th><th>" . $tableheader2 . "</th><th>" . $tableheader3 . "</th></tr></thead>";

//**Building Rows**
foreach ( $dataid as $id )   {
		$name = dbgetentry($namevar,$id->data_id);
		$amount = dbgetentry($amountvar,$id->data_id);
		$notes = dbgetentry($notesvarvar,$id->data_id);

	        if ($c) {echo "<tr class=\"alt\">";} else {echo "<tr>";}
	        echo "<td>";
		echo $name[0]->value;
                echo "</td><td>";
		echo $amount[0]->value;
		$totals = $totals + $amount[0]->value;
	        echo "</td><td>";
		echo $notes[0]->value;
	        echo "</td></tr>";
                $c = !$c; 
}  

	echo "<tfoot><tr><td colspan=\"3\"><div id=\"paging\"><span><ul><li><a>" . $tablelabel1 . "</a></li><li><a>";
        echo $totals;
	echo "</a></li></ul></span></div></td></tr>";
	echo "<tr><td colspan=\"3\"><div id=\"paging\"><span><ul><li><a>" . $tablelabel2 . "</a></li><li><a>";
	$totals = $totals*4;
        echo $totals;
	echo "</a></li></ul></span></div></td></tr></tfoot></table></div>";
}
?>

Tracking Name: Build-Income-Table

//Copyright 2018 Your Epic Tech
//Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:
//1. Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.
//2. Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.
//THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

<?php
	global $current_user;
        get_currentuserinfo();
	$username =  $current_user->user_login;	
        $tablename = "income-name";
	$tablelabel1 = "Weekly Income";
	$tablelabel2 = "Monthly Income";
	$tableamount = "income-amount";
	$tablenotes = "notes";
	$tableheader1 = "Income Name";
	$tableheader2 = "Income Amount";
	$tableheader3 = "Notes";
buildtable($username,$tablename,$tableamount,$tablenotes,$tablelabel1,$tablelabel2,$tableheader1,$tableheader2,$tableheader3);
?>

So you should have 3 PHP code snippets:

  • Table-Style
  • Functions
  • Build-Income-Table

Table-Style just makes the table pretty.

Functions provides a few necessary functions to gather the data, but not too much data, for the table.

Build-Income-Table builds the table, and calls the functions to gather the data and lay it out nicely.

php code snippets

Now what?

Next to each code snippet, you’ll see a short code. That’s what we’ll use to display the table on the page. 3 short codes. They need to go in correct order, Table-Style, Functions, Build-Income-Table.

You’ll want to copy these 3, and we’ll create another new page, as we did earlier! Paste those puppies into the content area, and let’s see what we get.

Success! You just lied to everybody, and told them you make $50,000 per week! I wish! Let me know if you have any ideas, or how you put this to work. I look forward to hearing about it! PHP with WordPress is not too shabby.

Join the newsletter

Subscribe

Subscribe to get our latest content by email.

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit

Leave a Reply

Your email address will not be published. Required fields are marked *