I have a database with 2 tables that I need to access/potentially modify. One table has a list of products (table name “product”), and information for each. The other stores identifying information for people who redeem a code for a product (table name “redeem”).
Each product has a unique claim code associated with it, and is stored in the product’s “location” column in the “product” table. I have a form, where individuals can enter their name (first & last), email address, and a claim code. The first person to enter the claim code for a particular product “wins” it. As soon as someone wins a product, I want their name, email, and claim code to be saved in the “redeem” table of the database, as well as a “success” message displayed to the user.
Any subsequent users who try to enter the same code, should receive an “error” message stating that the product has already been claimed, and nothing should be saved to the DB.
If anyone enters an invalid claim code (i.e. a code that is not associated with any particular product), they should receive an error message stating that they entered an incorrect code. Also, nothing should be saved to the DB.
PHP code I have so far is:
<?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);
?>
Right now, no matter what I enter in the form that is passed to this script, the message I receive is “Sorry, you have entered an incorrect claim code. Please use your browser’s back button to try again.”
I’m sure there’s a better way - especially since there seems to be 3 separate queries going on. Is there a way to simplify the query/some other way to do this?
Off topic but still vitally important… your code is vulnerable to SQL injection. It’s the most common security flaw, yet also extremely easy to prevent.
Well, first off, even if the verification code doesn’t exist in the database, mysql_query will still return a truthy result. The only time you get a false value back is when there was an error. The mysql_query doc page gives you an example for how you can detect and handle such an error.
$result = mysql_query('SELECT * WHERE 1=1');
if (!$result) {
die('Invalid query: ' . mysql_error());
}
It would probably be worth your while to read that entire doc page in detail to make sure you understand what kind of value is returned by mysql_query.
If you’re open to refactoring your database schema, then yeah, there’s a better way.
You should have a product table that holds only product data—nothing about redemption codes. And you should have a promotion table (since “redeem” is a verb) that holds all redemption codes and details about the redeemer, plus a foreign key to the product it will redeem.
If you do that, then your code could look like this:
function get_promotion_by_redeem_code($redeem_code)
{
$result = mysql_query("SELECT * FROM promotion WHERE redeem_code = '".mysql_real_escape_string($redeem_code)."'");
$row = mysql_fetch_assoc($result);
return $row;
}
function redeem_promotion($promotion, $redeemer_email, $redeemer_first_name = '', $redeemer_last_name = '')
{
mysql_query("
UPDATE promotion
SET redeemer_email = '".mysql_real_escape_string($redeemer_email)."',
SET redeemer_first_name = '".mysql_real_escape_string($redeemer_first_name)."',
SET redeemer_last_name = '".mysql_real_escape_string($redeemer_last_name)."'
WHERE redeem_code = '".mysql_real_escape_string($promotion['redeem_code'])."'
");
}
$first_name = $_POST['f_name'];
$last_name = $_POST['l_name'];
$email = $_POST['e_mail'];
$verification = $_POST['v_code'];
$connection = mysql_connect('localhost', 'db', 'pw');
mysql_select_db('blog_db', $connection);
$promotion = get_promotion_by_redeem_code($verification);
if ($promotion) {
if (!$promotion['redeemer_email']) {
redeem_promotion($promotion, $email, $first_name, $last_name);
} else {
echo 'Sorry, this item has already been redeemed.';
}
} else {
echo 'Sorry, you have entered an incorrect claim code. Please use your browser\\'s back button to try again.'
}
mysql_close($connection);
Yes, location in the “product” table was an un-used column (more informational in the existing e-commerce database I’m using, not used in any programming). I’m essentially storing each product’s code in the “location” field in the product table. However, initially, the v_code column in the redeem table is empty. It only gets populated as follows:
When a user goes to claim a product, the code should check (1) that the code exists in the product table, and (2) that it does not yet exist in the redeem table. If both those conditions are true, then it should store the user’s information in the redeem table.
Not a big deal to change the DB a little. The “redeem” table didn’t have any data in it yet, so not much lost there.
I created a new table called “promotion” as you suggested, with the following columns: redeemer_email, redeemer_first_name, redeemer_last_name, redeem_code.
I added one row to the table with a random “test” redeem_code (123456789abc).
Then I went to the form and acted as a user who would be redeeming a code: entered my first/last name, email and redemption code.
The form passes the info to the script, except when I go to check the “promotion” table, the test row did not update with the first/last name, or email address I entered.
Any ideas??
P.S. Thanks for everyone’s help with this. I’m not the best at this, and your patience with me is appreciated!!!
OK, now it’s working.
I added a couple columns: redeem_id (auto increment) and redeem_date_time (uses NOW() when submitted). The code below will work as I described earlier.
Thanks for everyone’s help!
<?php
function get_promotion_by_redeem_code($redeem_code)
{
$sql = "SELECT * FROM promotion WHERE redeem_code= '".mysql_real_escape_string($redeem_code)."'";
$result = mysql_query($sql);
$row = mysql_fetch_assoc($result);
return $row;
}
function redeem_promotion($email,$first_name,$last_name,$redeem_date_time,$redeem_code)
{
$query = "UPDATE promotion SET redeemer_email='".mysql_real_escape_string($email)."', redeemer_first_name='".mysql_real_escape_string($first_name)."', redeemer_last_name='".mysql_real_escape_string($last_name)."', redeem_date_time=NOW() WHERE redeem_code='".mysql_real_escape_string($redeem_code)."'";
$insert = mysql_query($query);
return $insert;
}
$email=$_POST['e_mail'];
$first_name=$_POST['f_name'];
$last_name=$_POST['l_name'];
$redeem_code=$_POST['v_code'];
$connection = mysql_connect('localhost', 'db', 'pw');
mysql_select_db('db', $connection);
$promotion = get_promotion_by_redeem_code($redeem_code);
if ($promotion) {
if (!$promotion['redeemer_email']) {
redeem_promotion($email,$first_name,$last_name,$redeem_date_time,$redeem_code);
echo 'Congratulations, you have successfully claimed this item!';
} else {
echo 'Sorry, this item has already been redeemed.';
}
} else {
echo 'Sorry, you have entered an incorrect claim code. Please use your browser\\'s back button to try again.';
}
mysql_close($connection);
?>