Use results from MySql Search to Search another MySql Database can it be done?

[richiejones24]

Need some help I have 2 tables in a database and I need to search the first table and use the results from that search, to search another table, can this be done? and if it can how would you recommend that I go about it?

 

Thanks For Your Help Guys! 

[msaz87]

Something like this?

 

$query1 = mysql_query("SELECT key FROM db1 WHERE something = 'something'") or die(mysql_error());

if( mysql_num_rows($query1) >= 1 ) {

   while( $row = mysql_fetch_array($query1) ) {
      $searchKey = $row['key'];
   }

   $query2 = mysql_query("SELECT key FROM db2 WHERE something = '$searchKey'") or die(mysql_error());

} else { 

   echo "nothing found";

}

[xyph]

You're doing something wrong, I think. You shouldn't need to search for a search result :/

 

What exactly are you trying to accomplish?

[richiejones24]

Thanks for your reply's, let me clarify what i am trying to do, I have a dating site and i have added the option to upgrade your profile so it is displayed on the homepage so I have created a new MySql table with the users username and the upgrade options they have selected. so what i won't to do is search the upgrade table (SELECT * FROM upgrade WHERE upgrade_one = '1') which will give me all the user names of all the user who have upgraded, then i won't to search the user table with the username (i got from the first search) to pull there profiles of the upgraded users.       

[xyph]

So you want to grab all premium users AND their profiles?

[richiejones24]

Something like this?

 

$query1 = mysql_query("SELECT key FROM db1 WHERE something = 'something'") or die(mysql_error());

if( mysql_num_rows($query1) >= 1 ) {

   while( $row = mysql_fetch_array($query1) ) {
      $searchKey = $row['key'];
   }

   $query2 = mysql_query("SELECT key FROM db2 WHERE something = '$searchKey'") or die(mysql_error());

} else { 

   echo "nothing found";

}

 

Thanks For the Reply!

This is not exactly what i need as the original search will bring multiple results (user names) so the second search I need it to do something like this ("SELECT key FROM db2 WHERE something = '$searchKey' OR something = '$searchKey' OR something = '$searchKey' OR something = '$searchKey'  ")

 

in some kind of array ive tried using "WHILE" and "FOREACH" but i cant figure out how to do it or whether it can be done.   

[richiejones24]

So you want to grab all premium users AND their profiles?

 

yes that's correct but the user names of the upgraded users are in a separate table

[gizmola]

Why would you make a 2nd table, when you could just add a status column to the users table to indicate whether they are "upgrade" users?

[richiejones24]

Why would you make a 2nd table, when you could just add a status column to the users table to indicate whether they are "upgrade" users?

Its complicated but i cant do that it has to run on its own table.

[gizmola]

Why would you make a 2nd table, when you could just add a status column to the users table to indicate whether they are "upgrade" users?

Its complicated but i cant do that it has to run on its own table.

 

What exactly is complicated?

[richiejones24]

Why would you make a 2nd table, when you could just add a status column to the users table to indicate whether they are "upgrade" users?

Its complicated but i cant do that it has to run on its own table.

 

What exactly is complicated?

 

The User profiles are spread over 5 tables and i use  MySql Join and the username to pull all the information from the tables to make the profile. If i added the upgrade option to one of the tables all i would be able to to is get the information from that table which would be useless i need the username to get the rest of the profile from the different tables.

[gizmola]

No, it sounds like you have a typical relational model where you have related information in seperate tables.  That is normal.  Using joins you can unite the information in one query.  If your user table has a column that indicates this membership status, then all you need do is specify that as criteria either as part of the join condition or in the where clause, and you'll get the result you are looking for. 

 

It is hard for us to advise you more specifically on how to do this type of query without understanding your schema more fully but I assure you that a seperate table is not needed, and not a good design choice.