CODEWITHSUNDEEP
phpmysql
Johnny Samual
Johnny Samual

Reputation: 23

verify mysql column data and look for matching email in that row?

Hi i wonder if someone can help me, i basically have a registration form where users can sign up and with each registration a unique verification code is generated to the database and sent out in the email to the user when they sign up.

im now working on the second stage where a user clicks a link in their email and is taken to verification.php

on this page the user must type in the verifcatiion/registration code they were sent in the email. the script i have here should look for the verification code and make sure it matches that which is in the database.

What i want to do is somehow tell my query that if the verification code/registration code is correct with that which we hold in the database then send out a confirmation email to the email address we hold for that user matching that verification code. (so the email which is belonging to that row with the same verification code.)

my table loooks like this 

id           email               registration_code (verification code)
1      [email protected]                    5093dkfd

at the moment my query searches for the registration code held in the database but i don't know how to get it to find the matching email for that registration code and send out the email. this bit i need help with. can anyone please point me in the right direction,

also i want the query to tell the user whether theyve entered the code correctly or not and as it's currently telling them they've entered it correctly even if they havnt.

/**
 * ShuttleCMS - A basic CMS coded in PHP.
 * Verification Code Check - Used to confirm a user's verification code
 * 
 */
define('IN_SCRIPT', true);
// Start a session
session_start();

//Connect to the MySQL Database
include 'includes/_config/connection.php';


/*
 * Checks form field for verification code, then where verification code has email, send email to recipient     
 */

if ($_POST['verificationcode']=='') {
        header('Location: verification.php?err=1');
}
if(get_magic_quotes_gpc()) {
        $verificationcode = htmlspecialchars(stripslashes($_POST['verificationcode']));
} 
else {
        $verificationcode = htmlspecialchars($_POST['verificationcode']);

}


/*
 * Checks if the verification code exists and look for email in that row which belongs to that verification code to send email out.
 */

$sql = "SELECT COUNT(*) FROM ptb_registrations WHERE registration_code = '$verificationcode' AND '$verificationcode' MATCHES email";
$result = mysql_query($sql)or die('Could not find member: ' . mysql_error());

if (!mysql_result($result,0,0)>0) {
    header('Location: verification.php?err=2');
}

then we send a confirmation email: 

/*
         * Email out the infromation
         */


    (EMAIL BODY)
    YOUR code was matched and you are now registered etc.

Upvotes: 2

Views: 1709

Answers (2)

Brian Hoover
Brian Hoover

Reputation: 7991

Something like this should work:

$sql = "SELECT email FROM ptb_registrations WHERE registration_code = '$verificationcode'";
$result = mysql_query($sql)or die('Could not find member: ' . mysql_error());
if (mysql_num_rows($result) == 0) {
   header('Location: verification.php?err=2');
} else {
  $row = mysql_fetch_array($result);
  $email = $row['email'];
}

Upvotes: 1

Michael L.
Michael L.

Reputation: 618

First off, you should be aware that the mysql_* functions have been deprecated and you should use one of the other mysql libraries, such as mysqli or PDO (which supports multiple SQL flavors).

Second, you should use the appropriate escaping function for the mysql library you choose.

To select the matching email, use SELECT email FROM ...

To check the results, you could start with a num_rows function (e.g. mysql_num_rows in the mysql library or the num_rows property in mysqli) to see if it found a matching email address, then if it found a match fetch the actual row content to get the email. Then use PHP to send an email to the address.

Upvotes: 1

Related Questions