Check database for existence of submitted form data

I am trying to set up a form/database to be used for a give away contest. The contest will be set up where a claim code will be sent out to an email distribution list. The first person to enter their name, email address and the code will win whatever prize is associated with the code.

table_1 will store the prize information (name, description, claim code, etc). This will be populated before the email goes out to the distribution list.
table_2 will only store the winner’s information.

The form will have to check not only that the claim code is correct by it’s existence in table_1, but also that it does not already exist in table_2.

I’m looking to create a form that does a few things:
1 - Allows a user to enter certain information (name, email address, claim code) - this part is easy, not concerned with coding the form. Just included it to give the full picture.
2 - Checks to see if the claim code the user entered is stored in table_1 of a MySQL database.
3a - If the claim code is in table_1, then the user’s form data (name, email address, and claim code) is saved to table_2 of the same database.
3b - If the claim code is not stored in table_1, then it displays an error message to the user stating that the code they entered is incorrect. The form would be re-displayed and the user would be prompted to try again. Nothing would get stored in table_2 until a correct validation code was entered.
4 - Once a user enters the correct claim code, that code is no longer valid for future users to submit.

I already have the database set up. I’m just looking for the code to process all the data.

Is there an easier way to do this? Does something like this already exist, or is there something similar available that I haven’t found yet?

Thanks in advance.

Post your database schema. There is a better way to do this.

The database is actually piggybacking off of a pre-existing database used for an e-commerce store (OpenCart). I’m just adding a table (table_2) to store the winner’s information.

table_1 is pre-existing, and can’t easily be modified as it’s used for the e-commerce store. I’m just using one of the un-used product fields to store the product’s claim code.

So, table_1 lists product information, and has columns:
product_id, model, sku, upc, location, quantity, stock_status_id, image, manufacturer_id, shipping, price, points, tax_class_id, date_available, weight, weight_class_id, length, width, height, length_class_id, subtract, minimum, sort_order, status, date_added, date_modified, viewed

I’m using the location column to store each product’s claim code.

table_2 has the following columns:
redeem_id, f_name, l_name, e_mail, c_code, date

Initially I thought when someone submits the form it would check to see if the claim code entered matches a code entered in the “location” column of table_1. If it did, it then would check to see if it has already been entered in “c_code” column of table_2. If it passes both checks, it would store the form data in table_2 and display some success message. If it failed one or the other, it would display the appropriate fail message.

I haven’t coded much on my own outside of tweaking some pre-existing PHP code (Wordpress, OpenCart, etc.) If there’s a better way, I’m all ears!!

I haven’t been successful yet, but the code I’m working on now, looks something like this. (A form submits f_name, l_name, e_mail, v_code to this script).
BTW, the earlier “table_1” is “product” and “table_2” is “redeem” in the code below.
No matter what I enter into the form, I am only able to get it to echo “Sorry, you have entered an incorrect claim code. Please use your browser’s back button to try again.” Even if the code I enter is correct, or is correct, but already exists in the winner’s (redeem) table.
[php]<?php
$first_name = $_POST[‘f_name’];
$last_name = $_POST[‘l_name’];
$email = $_POST[‘e_mail’];
$verification = $_POST[‘v_code’];

$dbc = mysql_connect(‘localhost’,‘db’,‘pw’)
or die(‘Could not connect: ’ . mysql_error());
$query = "INSERT INTO redeem (f_name, l_name, e_mail, v_code) " .
"VALUES (’$first_name’, ‘$last_name’, ‘$email’, ‘$verification’)";
if(mysql_query(“SELECT location FROM product WHERE location = ‘$verification’”)){
if(mysql_query(“SELECT v_code FROM redeem WHERE v_code = ‘$verification’”)){
echo ‘Sorry, this item has already been redeemed.’;
}else{ $result = mysql_query($dbc, $query)
or die(‘Error querying database.’);
}
}else{
echo ‘Sorry, you have entered an incorrect claim code. Please use your browser’s back button to try again.’;
}

mysql_close($dbc);
?>[/php]

You have the querys nested in each other. Pseudo code:

[php]
select v_code from table one
if (!mysql_num_rows($result)) {
die(“Sorry, you have entered an incorrect claim code. Please use your browser’s back button to try again”);
}

// No else is needed here. If you get here, there was a match in table one… Now do second query to see if it was redeemed.

SELECT v_code FROM redeem WHERE v_code = '$verification

[/php]

Sponsor our Newsletter | Privacy Policy | Terms of Service