SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Member
    Join Date
    May 2012
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    PHP IF statement in mysql query

    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:
    Code 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);
    ?>

    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?

  2. #2
    SitePoint Enthusiast
    Join Date
    Mar 2004
    Location
    Michigan
    Posts
    60
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    EDIT: I reread your post, so the v_code and location is the same?

    PHP Code:
    <?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);
    ?>

  3. #3
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,268
    Mentioned
    18 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,268
    Mentioned
    18 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by scd1982 View Post
    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.
    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.

    PHP Code:
    $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.

  5. #5
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,268
    Mentioned
    18 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by scd1982 View Post
    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?
    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:

    PHP Code:
    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); 

  6. #6
    SitePoint Member
    Join Date
    May 2012
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by spufi View Post
    EDIT: I reread your post, so the v_code and location is the same?
    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.

  7. #7
    SitePoint Member
    Join Date
    May 2012
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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).

    My table now looks like this:

    redeemer_email redeemer_first_name redeemer_last_name 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!!!

  8. #8
    SitePoint Member
    Join Date
    May 2012
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    RESOLVED PHP IF statement in mysql query

    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!

    Code PHP:
    <?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);  
    ?>


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •