Can Gravity Forms validate field entries against MySQL table values in Wordpress?

gerorge
May 14, 2023
202 views
3 votes
3 Answers

Basically, I have a table with 8 million codes for promotion. The contestant will enter the code on a gravity form, which will then query the database for code, once it is there the code will be marked as used, and the contestant should be redirected to the entry form. If not the form should tell the user code is invalid. This is my code below which I pulled from other sources because I am not a coder. However, when I submit, the correct code, it says incorrect. Any help will be appreciated just bear in mind I am not a programmer

add_filter( 'gform_field_validation_4', 'validate_code' );
function validate_code($validation_result){
    $form = $validation_result['form'];
    foreach( $form['fields'] as &$field ) {
    if ( strpos( $field->cssClass, 'validate-code' ) === false ) {
        continue;
        }
    }
    $field_value = rgpost( "input_1" );
    $is_valid = is_code( $field_value );
    $validation_result['is_valid'] = false;
    $field->failed_validation = true;
    $field->validation_message = 'The code you have entered is not valid. Please enter another.';
    $validation_result['form'] = $form;
    return $validation_result;
}
function is_code($code){
    $info = 'mysql response';
require_once(ABSPATH . '/wp-config.php');
    $conn = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
    if (mysqli_connect_errno()) {
        printf("Connect failed: %sn", mysqli_connect_error());
        echo 'Unable to connect to server, please use the back button in your browser and resubmit the form';
        exit();
    }
    $code = mysqli_real_escape_string($conn, $_POST['codes']);
    $sql_check = mysqli_query($conn, "SELECT * FROM entrycode WHERE codes = '$code' AND valid = 0");
    $sql_checkrows = mysqli_num_rows($sql_check);
    if ($sql_checkrows > 0) {
        $sql_update = "UPDATE entrycode SET used = 1 WHERE codes ='$code'";
        if (mysqli_query($conn, $sql_update)) {
            $info = mysqli_affected_rows($conn) . " Row updated.n";
            $info = 'success';
            return true;
    } else {
        return false;
        }
        mysql_close($conn);
    }
}
add_filter('gform_confirmation_4', 'valid_invitation_confirmation', 10, 4);
function valid_invitation_confirmation($confirmation, $form, $lead, $ajax){
    // customize this URL - I send the code in the query string
    $success_url = 'https://sample.com/enter' . '/?code=' . $lead[1];
    $confirmation = array('redirect' => $success_url);
    return $confirmation;
    }

Answers

Chosen as BEST ANSWER

so I modified the code from Phill, I am no longer getting the error, but the code is not being validated, as a matter of fact, any code I type in is valid., I am at a lost. My table is structured like this

entry codes table

ID	Codes	Used
34	98j65XF16	O

add_filter('gform_validation_4_1', 'validate_code');
function validate_code($validation_result){
    // this assumes the code is entered in field one on your form
    // change this input_ number if it's a different field
    if($is_code_valid($_POST['input_1'])){
        $validation_result['is_valid'] = false;
        foreach($validation_result['form']['fields'] as &$field){
        // field 1 is the field where we want to show the validation message 
            if($field['id'] == 1){
                $field['failed_validation'] = true;
                $field['validation_message'] = 'The code you entered is invalid: please try again.';
                break;
            }
        }
    }
    return $validation_result;
}
// use this function to validate codes 
function is_code_valid($thiscode){
    global $wpdb;

    // Get match count (consider using transient ref table).
    $matched_codes = $wpdb->get_var(
        $wpdb->prepare(
            "
                SELECT sum(meta_value)
                FROM $wpdb->entrycode
                WHERE codes = %s
                AND valid = 0
            ",
            $thiscode
        )
    );

    // If matches found , update table count?.
    if ( $matched_codes > 0) {
        $wpdb->query( $wpdb->prepare( 
            "
                UPDATE $wpdb->entrycode 
                SET used = %d
                WHERE codes = %s
            ",
            1, 
            $thiscode
        ) );        
        return true;
    }

    return false;

}


// doing this here because the redirect URL does not support variables or shortcodes
// change the 70 here to your form ID
add_filter('gform_confirmation_4', 'valid_invitation_confirmation', 10, 4);
function valid_invitation_confirmation($confirmation, $form, $lead, $ajax){
    // customize this URL - I send the code in the query string
    $success_url = 'https://demo.com/enter' . '/?code=' . $lead[1];
    $confirmation = array('redirect' => $success_url);
    return $confirmation;
}

(Edit)

Updated function to validate codes:

function is_code_valid($thiscode){
    global $wpdb;

    // Get match count (consider using transient ref table).
    $matched_codes = $wpdb->get_var(
        $wpdb->prepare(
            "
                SELECT sum(meta_value)
                FROM $wpdb->entrycode
                WHERE codes = %s
                AND valid = 0
            ",
            $thiscode
        )
    );

    // If matches found , update table count?.
    if ( !empty ( $matched_codes ) ) {
        $wpdb->query( $wpdb->prepare( 
            "
                UPDATE $wpdb->entrycode 
                SET used = %d
                WHERE codes = %s
            ",
            1, 
            $thiscode
        ) );        
        return true;
    }

    return false;

}

this code i have still is not checking the database with codes for validation ,, all codes returning invalid code message

 add_filter('gform_validation_2', 'validate_code');
    GFCommon::log_debug( __METHOD__ . '(): The POST => ' . print_r( $_POST, true ) );
    function validate_code($validation_result){
        // this assumes the code is entered in field one on your form
        // change this input_ number if it's a different field
        if(!is_code_valid($_POST['input_1'])){
            $validation_result['is_valid'] = false;
            foreach($validation_result['form']['fields'] as &$field){
            // field 1 is the field where we want to show the validation message 
                if($field['id'] == 1){
                    $field['failed_validation'] = true;
                    $field['validation_message'] = 'The code you entered is invalid: please try again.';
                    break;
                }
            }
        }
        return $validation_result;
    }
    function is_code_valid($thiscode){
           GFCommon::log_debug( __METHOD__ . '(): The POST => ' . print_r( $_POST, true ) ); 
            global $wpdb;
            // Get match count (consider using transient ref table).
            
            $matched_codes = $wpdb->get_var(
                $wpdb->prepare(
                    "
                        SELECT sum(meta_value)
                        FROM $wpdb->entrycode
                        WHERE codes = %s
                        AND valid = 0
                    ",
                    $thiscode
                )
            );
        
            // If matches found , update table count?.
            if ( !empty ( $matched_codes ) ) {
                $wpdb->query( $wpdb->prepare( 
                    "
                        UPDATE $wpdb->entrycode 
                        SET used = %d
                        WHERE codes = %s
                    ",
                    1, 
                    $thiscode
                ) );        
                return true;
            }
        
            return false;
        
    }
    
    // doing this here because the redirect URL does not support variables or shortcodes
    // change the 70 here to your form ID
    add_filter('gform_confirmation_2', 'valid_invitation_confirmation', 10, 4);
    function valid_invitation_confirmation($confirmation, $form, $lead, $ajax){
        // customize this URL - I send the code in the query string
        $country = ($_POST['input_3']);
        $success_url = 'https://sample.com/enter' . '/?code=' .$lead[1] .'&country=' .$country;
        $confirmation = array('redirect' => $success_url);
        return $confirmation;
    }

Please signup or login to give your own answer.

Click here to cancel reply.

Can Gravity Forms validate field entries against MySQL table values in WordPress?

Answers