Quick SQL question-SOLVED

7 replies
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'];
}
#question #quick #sql
  • Profile picture of the author Mkj
    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.
    {{ DiscussionBoard.errors[7597539].message }}
    • Profile picture of the author PHPSpaZ
      Mkj, that would work if the select were logical, as in IF their is no matching email in table1 THEN look in table 2.
      {{ DiscussionBoard.errors[7597598].message }}
  • Profile picture of the author KirkMcD
    Do a UNION query and only select the fields you need.
    {{ DiscussionBoard.errors[7598376].message }}
    • Profile picture of the author PHPSpaZ
      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
      {{ DiscussionBoard.errors[7598439].message }}
      • Profile picture of the author capsoon
        In this case.I think you can be used union operation. ^__^

        SELECT *
        FROM hotel
        WHERE primaryEmail='email'
        union
        SELECT *
        FROM vendor
        WHERE primaryEmail='email'
        {{ DiscussionBoard.errors[7599699].message }}
        • Profile picture of the author PHPSpaZ
          Originally Posted by capsoon View Post

          In this case.I think you can be used union operation. ^__^
          Thanks for breaking it down like that. Really helped me understand it better.
          {{ DiscussionBoard.errors[7602169].message }}
    • Profile picture of the author PHPSpaZ
      Originally Posted by KirkMcD View Post

      Do a UNION query and only select the fields you need.
      I see how that will help me to reduce my filesize a bit. Thank you!
      {{ DiscussionBoard.errors[7602158].message }}

Trending Topics