MYSQL Query

[rlhanson]

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?

[rlhanson]

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
 
 //Include database connection details
 require_once('config.php');
 //Connect to mysql server
 $link2 = mysql_connect(DB_HOST, DB_USER, DB_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";
  }
 }
 
 ?>  

[rlhanson]

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

 <?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");
 }  

[jmarcv]

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.