Quick SQL question-SOLVED

by 7 replies
9
I have to work off of another companys db and its not proper so I need some help. The users don't have a common table, instead they are divided into two tables one called 'hotel' and another called 'vendor'. The system requires a unified login so that all users use the same login form. Auth is handled on a separate table but once they auth the only info I have about them is their email and the fact that authentication passed(Authentication data is not associated with the user other than by their email). From here on out I can make the code how I want it. SOOO... To me the first step is to grab the basic user info for the session. PROBLEM: The users are in two separate tables and I dont know how to do this: If the user is not found in one table the get_user function will look in the other. Will this work?:

public function get_user($email) {
global $db;

$result=$db->query("SELECT * FROM hotel WHERE primaryEmail='$email'") or $result=$db->query("SELECT * FROM vendor WHERE primaryEmail='$email'");
$result_set=$db->fetch_array($result);
$this->user_id=$result_set['user_id'];
$this->email=$result_set['email'];
$this->user_name=$result_set['user_name'];
}
#programming #question #quick #sql
  • Not sure if I follow you for all of it but combining 2 tables isn't that hard:

    Code:
     = "SELECT table1.email, table2.email".
 "FROM table1, table2 ".
    "WHERE table1.email = table2.email";
    Not sure the above will help or not...

    I have used the above to combine data from 2 tables on 2 separate databases.
    • [ 1 ] Thanks
    • [1] reply
    • Mkj, that would work if the select were logical, as in IF their is no matching email in table1 THEN look in table 2.
  • Do a UNION query and only select the fields you need.
    • [ 1 ] Thanks
    • [2] replies
    • K I found a simple solution... Here you go:

      public function get_user($email) {
      global $db;
      //Assume user logged in is a hotel and grab their user info
      $result=$db->query("SELECT * FROM hotel WHERE primaryEmail='$email'");
      $result_set=$db->fetch_array($result);
      $email=$result_set['primaryEmail'];
      $active=$result_set['active'];
      $type='hotel';
      //If the query yeilded no results assume the user is a vendor
      if(empty($email)){
      $result=$db->query("SELECT * FROM vendor WHERE primaryEmail='$email'");
      $result_set=$db->fetch_array($result);
      $email=$result_set['primaryEmail'];
      $active=$result_set['active'];
      $type='vendor';
      //If all else fails let the user know they are not in our system*FAILSAFE FOR AUTH*
      if(empty($email)){
      die('Your email was not found in our system. Make sure that you type your email carefully.');
      }
      }
      //Check to see if the user has a verified email address, if not Tell them to activate
      if($active=='0'){
      die('Please activate your account.');
      }
      //If everything checks out create the user object
      $this->user_id=$result_set['id'];
      $this->typeOf=$type;
      $this->primaryEmail=$result_set['primaryEmail'];
      $this->contactName=$result_set['contactName'];
      }


      This did the trick just fine
      • [1] reply
    • I see how that will help me to reduce my filesize a bit. Thank you!
Join the discussion

Next Topics on Trending Feed

  • 9

    Quick SQL question-SOLVED

    I have to work off of another companys db and its not proper so I need some help. The users don't have a common table, instead they are divided into two tables one called 'hotel' and another called 'vendor'. The system requires a unified login so that all users use the same login form. Auth is handled on a separate table but once they auth the only info I have about them is their email and the fact that authentication passed(Authentication data is not associated with the user other than by their email). From here on out I can make the code how I want it. SOOO... To me the first step is to grab the basic user info for the session. PROBLEM: The users are in two separate tables and I dont know how to do this: If the user is not found in one table the get_user function will look in the other. Will this work?: public function get_user($email) {