Selecting a value from previous Gravity Forms entry (SQL Related)

Question

I am trying to select a value from the last previous entry, and increment that number by one each time. I have it working to where it selects the current entries value (default 1000) and increments 1 and re-inserts the value back (so ends up 1001 each time). I need it to select the last previous entry of that field instead so it will go 1001,1002,1003 etc. Thanks.

add_action('gform_after_submission_4', 'add_submission_id_four', 10, 2);
function add_submission_id_four($entry, $form) {
    global $wpdb;
    $field_number = 3;
    $table = $wpdb->prefix . 'rg_lead_detail';
    $form_id = 4; // update to the form ID your unique id field belongs to
    $result = $wpdb->get_var("SELECT value FROM $table WHERE form_id = '$form_id'
    AND field_number = '$field_number'");
    $result++;
    $unique = strval($result);

    $wpdb->insert("{$wpdb->prefix}rg_lead_detail", array(
            'value'         => $unique,
            'field_number'  => $field_number,
            'lead_id'       => $entry['id'],
            'form_id'       => $entry['form_id']
        ));// update the entry
}

score 0 · Answer 1 · edited May 23 '17 at 12:21

0

Can you just use an AutoIncrement column for your value column?

Mysql - Alter a column to be AUTO_INCREMENT

edited May 23 '17 at 12:21

Community

1
1

answered Jul 17 '14 at 22:47

Levesque

874
7
6

There is already an auto incrementing row by default named entry ID, but it just goes 1,2,3,4 etc... and there is no way to change that. – Benjamin Cuslidge Jul 17 '14 at 22:52

doublesharp · Accepted Answer · 2014-07-17T23:13:22.793

0

You can use the MAX() function to get the largest value for that form_id and field_number, defaulting to 1000 using IFNULL() (so that 1001 is the first returned). You can do the +1 in SQL as well so that incrementing $result is not needed.

I find it good form to use $wpdb->prepare() as well to handle the arguments.

$sql = "SELECT MAX(IFNULL(value,1000))+1 FROM {$wpdb->prefix}rg_lead_detail WHERE form_id = %d AND field_number = %d";
$result = $wpdb->get_var( $wpdb->prepare( $sql, $form_id, $field_number ) );
$unique = strval($result);

edited Jul 17 '14 at 23:13

answered Jul 17 '14 at 23:04

doublesharp

26,888
6
52
73

it is saying unexpected ";" on this line: $result = $wpdb->get_var( $wpdb->prepare( $sql, $form_id, $field_number ); also, do I need to replace the form_id = %d with what I had previously? – Benjamin Cuslidge Jul 17 '14 at 23:10
The call to `$wpdb->prepare()` will replace the `%d` with the `$form_id` and `$field_number` respectively. It's not needed in this case per se, but it is good form. – doublesharp Jul 17 '14 at 23:14
Thank you so much for the prompt replies and explanations. It's odd that there is no option in Gravity Forms to create a unique ID in any format like this... You helped me achieve it though! – Benjamin Cuslidge Jul 17 '14 at 23:42

Selecting a value from previous Gravity Forms entry (SQL Related)

2 Answers2