Very simple MySQL queries

[Linkz0rs]

HELP: Very simple MySQL queries

Hello, I've been working on a script and I'm getting a little confused here...
I know how to grab data from a database and show the results in a list (but that's not quite what I want here)
I want the script to...
1. Ask the user to enter a code (default page)
2. Utilize POST for the same page (I don't want to have multiple pages for this, I want it all in ONE page [index.php]) which then checks the database to see if the code exists
3. If the code exists then return the result (Used, Un-Used, Doesnt exist in table)
4. If the code does exist and is used then tell the user it's already been used
5. If the code does exist and has NOT been used, then give the user a link to download a file then modify the table in the database to mark the code as Used so it thus cannot be used again
6. If the code does not exist at all, then tell the user.

I've stripped out a lot of the HTML to just very basic ECHO's.

If you can help me achieve the above ^^^^^^ I would be appreciative.
My code:

<?php

// Connect to Database
$username = "my_username";
$password = "my_password";
$hostname = "localhost";    
$dbh = mysql_connect($hostname, $username, $password) 
    or die("Cannot connect to database");
$selected = mysql_select_db("database_name",$dbh) 
    or die("Cannot connect to database");

// Fetch Code from POST
$couponcode = $_POST['couponcode'];

// Default - First Visit - Ask User to enter a code in the form
if (!isset($_REQUEST['couponcode'])) {
    echo("<form action='index.php' method='post' id='checkcc'>
      <p>Coupon Code: <input id='name' name='couponcode' class='text' /><br />
      <input type='image' name='imageField' id='imageField' src='../images/submit.gif' class='send' /></p></form>");
}

// Code already used
elseif ($couponcode=="") {
    echo("Unfortunately that code has already been used.");    
}

// Valid Code
elseif ($couponcode=="") {
    echo("Valid Code! Click here to receive your prize.");

    // Enter some code to make what WAS a valid code now marked as Used so it cannot be used again
}
ou
  elseif ($couponcode=="") {
        echo("You did not enter a code, please go back and try again.");
}

// Code does not exist
  else {
        echo("Code does not exist, sorry.");
}
?>

My table looks like this...
ID, Code, Validity

 

[bdoprod]

Sounds to me like you might need a nested if statement to have the html form display when there is nothing POSTed. In my example below, I use two nested if statements to validate the inputted data.

You also need to have an SQL 'SELECT' statement (see below) to find the code in your database. You may have inadvertantly deleted that line of code when you deleted all of the HTML you were talking about.

If you plan to update your database to mark the coupon as used when the use puts in a valid coupon, you will also need an SQL UPDATE statement (see below).

Obviously, this code should be cleaned up, I wrote it with the intent of making it easy to understand, not necessarily to be efficient.

[FONT=Courier New]
[B]<?php
$couponcode = $_REQUEST['couponcode'];
//database connection stuff
$con = mysql_connect("localhost", "username", "password") or die(mysql_error()); //create connection to SQL
mysql_select_db("couponsdatabase") or die(mysql_error()); //choose database on server
//the following variable is used as a SQL statement to 
//select two fields from your coupon database - one with the coupon code and one where you 
//mark whether or not the coupon has been used - obviously you'll need to change the select statement to 
//match your database fields
$selectstatement = "SELECT couponcode, couponused FROM couponcodestable WHERE couponcode = '". $couponcode."';";
//this statement will be used to set the coupon as 'used' so it can't be used again.
$updatestatement = "UPDATE couponcodes SET couponused='1' WHERE couponcode = '". $couponcode . "';";
$selectrows = mysql_query($selectstatement);
$row = mysql_fetch_array( $selectrows );
//get the couponcode field from the database result
$dbcouponcode = $row['couponcode']; 
// get the couponused field from the database - assuming this is set to 0 if it hasn't been used or 1 if it has
$dbcouponused = $row['couponused']; 
$couponcode = $_REQUEST['couponcode'];
if (!$couponcode=='')) {  //if the couponcode passed from the html form IS NOT blank (e.g. they have passed a couponcode)
  if ($couponcode==$dbcouponcode){  //if the couponcode on html form is the same as the one found in the database
    if ($couponused=='0') {  //if the coupon has not been marked as used (1)
      mysql_query($updatestatement); //this marks the coupon as 'used' in the database
      // display text to user to show that the coupon was used correctly
    } else {
      echo("Sorry, this coupon has already been used.  Click <a href=index.php>here</a> to try again.");
      //linking them back to the index.php page without post variables will display the form below
    } else {
    echo("Invalid Coupon.  Click <a href=index.php>here</a> to try again.");
  }
} else {  //if the couponcode passed from the html form IS blank, show the form
  echo("<form action=index.php method=post><br />
             Coupon Code: <input class=text id=couponcode name=couponcode /><br />
             <input type="submit" name="mysubmit" value="Submit Coupon" />
           </form>
}
mysql_close($con);
?>

[/B][/FONT]

 

[Linkz0rs]

Sounds to me like you might need a nested if statement to have the html form display when there is nothing POSTed. In my example below, I use two nested if statements to validate the inputted data.

You also need to have an SQL 'SELECT' statement (see below) to find the code in your database. You may have inadvertantly deleted that line of code when you deleted all of the HTML you were talking about.

If you plan to update your database to mark the coupon as used when the use puts in a valid coupon, you will also need an SQL UPDATE statement (see below).

Obviously, this code should be cleaned up, I wrote it with the intent of making it easy to understand, not necessarily to be efficient.

[FONT=Courier New]
[B]<?php
$couponcode = $_REQUEST['couponcode'];
//database connection stuff
$con = mysql_connect("localhost", "username", "password") or die(mysql_error()); //create connection to SQL
mysql_select_db("couponsdatabase") or die(mysql_error()); //choose database on server
//the following variable is used as a SQL statement to 
//select two fields from your coupon database - one with the coupon code and one where you 
//mark whether or not the coupon has been used - obviously you'll need to change the select statement to 
//match your database fields
$selectstatement = "SELECT couponcode, couponused FROM couponcodestable WHERE couponcode = '". $couponcode."';";
//this statement will be used to set the coupon as 'used' so it can't be used again.
$updatestatement = "UPDATE couponcodes SET couponused='1' WHERE couponcode = '". $couponcode . "';";
$selectrows = mysql_query($selectstatement);
$row = mysql_fetch_array( $selectrows );
//get the couponcode field from the database result
$dbcouponcode = $row['couponcode']; 
// get the couponused field from the database - assuming this is set to 0 if it hasn't been used or 1 if it has
$dbcouponused = $row['couponused']; 
$couponcode = $_REQUEST['couponcode'];
if (!$couponcode=='')) {  //if the couponcode passed from the html form IS NOT blank (e.g. they have passed a couponcode)
  if ($couponcode==$dbcouponcode){  //if the couponcode on html form is the same as the one found in the database
    if ($couponused=='0') {  //if the coupon has not been marked as used (1)
      mysql_query($updatestatement); //this marks the coupon as 'used' in the database
      // display text to user to show that the coupon was used correctly
    } else {
      echo("Sorry, this coupon has already been used.  Click <a href=index.php>here</a> to try again.");
      //linking them back to the index.php page without post variables will display the form below
    } else {
    echo("Invalid Coupon.  Click <a href=index.php>here</a> to try again.");
  }
} else {  //if the couponcode passed from the html form IS blank, show the form
  echo("<form action=index.php method=post><br />
             Coupon Code: <input class=text id=couponcode name=couponcode /><br />
             <input type="submit" name="mysubmit" value="Submit Coupon" />
           </form>
}
mysql_close($con);
?>

[/B][/FONT]

Thank you!
Your code didn't quite work out, so I've used the same code format I used before, just added in your database query stuff

Again, thank you very much
Much appreciated. ^^

 

[misson]

If you can help me achieve the above ^^^^^^ I would be appreciative.

Make sure you distinguish between the specific problems you're asking about and the description of the overall goal. Both need to be included, but when you mix the two it isn't clear what you're asking for. Moreover, the larger the request, the fewer people will look at it and the fewer good responses you'll get.

A design concern: what happens if someone distributes the link they're given after successfully submitting a coupon? Will the link work in perpetuity?

The mysql extension is outdated and on its way to deprecation. Instead, use PDO, which has many useful improvements, such as prepared statements and support for the Traversable interface, so you can loop over results with foreach. If you need a PDO tutorial, try "Writing MySQL Scripts with PHP and PDO".

Speaking of prepared statements, the code could use them. The sample is vulnerable to SQL injection, which is a very serious security risk. Data shouldn't be interpolated directly into a statement.

Mixing data access with display and logic code results in high coupling, which generally should be avoided. Better to separate each concern into distinct modules. See the MVC and Three-Tier architectures for some top-level examples of how these can be separated.

 

[Linkz0rs]

Make sure you distinguish between the specific problems you're asking about and the description of the overall goal. Both need to be included, but when you mix the two it isn't clear what you're asking for. Moreover, the larger the request, the fewer people will look at it and the fewer good responses you'll get.

A design concern: what happens if someone distributes the link they're given after successfully submitting a coupon? Will the link work in perpetuity?

The mysql extension is outdated and on its way to deprecation. Instead, use PDO, which has many useful improvements, such as prepared statements and support for the Traversable interface, so you can loop over results with foreach. If you need a PDO tutorial, try "Writing MySQL Scripts with PHP and PDO".

Speaking of prepared statements, the code could use them. The sample is vulnerable to SQL injection, which is a very serious security risk. Data shouldn't be interpolated directly into a statement.

Mixing data access with display and logic code results in high coupling, which generally should be avoided. Better to separate each concern into distinct modules. See the MVC and Three-Tier architectures for some top-level examples of how these can be separated.

..... o.0 im not trying to post on a forum like a pro... and i've already gotten what i wanted
my code is much more improved then displayed... =]
mods please close this topic.. k thanks baii

 

[Skizzerz]

Closed per request