Show content if code exists and email field is empty

[system]

Hi,

I’m trying to create a basic script where has a unique code which is then checked to see if it exists on the DB, and if the email field is empty, the record is updated with their email.

I’ve manage to get that part of the script working, but am struggling to make it so that if there’s an email already assigned to the code, the user cannot access the free download. Here’s what I’ve got so far:

[php]

<?php //form not yet submitted //display add news form if (!$_POST['submit']) { ?>

<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post" name="frmFreeTrack">
<label>Email:</label>
<input size="50" maxlength="250" type="text" name="email" />    
<label>Code:</label>
<input size="50" maxlength="250" type="text" name="code" />
<input type="Submit" name="submit" value="Submit" />
</form>

<?php } else { include('config.php'); //set up error list $errorList = array(); $code = $_POST['code']; $email = $_POST['email']; //Open a connection to the database $db = mysql_connect($hostname, $user, $pass) or die ('Error connecting to mysql'); mysql_select_db($name) or die ('Error connecting to mysql'); $query = "SELECT * FROM $codesDB WHERE code='$code' "; $result = mysql_query($query) or die(mysql_error()); if (mysql_num_rows($result) ) { $query = "UPDATE $codesDB SET email = '$email' WHERE code='$code' "; $result = mysql_query($query) or die(mysql_error()); print 'Download free track here'; include('download.php'); } else { print 'Incorrect code'; } } ?>[/php]

How would I go about adding, if the email field has content “this code has already been used” error?

Any pointers would be greatly appreciated.

Sean

[jSherz]

First of all, you should always sanitize user input before putting it in a query:

[php]$code = mysql_real_escape_string($_POST[‘code’]);
$email = mysql_real_escape_string($_POST[‘email’]);[/php]

When you’re checking for the code to have been used, you could either remove it from the DB or check that the e-mail field is blank:

[php]$query = "SELECT * FROM $codesDB WHERE code=’$code’ ";[/php]

Becomes:

[php]$query = "SELECT * FROM $codesDB WHERE code=’$code’ AND email = ‘’ ";[/php]

Then a user would get an incorrect code error message when they entered one that was already used.

[Seanie]

Hi
Thanks for your help.

I’ve made the changes suggested, but am now just getting “Invalid Code” for every code/email I enter.

Have I added it in the right place?

[php]<?php
//form not yet submitted
//display add news form
if (!$_POST[‘submit’])
{
?>

Email:

Code:

<?php } else { include('config.php'); //set up error list $errorList = array(); $code = mysql_real_escape_string($_POST['code']); $email = mysql_real_escape_string($_POST['email']); //Open a connection to the database $db = mysql_connect($hostname, $user, $pass) or die ('Error connecting to mysql'); mysql_select_db($name) or die ('Error connecting to mysql'); $query = "SELECT * FROM $codesDB WHERE code='$code' AND 'email' = '' "; //$query = "SELECT * FROM $codesDB WHERE code='$code' "; $result = mysql_query($query) or die(mysql_error()); if (mysql_num_rows($result) ) { $query = "UPDATE $codesDB SET email = '$email' WHERE code='$code' "; $result = mysql_query($query) or die(mysql_error()); print 'Download free track here'; include('download.php'); } else { print 'Sorry this code is invalid or has already been used'; } } ?>[/php]

Sean

[jSherz]

Is the e-mail field set to blank when the code hasn’t been used?

[Seanie]

Yes, there are three columns in the table - id, code and email.

id and code have data in them (id increments and code is unique code) emails are blank by default until a user inputs a code thats on the table and inputs the email into the email field.

I just want to make sure that the codes can’t be used more than once - ie once an email has been written into the email field.

[jSherz]

You put ‘email’ = ‘’ which I believe compares the string ‘email’ and the string ‘’, not the field. You need to use back ticks: ``

[php]$query = "SELECT * FROM $codesDB WHERE code=’$code’ AND email = ‘’ ";[/php]

[Seanie]

Doesn’t work either

http://assets.over9.co.uk/freetrack/

Try code 123

[jSherz]

Can you run this:

[php]echo mysql_num_rows($result);[/php]

After:

[php]$result = mysql_query($query) or die(mysql_error());[/php]

[Seanie]

Okay, I’ve added that and I get no errors.

It seems that it goes straight to else of the if statement, regardless of what data I input in the form. :’(

[jSherz]

Does the mysql_num_rows output anything? You should see a number displayed somewhere on the page.

[Seanie]

Nope. It just goes straight to the else and prints ‘Sorry this code is invalid or has already been used’

I added the mysql_num_rows to the else part of the if statement and it output 0, which is odd, cause there are 12 rows.

[jSherz]

That means that the query returned no results. I tested that very query myself with a test database and it worked fine. Are you 100% sure that the e-mail fields are all blank (on the ones you are testing).

[Seanie]

Yep they’re all blank, is there anything in the field settings i could be missing? I’ve got them set to VARCHAR right now.