Data from multiple DB's/tables

[d0nny]

Not sure if this is an SQL question but I suspect it starts with putting the PHP script together.

I have a small application that allows our sales team to submit very simple development requests.

In my request form I have the following fields:
date, user_id, description, team, assigned, request, sub_request,status

This is slightly complex in its makeup as most of these fields are references to other tables and in the case of the user_id, this will hold an id to a different database, as the user authentication is managed by a separate DB, `login`.
The team, assigned, request, sub_requests and status fields are similar in that they reference separate tables in the same DB (`requests`).

So when I want to list all requests, I need to understand how I compile the query to get the names of the id's referenced in the main table.
Are you following?

Basically, I see a lot of WHERE clauses (or probably a join), but I don't know how to reference another database.

As I say, this is probably mainly an SQL question, but I do need to understand how I can write my script to include connections to both databases?

Or is there a better way of doing this?

 

[vacunita]

Assuming the second Database is located in the same machine as the first one you can reference a table in it by adding the db name before the table name and field

at its most basic:

SELECT dbname.tablename.field, dbname2.tablename.field from dbname.tablename, dbname2.tablename WHERE ...


Some more info here:

http://www.dottedidesign.com/node/14

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[d0nny]

Many thanks. I'll give that a try.

Do you know if the connect script (to the database) needs to change?
Fortunately, my databases have a common login and password, but I also specifiy the database in that script.

 

[jpadie]

not if the two databases are on the same server.

 

[vacunita]

The PHP script should not change. Only the query should change.

Note: Make sure that the user in your connection script has rights to both databases.







----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[d0nny]

I've actually tried this and it works very well, so I could just shut up and go away!
But I am trying to understand how this works...

In my PHP script I reference the DB login script like so...

include_once ('./includes/dbc.php

The dbc.php script looks like so ...

$dbname = 'host_login';
$link = mysql_connect("localhost","db_admin","password") or die("Couldn't make connection.");
$db = mysql_select_db($dbname, $link) or die("Couldn't select database");

Now, I'm obviously selecting the database name ($dbname) and connect to that database through the $db variable.
What confuses me is how does my query/script connects to the 'other' database??

 

[jpadie]

the select_db is the equivalent to passing a mysql query of USE databasename.

doing so provides 'hinting' to the mysql engine so that in the absence of a fully qualified field name it can make guesses as to database and table names.

in Vacunita's post he has used fully qualified field names and table names. the qualifications are

database.table.field

when providing full qualification, you do not need to select a database.

 

[DonQuichote]

Now, I'm obviously selecting the database name ($dbname) and connect to that database through the $db variable.
What confuses me is how does my query/script connects to the 'other' database??

In exactly the same way. Suppose you have a "login" database and a "request" database:

$dbLoginName = 'host_login';
$LoginLink = mysql_connect("localhost","db_admin","password") or die("Couldn't make connection.");
$dbLogin = mysql_select_db($dbLoginName, $LoginLink) or die("Couldn't select database");

$dbRequestName = 'request';
$RequestLink = mysql_connect("localhost","db_admin","password", TRUE) or die("Couldn't make connection.");
$dbRequest = mysql_select_db($dbRequestName, $RequestLink) or die("Couldn't select database");

But seriously, you should consider error handling instead of "dying". Don't trouble users with error messages they cannot help, especially not when YOU keep yourself in the dark. So log the errors, have them mailed to you or whatever. They errors should reach you first. The users should only be told what to do (which for an internal error is to try again later).

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)