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 – Text
  • Email – Email
  • Phone – Text
  • Checkbox – Checkboxes
  • Row ID – Hidden (used to store the row ID when we are updating the data)

In editing mode by default when the form is submitted the form values will reset to the original values when the page was loaded. You might want to go to Edit Form → Settings → Confirmations then go to the settings for the Default confirmation and set Reset form values to Keep form values.

You might also want to disable sending the notification email (remove or disable any notifications at Edit Form → Settings → Notifications) and saving entries (uncheck Edit Form → Settings → General → Save submitted form data).

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

Add the code below to the WordPress theme functions.php file (or or use a plugin).

12
34
5
6
7891011
12
1314
15
16
17
18
19
20
21
22
add_filter('quform_post_process_1', function (array $result, Quform_Form $form) {    global $wpdb;
    $table = 'my_table'; 
    // The data to save/update
    $data = array(
        'name' => $form->getValue('quform_1_3'),        'email' => $form->getValue('quform_1_4'),        'phone' => $form->getValue('quform_1_5'),        'checkbox' => is_array($form->getValue('quform_1_6')) ? serialize($form->getValue('quform_1_6')) : ''    );
 
    $rowId = absint($form->getValue('quform_1_7')); 
    if ($rowId > 0) {
        $wpdb->update($table, $data, array('id' => $rowId));
    } else {
        $wpdb->insert($table, $data);
    }
 
    return $result;
}, 10, 2);
add_filter('quform_post_process_1', function (array $result, Quform_Form $form) {
    global $wpdb;
    $table = 'my_table';

    // The data to save/update
    $data = array(
        'name' => $form->getValue('quform_1_3'),
        'email' => $form->getValue('quform_1_4'),
        'phone' => $form->getValue('quform_1_5'),
        'checkbox' => is_array($form->getValue('quform_1_6')) ? serialize($form->getValue('quform_1_6')) : ''
    );

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

    if ($rowId > 0) {
        $wpdb->update($table, $data, array('id' => $rowId));
    } else {
        $wpdb->insert($table, $data);
    }

    return $result;
}, 10, 2);
  • On line 1, replace the number 1 with the form ID
  • On line 3, 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 7, 8, 9, and 10 change name, email, phone and checkbox to the names of the database columns and replace quform_1_3, quform_1_4, quform_1_5 and quform_1_6 with the unique ID of the form element from which to get the value. Add or remove lines as needed.
  • On line 13, replace quform_1_7 with the unique ID of the Hidden field which stores the Row ID

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

Add the code below into the same file as the code in Step 3. 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.

12
3
4
5
6
7
8
9
10
11
12
13
14
15
16
171819202122
23
24
25
add_action('quform_pre_display_1', function (Quform_Form $form) {    if (!isset($_GET['edit'])) {
        return;
    }
 
    global $wpdb;
    $rowId = (int) $_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(
        'quform_1_3' => $row['name'],        'quform_1_4' => $row['email'],        'quform_1_5' => $row['phone'],        'quform_1_6' => maybe_unserialize($row['checkbox']),        'quform_1_7' => (string) $rowId    );
 
    $form->setValues($data);
});
add_action('quform_pre_display_1', function (Quform_Form $form) {
    if (!isset($_GET['edit'])) {
        return;
    }

    global $wpdb;
    $rowId = (int) $_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(
        'quform_1_3' => $row['name'],
        'quform_1_4' => $row['email'],
        'quform_1_5' => $row['phone'],
        'quform_1_6' => maybe_unserialize($row['checkbox']),
        'quform_1_7' => (string) $rowId
    );

    $form->setValues($data);
});
  • On line 1, replace the number 1 with the form ID
  • On lines 17, 18, 19, and 20, change name, email, phone and checkbox to the names of the database columns and replace quform_1_3, quform_1_4, quform_1_5 and quform_1_6 with the unique ID of the form element from which to get the value. Add or remove lines as needed.
  • On line 21, replace quform_1_7 with the unique ID of the Hidden field which stores the Row ID

Note: anyone will be able to edit rows by changing the Row ID in the URL, you might want to 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.

Be inspired. © 2020 ThemeCatcher Ltd. 20-22 Wenlock Road, London, England, N1 7GU | Company No. 08120384 | Built with React | Privacy Policy