Page 2 of 2 FirstFirst 12
Results 11 to 14 of 14

Thread: MYSQL Query

  1. #11
    rlhanson's Avatar
    rlhanson is offline Master Glow Jedi
    Join Date
    Aug 2007
    Location
    Chapman, Kansas
    Posts
    531

    Default

    John-Marc,
    I have a store db, and a hosting/billing db. I want to create a customer account area on my website where my clients can login and view their printing (store db) and hosting (hosting db) invoice history, as well as update their address/phone, etc all within their client profile.
    I'm thinking maybe I should create a member db like the script I mentioned has, and add the clients from both db into the member db as this would let me have only 1 db for client contact info. Then once they are logged in query the other 2 db to display content based on which db they appear in. Does that make sense?

    Hopefully a simple/stupid question... if I connect and query db1 and have a result set of result1 and then connect and query db2 and have a result set of result2... Can I use both vars (result1 and result2) even after the connections are closed?
    Thank you,
    Lynne Hanson
    RL Hanson-Online

  2. #12
    rlhanson's Avatar
    rlhanson is offline Master Glow Jedi
    Join Date
    Aug 2007
    Location
    Chapman, Kansas
    Posts
    531

    Default

    Quote Originally Posted by jmarcv View Post

    select id from check_db1.table
    UNION
    select id from db2.table2
    I need to check if the email exists in either db tables and if it does in either allow login.

    Both tables have different ID's assigned to the user so I tried email and * with a WHERE email = $email and couldn't get any combination to work.

    If I use a separate member db for login then once they are logged in I am successfully querying each db.table and displaying information by where the user is located in the databases.

    This is what I have after they login using a members db (which I would like to eliminate):


    PHP Code:
    <?php
     
     
    //Include database connection details
     
    require_once('config.php');
     
    //Connect to mysql server
     
    $link2 mysql_connect(DB_HOSTDB_USERDB_PASSWORD);
     if(!
    $link2) {
      die(
    'Failed to connect to server: ' mysql_error());
     }
     
    //Create query
     
    $qry2="SELECT * FROM db_store.login WHERE Email='$email' AND PW='$password'";
     
    $result2=mysql_query($qry2);
     
     
    //Check whether the query was successful or not
     
    if($result2) {
      if(
    mysql_num_rows($result2) == 1) {
       
    //Client in store db
       
    echo "client is in store db";
      }else {
       
    //Client not in store db
       
    echo "client is NOT in store db";
      }
     }
     
    ?>
        
        <?php
     
    //Create query
     
    $qry3="SELECT * FROM db_host.users WHERE email='$email'";
     
    $result3=mysql_query($qry3);
     while (
    $row mysql_fetch_assoc($result3)) {
      
    $id $row['id'];
      
    $username $row['email'];
      
        echo 
    "<br />" $id $username $password;
     echo 
    "<br />";
     }
     
     
    //Check whether the query was successful or not
     
    if($result3) {
      if(
    mysql_num_rows($result3) == 1) {
       
    //Client in host db
       
    echo "client is in host db";
       
       
       
      }else {
       
    //Client not in host db
       
    echo "client is NOT in host db";
      }
     }
     
     
    ?>
    Thank you,
    Lynne Hanson
    RL Hanson-Online

  3. #13
    rlhanson's Avatar
    rlhanson is offline Master Glow Jedi
    Join Date
    Aug 2007
    Location
    Chapman, Kansas
    Posts
    531

    Default

    I got it!!
    Probably not the most efficient way, but it works!

    PHP Code:
    <?php
    //Create query
     
    $qry2="SELECT * FROM db_store.login WHERE Email='$login' AND PW='$password'";
     
    $result2=mysql_query($qry2);
     
     
     
    //Check whether the query was successful or not
     
    if($result2) {
      if(
    mysql_num_rows($result2) == 1) {
       
    //Login Successful
       
    session_regenerate_id();
       
    $member mysql_fetch_assoc($result2);
       
    $_SESSION['SESS_MEMBER_ID'] = $member['ID'];
       
    $_SESSION['SESS_FIRST_NAME'] = $member['UserName'];
       
    $_SESSION['SESS_LOGIN'] = $member['Email'];
       
    $_SESSION['SESS_PASSWORD'] = $member['PW'];
       
    session_write_close();
       
    header("location: member-index.php");
       exit();
      }
     
    //Create query
     
    $qry3="SELECT * FROM db_host.users WHERE email='$email'";
     
    $result3=mysql_query($qry3);}
      if(
    $result3) {
      if(
    mysql_num_rows($result3) == 1) {
       
    //Login Successful
       
    session_regenerate_id();
       
    $member mysql_fetch_assoc($result3);
       
    $_SESSION['SESS_MEMBER_ID'] = $member['id'];
       
    $_SESSION['SESS_FIRST_NAME'] = $member['firstname'];
       
    $_SESSION['SESS_LOGIN'] = $member['email'];
       
    $_SESSION['SESS_PASSWORD'] = $_POST['password'];
       
    session_write_close();
       
    header("location: member-index.php");
       exit();
      }
      else {
     
       
    //Login failed
       
    header("location: login-failed.php");
       exit();
      }
     }else {
      die(
    "Query failed");
     }
    Thank you,
    Lynne Hanson
    RL Hanson-Online

  4. #14
    jmarcv's Avatar
    jmarcv is offline Cranky Coder
    Join Date
    Jan 2005
    Posts
    354

    Default

    Quote Originally Posted by rlhanson View Post
    John-Marc,
    Does that make sense?
    Always makes sense not to duplicate data - what you are proposing is the whole concept of "relational" database. Main member table with memberID
    invoices, orders, etc in other tables labelled with only the memberid

    Hopefully a simple/stupid question... if I connect and query db1 and have a result set of result1 and then connect and query db2 and have a result set of result2... Can I use both vars (result1 and result2) even after the connections are closed?
    Yes. Because these are result sets - PHP passes data to an array in memory.

Page 2 of 2 FirstFirst 12

Similar Threads

  1. Replies: 26
    Last Post: 02-01-2008, 08:17 PM
  2. Replies: 0
    Last Post: 04-21-2005, 09:13 PM

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14