Create and edit custom database rows

This guide will show you how to create a form that will insert new rows into a custom database table within the WordPress database, then when visiting the form again with the row ID in the URL it will load the saved data into the form and submitting the form again will updated the saved information.

Step 1 – Create the database table

In this example the database table will be called my_table and will have 5 columns: ID (auto incrementing), name, email, message and checkbox this SQL query will create the table:

1
2
3
4
5
6
7
8
CREATE TABLE `my_table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL,
  `email` varchar(128) NOT NULL,
  `phone` varchar(128) NOT NULL,
  `checkbox` varchar(128) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
CREATE TABLE `my_table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL,
  `email` varchar(128) NOT NULL,
  `phone` varchar(128) NOT NULL,
  `checkbox` varchar(128) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

Step 2 – Create the form

Create a form and add the following fields.

  • Name – Single Line Text
  • Email – Email Address
  • Phone – Single Line Text
  • Checkbox – Checkboxes
  • Row ID – Hidden (used to store the row ID when we are updating the data)

You might also want to disable sending the notification email (uncheck Settings → Email → Send form data via email) and saving entries (uncheck Settings → Entries → Save submitted form data).

Step 3 – Add the code to handle creating and editing the rows

Add each section of code below to the wp-content/themes/YOUR_THEME/functions.php file (or create a plugin for it). I’ve split the code into separate sections to make it easier to digest.

1
2
3
45
6
7
89101112
13
1415
16
17
18
19
20
21
22
function my_save_form_data(iPhorm $form)
{
    global $wpdb;
    $table = 'my_table'; 
    // The data to save/update
    $data = array(
        'name' => $form->getValue('iphorm_1_1'),        'email' => $form->getValue('iphorm_1_2'),        'phone' => $form->getValue('iphorm_1_3'),        'checkbox' => is_array($form->getValue('iphorm_1_4')) ? serialize($form->getValue('iphorm_1_4')) : ''    );
 
    $rowId = absint($form->getValue('iphorm_1_5')); 
    if ($rowId > 0) {
        $wpdb->update($table, $data, array('id' => $rowId));
    } else {
        $wpdb->insert($table, $data);
    }
}
add_action('iphorm_post_process_1', 'my_save_form_data');
function my_save_form_data(iPhorm $form)
{
    global $wpdb;
    $table = 'my_table';

    // The data to save/update
    $data = array(
        'name' => $form->getValue('iphorm_1_1'),
        'email' => $form->getValue('iphorm_1_2'),
        'phone' => $form->getValue('iphorm_1_3'),
        'checkbox' => is_array($form->getValue('iphorm_1_4')) ? serialize($form->getValue('iphorm_1_4')) : ''
    );

    $rowId = absint($form->getValue('iphorm_1_5'));

    if ($rowId > 0) {
        $wpdb->update($table, $data, array('id' => $rowId));
    } else {
        $wpdb->insert($table, $data);
    }
}
add_action('iphorm_post_process_1', 'my_save_form_data');
  • On line 4, change my_table to the table name (Note: this code will only work if the table is within the WP database, to use another database create a new instance of the wpdb class)
  • On lines 8, 9, 10, and 11 change name, email, phone and checkbox to the names of the database columns and replace iphorm_1_1, iphorm_1_2, iphorm_1_3 and iphorm_1_4 with the unique ID of the form element from which to get the value. Add or remove lines as needed.
  • On line 14, replace iphorm_1_5 with the unique ID of the Hidden field which stores the Row ID
  • On line 22, replace the number 1 with the form ID

Step 4 – Add the code to handle loading the saved form data

We will check the form page URL for an edit parameter containing the ID of the row to edit, for example: http://www.example.com/my-form?edit=6 to edit row 6.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
181920212223
24
25
26
27
function my_load_form_data(iPhorm $form)
{
    if (!isset($_GET['edit'])) {
        return;
    }
 
    global $wpdb;
    $rowId = absint($_GET['edit']);
 
    $row = $wpdb->get_row($wpdb->prepare('SELECT * FROM my_table WHERE id = %d', $rowId), ARRAY_A);
 
    if (!is_array($row)) {
        return;
    }
 
    // The form values to set
    $data = array(
        'iphorm_1_1' => $row['name'],        'iphorm_1_2' => $row['email'],        'iphorm_1_3' => $row['phone'],        'iphorm_1_4' => maybe_unserialize($row['checkbox']),        'iphorm_1_5' => $rowId    );
 
    $form->setValues($data);
}
add_action('iphorm_pre_display_1', 'my_load_form_data');
function my_load_form_data(iPhorm $form)
{
    if (!isset($_GET['edit'])) {
        return;
    }

    global $wpdb;
    $rowId = absint($_GET['edit']);

    $row = $wpdb->get_row($wpdb->prepare('SELECT * FROM my_table WHERE id = %d', $rowId), ARRAY_A);

    if (!is_array($row)) {
        return;
    }

    // The form values to set
    $data = array(
        'iphorm_1_1' => $row['name'],
        'iphorm_1_2' => $row['email'],
        'iphorm_1_3' => $row['phone'],
        'iphorm_1_4' => maybe_unserialize($row['checkbox']),
        'iphorm_1_5' => $rowId
    );

    $form->setValues($data);
}
add_action('iphorm_pre_display_1', 'my_load_form_data');
  • On lines 18, 19, 20, and 21 change name, email, phone and checkbox to the names of the database columns and replace iphorm_1_1, iphorm_1_2, iphorm_1_3 and iphorm_1_4 with the unique ID of the form element from which to get the value. Add or remove lines as needed.
  • On line 22, replace iphorm_1_5 with the unique ID of the Hidden field which stores the Row ID
  • On line 27, replace the number 1 with the form ID

Note: anyone will be able to edit rows by changing the Row ID in the URL, you should implement a permission check (e.g. using current_user_can) or create a unique key when saving the row which is also required in the URL when loading the form data. You might need a developer for this.

Still having trouble? Head over to the forums.

Forums