Email Validation based on SQL results

Discuss MySQL Ajax Table Editor

Email Validation based on SQL results

Postby ouija » Mon Feb 20, 2012 12:18 am

Hi,

I am trying to use the val_fun for on_edit/on_save in conjunction with a function that calls to my database to check if an email address already exists by another user (or if it belongs to the user) or not, and return true or false depending on the result, in hopes to avoid users being created with multiple accounts under the same email address.

Being new to MATE I am still somewhat uncertain how to call values of other columns to run them in SQL statements, so any help on how to do this would be greatly appreciated! What I mean by that is when using the 'val_fun' => array(&$this,'valName') or whichever, how can I reference data from other columns? All I can use is the $val variable for whatever column it is associated with. So if I'm running the val_fun statement on my "id" column, how can I pull data using an array from the "userEmail" column? I'm sure they are stored in an array with either $col / $val / $info, but I have no idea how to access them and use them in my query.

Hopefully this makes sense. Anyways, I tried using $_SESSION variables to see if that might help, but unfortunately it doesn't appear to have any effect when trying to verify if an email exists in the database or not.

My code under the initiateEditor(); function looks like this:
Code: Select all
$tableColumns['userEmail'] = array('display_text' => 'User Email', 'perms' => 'EVTAXQSHOM', 'val_fun' => array(&$this,'valEmail'));


And my corresponding function looks like this:
Code: Select all
   // email validation
   function valEmail($col,$val,$info)
   {
   if (preg_match("/^([a-zA-Z0-9])+([a-zA-Z0-9\._-])*@([a-zA-Z0-9_-])+([a-zA-Z0-9\._-]+)+$/", ($val))
      {
         // now check database to ensure email doesn't already exist
           //require_once ('../connect.php');

         $query = "SELECT userEmail FROM userData WHERE userEmail = $val";
         $result = @mysql_query($query);
         $num = @mysql_num_rows($result);
         if ($num> 0) {         
            //email exists in database, check to see if user is editing/updating their own record
            $query = "SELECT userEmail FROM userData WHERE id = ".$_SESSION['userID']." LIMIT 1";
            $result = @mysql_query($query);
            $num = @mysql_num_rows($result);
            if ($num> 0)   
            {
               // email exists for current user, return true
               return true;
            }
            else
            {
               // email exists but for another user, return false
               return false;
            }
         }
      // else email does not exist in database, return true
       return true;
      }
     // else email formatting error, return false
     else
     {
         return false;
      }
   }


The function simply doesn't appear to have any effect. Any thoughts? I feel like I am missing something, however this is the same code I used prior to MATE and it worked fine, although I did make use of stripslashes(trim) functions for the $var being searched (and have tried this without success on MATE) so I am stumped.

Any help is appreciated!


UPDATE: So I've managed to get the validation working by checking existing emails in the database using on_fun_edit and using "hidden_edit" (which I just learned about) to use the current ID of the selected user/id and also realized that my sql query syntax was broken and missing apostrophes around the variables so it wasn't working...

Here is my newly updated function for email validation, using hidden_edit' => true on $tableColumns['id'] and
'val_fun' => array(&$this,'valEmail') on $tableColumns['userEmail']
Code: Select all
   // email validation
   function valEmail($col,$val,$info)
    {
      // check if valid email format
      if (preg_match("/^([a-zA-Z0-9])+([a-zA-Z0-9\._-])*@([a-zA-Z0-9_-])+([a-zA-Z0-9\._-]+)+$/", $val))
      {
         // email valid, now check if already exists
         $query = "SELECT userEmail FROM userData WHERE userEmail = '$val'";
         $result = @mysql_query($query);
         $num = @mysql_num_rows($result);
             if ($num> 0)
         {
            // email does exist, now check if user if updating own record with same email
            $selectedUserID = $info['id'];
            $query = "SELECT userEmail FROM userData WHERE userEmail = '$val' and id = '$selectedUserID'";
            $result = @mysql_query($query);
            $num = @mysql_num_rows($result);
            if ($num> 0)
            {   
               // user is editing own record so just save
               return true;
            }
            else
            {
               // emails exists to another user; fails validation
               return false;
            }
         }
         else
         {
          // email doesn't exist in database at all
            return true;
         }
         return true;
      }
     // else email formatting error, return false
      else
      {
         return false;
      }
   }


Now I just have to learn how to override the error message to give it more detail on why validation failed..
ouija
 
Posts: 12
Joined: Sat Feb 18, 2012 10:30 am

Return to Open Discussion

Who is online

Users browsing this forum: Google [Bot] and 9 guests

cron