[SOLVED] Multiple mysql databases

[Lyleyboy]

Hi all,

 

I'm after some advice. I am reasonable with php mysql but not great. What I have is a quickly growing project and database size limitations (Host driven).

What I would like to do is to use two databases. At the moment I use one for all the users and photo galleries but I'd like to use a different one for the next bit of the project.

 

My question is can I have two running together.

If I have two comm includes at the start of the file will mysql just find the correct table from my queries.

 

I hope I have explained this well enough but I'm not certain. If you need any more help let me know.

[Highlander]

Yea, you need one connection to each database.

 

No, MySQL will not know in which database the given table is, you will have to tell him, by using the appropriate connection.

 

 

[Lyleyboy]

Thanks for that. My code is below (And not working)

 

$handle_db1 = mysql_connect("localhost", "discover_lyle", "******") or die(mysql_error());
  $handle_db2 = mysql_connect("localhost", "discover_lyle", "******") or die(mysql_error());
  
  mysql_select_db("discover_discovery",$handle_db1);
  mysql_select_db("discover_messaging",$handle_db2); 

  //do a query from db1:
  $query = "select * from users"; $which = $handle_db1;
  $result = mysql_query($query,$which) or die(mysql_error());
    while($row = mysql_fetch_array($result)){
      echo $row['username'] . "<br/>";
    }
  

  //do a query from db2 :
  $query = "select * from dummy"; $which = $handle_db2;
  $result = mysql_query($query,$which) or die(mysql_error());
    while($row = mysql_fetch_array($result)){
      echo $row['id'] . "<br/>";
    }

 

My error is

Table 'discover_messaging.users' doesn't exist

 

Any help would be cool.

[wildteen88]

If the databases both relate to each other then you should only use one database. You should read up on database normalisation for how to setup your database/tables properly.

 

But the real issue is with mysql_connect. As quoted from the manual

If a second call is made to mysql_connect()  with the same arguments' date=' no new link will be established, but instead, the link identifier of the already opened link will be returned.[/quote']

You'll want set the forth parameter for mysql_connect to true.

  $handle_db2 = mysql_connect("localhost", "discover_lyle", "******", true) or die(mysql_error());

This will force a new connection. Your code should now work as expected.