FREE, Simple process to Create, Read, Update and Delete form data (CRUD) into your own database tables in a WordPress site, without the use of Plugins.
Example is to create a site to manage your workout activity.
Create a table to store the data.
- Using phpMyAdmin, create the following table in your wordpress site database
Create a child theme of the theme you wanna use. I am using Child Theme Configurator
Now add the following to the functions.php file of your theme. This will handle form submissions.
//
// workout form processing
//
add_action( ‘template_redirect’, function() {
//make sure its our form
//
if (isset($_POST[‘workoutformbtn’]) && wp_verify_nonce($_POST[‘tuokrow_etaerc’], ‘create_workout’)) {
// these next 4 lines are for degugging, they write to php_error.log file
// comment them out when you’re done debugging your site and it goes live
error_log(“workoutdate”);
error_log($_POST[‘activity’]);
error_log($_POST[‘time_mins’]);
error_log($_POST[‘user_id’]);
// create an array of the form fields to pass to the php file that will handle inserting
// new data into the database table
$fields = array(
‘workoutdate’ => $_POST[‘workoutdate’],
‘activity’ => $_POST[‘activity’],
‘time_mins’ => $_POST[‘time_mins’],
‘user_id’ => $_POST[‘user_id’]);
// this calls the php file that will insert the data into your database table
wp_remote_post( ‘http://localhost/testsite/my_stuff/create_workout.php’, [
‘body’ => $fields,
]);
// after the data is inserted into the database you redirect them back to the workout form page
wp_safe_redirect(‘http://localhost/testsite/my-workout/’);
}
}, 10);
Next copy the page.php file in your site active theme
site>wp-content>themes>YourTheme>page.php
To
site>wp-content>themes>YourTheme>my_workout_page.php
This is the page.php file for my theme
<?php
/**
* The template for displaying all pages.
*
* This is the template that displays all pages by default.
* Please note that this is the WordPress construct of pages
* and that other ‘pages’ on your WordPress site will use a
* different template.
*
* @package Workout Lite
*/
get_header(); ?>
<div class=”main-container”>
<div class=”content-area”>
<div class=”middle-align content_sidebar”>
<div class=”site-main” id=”sitemain”>
<?php while ( have_posts() ) : the_post(); ?>
<?php get_template_part( ‘content’, ‘page’ ); ?>
<?php
//If comments are open or we have at least one comment, load up the comment template
if ( comments_open() || ‘0’ != get_comments_number() )
comments_template();
?>
<?php endwhile; // end of the loop. ?>
</div>
<?php get_sidebar(); ?>
<div class=”clear”></div>
</div>
</div>
<?php get_footer(); ?>
This is the content of my_workout_page.php
<?php
/**
Template Name: my workout page
*/
get_header(); ?>
<div class=”main-container”>
<div class=”content-area”>
<div class=”middle-align content_sidebar”>
<div class=”site-main” id=”sitemain”>
<form role=”form” method=”post”>
<?php wp_nonce_field( ‘create_workout’, ‘tuokrow_etaerc’ ); ?>
<div class=”form-group”>
<label for=”workoutdate”>Workout Date </label><input id=”workoutdate” name=”workoutdate” type=”date” class=”form-control input-sm” required=””>
</div>
<div class=”form-group”>
<label for=”activity”>Activity </label><input id=”activity” name=”activity” type=”text” placeholder=”activity” class=”form-control input-sm” required=””>
</div>
<div class=”form-group”>
<label for=”time_mins”>Time In Mins </label><input id=”time_mins” name=”time_mins” type=”text” class=”form-control input-sm” required=””>
</div>
<div class=”form-group”>
<input id=”user_id” name=”user_id” type=”hidden” value=”<?php echo get_current_user_id(); ?>” class=”form-control input-sm” required=””>
</div>
<div class=”row justify-content-center”>
<div class=”col-xs-4 col-sm-4 col-md-4″>
<input type=”submit” value=”Submit” class=”btn btn-info btn-block” name=”joinleagueformbtn”>
</div>
</div>
</form>
<div>
<label>Workout History</label>
</div>
<div>
</div>
<script src=”https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js”></script>
<script>
jQuery( document ).on(‘submit_success’, function( event, response ){
read();
});
jQuery( document ).ready(function( $ ){
read();
});
function getReadUrl() {
var params = new URLSearchParams(window.location.search);
var url = “../my_stuff/read_workouts.php”;
if (params.has(‘id’)) {
url = url + “?id=” + params.get(‘id’);
}
return url;
}
function read(){
var url = getReadUrl();
$.get( url, function( data ) {
document.getElementById(“records”).innerHTML = data;
});
}
</script>
<table border=”1″ id=”records”></table>
</div>
</div>
<?php get_sidebar(); ?>
<div class=”clear”></div>
</div>
</div>
<?php get_footer(); ?>
Set the workout page to use the workout template
Now we will make the 5 file files needed to communicate with your database table
- The database connection file called mydbfile.php
- You will create this in the root directory of your website.
- This will be used by all the remaining files to Create, Read, Update and Delete data in your WordPress database table(s).
B. The Create file. Mine is called create_workout.php and is in a subdirectory of the web root directory called mystuff
C. The Read file. Mine is called read_workouts.php and is in a subdirectory of the web root directory called mystuff
D. The Update file. Mine is called update_workout.php and is in a subdirectory of the web root directory called mystuff
E. The Delete file. Mine is called delete_workout.php and is in a subdirectory of the web root directory called mystuff
Contents of mydbfile.php
<?php
# connection to database tables
$conn = new mysqli(“localhost”,”root”,”root”,”testsite”);
?>
Contents of create_workout.php
<?php
include ‘../../mydbfile.php’;
$workoutdate = $_POST[“workoutdate”];
$activity = $_POST[“activity”];
$time_mins = $_POST[“time_mins”];
$user_id = $_POST[“user_id”];
// next four lines are for debugging, they appear in the php_error.log file
// comment them out before putting into production
error_log(‘workoutdate = ‘.$workoutdate);
error_log(‘activity = ‘.$activity);
error_log(‘time_mins = ‘.$time_mins);
error_log(‘user_id = ‘.$user_id);
$sql = “insert into my_workouts (user_id,workout_date,activity,time_mins) values(‘$user_id’, ‘$workoutdate’, ‘$activity’,’$time_mins’)”;
error_log(‘sql = ‘.$sql);
$conn->query($sql);
$conn->close();
?>
Contents of read_workouts.php
<?php
include ‘../../mydbfile.php’;
global $wpdb;
include ‘../wp-load.php’;
// get current user ID, with default value, if empty
$current_user_id = get_current_user_id();
// next two lines are for debugging, they appear in the php_error.log file
// comment them out before putting into production
error_log(‘current_user_id ‘.$current_user_id);
error_log(‘id = ‘.$_GET[‘id’]);
$sql = “select * from my_workouts where user_id = “.$current_user_id;
$result = $conn->query($sql);
// next line is for debugging, they appear in the php_error.log file
// comment it out before putting into production
error_log(‘sql = ‘.$sql);
// webpage form starts here
echo “<tbody>”;
echo “<tr>”;
echo “<td>Date</td><td>Workout</td><td>Time Spent (Mins)</td><td>Update</td><td>Delete</td>”;
echo “</tr>”;
while($row = $result->fetch_assoc()) {
if ($row[‘id’] == $_GET[‘id’]) {
echo ‘<tr><td colspan=”5″><form action=”/testsite/my_stuff/update_workout.php” method=”POST”>’;
echo ‘<table border=”1″>’;
echo ‘<tr><td><input type=”date” name=”workout_date” value=”‘ . $row[‘workout_date’] . ‘”></td>’;
echo ‘<td><input type=”text” name=”activity” value=”‘ . $row[‘activity’] . ‘”></td>’;
echo ‘<td><input type=”text” name=”time_mins” value=”‘ . $row[‘time_mins’] . ‘”></td>’;
echo ‘<td><input type=”hidden” name=”user_id” value=”‘ . $row[‘user_id’] . ‘”><input type=”submit” value=”Save”></td>’;
echo ‘<td><input type=”hidden” name=”id” value=”‘ . $row[‘id’] . ‘”></td></tr>’;
echo ‘</form>’;
echo ‘</td></td>’;
} else {
echo “<tr>”;
echo “<td>” . $row[‘workout_date’] . “</td>”;
echo “<td>” . $row[‘activity’] . “</td>”;
echo “<td>” . $row[‘time_mins’] . “</td>”;
echo ‘<td><a href=”/testsite/my-workout/?id=’ . $row[‘id’] . ‘”>Update</a></td>’;
echo ‘<td><a href=”/testsite/my_stuff/delete_workout.php?id=’ . $row[‘id’] . ‘”>Delete</a></td>’;
echo “</tr>”;
}
}
echo “</tbody>”;
$conn->close();
?>
Contents of update_workout.php
<?php
include ‘../../mydbfile.php’;
global $wpdb;
include ‘../wp-load.php’;
// get current user ID, with default value, if empty
$current_user_id = get_current_user_id();
// next two lines are for debugging, they appear in the php_error.log file
// comment them out before putting into production
error_log(‘current_user_id ‘.$current_user_id);
error_log(‘workout id = ‘.$_POST[‘id’]);
$workout_date = $_POST[‘workout_date’];
$activity = $_POST[‘activity’];
$time_mins = $_POST[‘time_mins’];
$id = $_POST[‘id’];
$sql = “update my_workouts set workout_date='”.$workout_date.”‘, activity='”.$activity.”‘, time_mins='”.$time_mins.”‘ where id=” . $id . ” and user_id=” . $current_user_id;
// next line is for debugging, they appear in the php_error.log file
// comment it out before putting into production
error_log(‘sql = ‘.$sql);
$result = $conn->query($sql);
$conn->close();
header(“location: /testsite/my-workout/”);
?>
Contents of delete_workout.php
<?php
include ‘../../mydbfile.php’;
global $wpdb;
include ‘../wp-load.php’;
// get current user ID, with default value, if empty
$current_user_id = get_current_user_id();
// next two lines are for debugging, they appear in the php_error.log file
// comment them out before putting into production
error_log(‘current_user_id ‘.$current_user_id);
error_log($_GET[‘id’]);
$id = $_GET[‘id’];
$sql = “delete from my_workouts where id=”.$id.” and user_id=”.$current_user_id;
// next line is for debugging, they appear in the php_error.log file
// comment it out before putting into production
error_log(‘sql = ‘.$sql);
$result = $conn->query($sql);
$conn->close();
header(“location: /testsite/my-workout/”);
?>