MySQL — Using Joins
In the previous chapters, we were getting data from one table at a time. This is good enough for simple takes, but in most of the real world MySQL usages, you will often need to get data from multiple tables in a single query.
You can use multiple tables in your single SQL query. The act of joining in MySQL refers to smashing two or more tables into a single table.
You can use JOINS in the SELECT, UPDATE and DELETE statements to join the MySQL tables. We will see an example of the LEFT JOIN also which is different from the simple MySQL JOIN.
Using Joins at the Command Prompt
Assume we have two tables tcount_tbl and tutorials_tbl, in TUTORIALS. Now take a look at the examples given below −
Example
root@host# mysql -u root -p password; Enter password:******* mysql> use TUTORIALS; Database changed mysql> SELECT * FROM tcount_tbl; +-----------------+----------------+ | tutorial_author | tutorial_count | +-----------------+----------------+ | mahran | 20 | | mahnaz | NULL | | Jen | NULL | | Gill | 20 | | John Poul | 1 | | Sanjay | 1 | +-----------------+----------------+ 6 rows in set (0.01 sec) mysql> SELECT * from tutorials_tbl; +-------------+----------------+-----------------+-----------------+ | tutorial_id | tutorial_title | tutorial_author | submission_date | +-------------+----------------+-----------------+-----------------+ | 1 | Learn PHP | John Poul | 2007-05-24 | | 2 | Learn MySQL | Abdul S | 2007-05-24 | | 3 | JAVA Tutorial | Sanjay | 2007-05-06 | +-------------+----------------+-----------------+-----------------+ 3 rows in set (0.00 sec) mysql>
Now we can write an SQL query to join these two tables. This query will select all the authors from table tutorials_tbl and will pick up the corresponding number of tutorials from the tcount_tbl.
mysql> SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count -> FROM tutorials_tbl a, tcount_tbl b -> WHERE a.tutorial_author = b.tutorial_author; +-------------+-----------------+----------------+ | tutorial_id | tutorial_author | tutorial_count | +-------------+-----------------+----------------+ | 1 | John Poul | 1 | | 3 | Sanjay | 1 | +-------------+-----------------+----------------+ 2 rows in set (0.01 sec) mysql>
Using Joins in a PHP Script
PHP uses mysqli query() or mysql_query() function to get records from a MySQL tables using Joins. This function takes two parameters and returns TRUE on success or FALSE on failure.
Syntax
Required — SQL query to get records from multiple tables using Join.
Optional — Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.
First create a table in MySQL using following script and insert two records.
create table tcount_tbl( tutorial_author VARCHAR(40) NOT NULL, tutorial_count int ); insert into tcount_tbl values('Mahesh', 3); insert into tcount_tbl values('Suresh', 1); Example
Try the following example to get records from a two tables using Join. −
Copy and paste the following example as mysql_example.php −
connect_errno ) < printf("Connect failed: %s
", $mysqli->connect_error); exit(); > printf('Connected successfully.
'); $sql = 'SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a, tcount_tbl b WHERE a.tutorial_author = b.tutorial_author'; $result = $mysqli->query($sql); if ($result->num_rows > 0) < while($row = $result->fetch_assoc()) < printf("Id: %s, Author: %s, Count: %d
", $row["tutorial_id"], $row["tutorial_author"], $row["tutorial_count"]); > > else < printf('No record found.
'); > mysqli_free_result($result); $mysqli->close(); ?> Output
Access the mysql_example.php deployed on apache web server and verify the output.
Connected successfully. Id: 1, Author: Mahesh, Count: 3 Id: 2, Author: Mahesh, Count: 3 Id: 3, Author: Mahesh, Count: 3 Id: 5, Author: Suresh, Count: 1
MySQL LEFT JOIN
A MySQL left join is different from a simple join. A MySQL LEFT JOIN gives some extra consideration to the table that is on the left.
If I do a LEFT JOIN, I get all the records that match in the same way and IN ADDITION I get an extra record for each unmatched record in the left table of the join: thus ensuring (in my example) that every AUTHOR gets a mention.
Example
Try the following example to understand the LEFT JOIN.
root@host# mysql -u root -p password; Enter password:******* mysql> use TUTORIALS; Database changed mysql> SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count -> FROM tutorials_tbl a LEFT JOIN tcount_tbl b -> ON a.tutorial_author = b.tutorial_author; +-------------+-----------------+----------------+ | tutorial_id | tutorial_author | tutorial_count | +-------------+-----------------+----------------+ | 1 | John Poul | 1 | | 2 | Abdul S | NULL | | 3 | Sanjay | 1 | +-------------+-----------------+----------------+ 3 rows in set (0.02 sec)
You would need to do more practice to become familiar with JOINS. This is slightly a bit complex concept in MySQL/SQL and will become more clear while doing real examples.
How do I construct a cross database query in PHP?
Is there something better than this? Is there a way for me to do a cross db MySQL query in PHP without having to something crazy like (2)?
CLARIFICATION: None of the proposed answers actually let me do a cross db query. Instead, they allow me to access two different DBs separately. I want a solution that allows me to do something like SELECT foreign_db.login.username, firstname, lastname from foreign_db.login, user where . NOT just make different queries to different DBs. For what it’s worth, (2) doesn’t work for me.
After some testing, for whatever reason, my setup doesn’t allow me to do number (2). I always need to use mysql_select_db. Something that does work is to use mysql_select_db to switch between dbs, but this doesn’t allow me to do cross db queries. I’m thinking of something like select * from main_db.login, customerInfo where. etc.
(2) is the correct answer. My PHP server does hundreds of such queries a minute with part of the data from a second database specified by name. So the question is less «How do I do this?» and more «Why isn’t this working?»
After some intense digging around, I got it to work like you would think it should: SELECT FROM * foreigndb.login WHERE. Not sure who gets the bounty. Maybe I can just demote this question. and then cast some reputation points in the charity bin.
9 Answers 9
You will need your databases to run on the same host.
If so, you should be able to use mysql_select_db on your favourite/default db and manually specify a foreign database.
$db = mysql_connect($hots, $user, $password); mysql_select_db('my_most_used_db', $db); $q = mysql_query(" SELECT * FROM table_on_default_db a, `another_db`.`table_on_another_db` b WHERE a.id = b.fk_id "); If your databases run on a different host, you won’t be able to join directly. But you can then make 2 queries.
$db1 = mysql_connect($host1, $user1, $password1); $db2 = mysql_connect($host2, $user2, $password2); $q1 = mysql_query(" SELECT id FROM table WHERE [..your criteria for db1 here..] ", $db1); $tmp = array(); while($val = mysql_fetch_array($q1)) $tmp[] = $val['id']; $q2 = mysql_query(" SELECT * FROM table2 WHERE fk_id in (".implode(', ', $tmp).") ", $db2); After reading your clarification, I am under the impression that you actually want to query tables residing in two separate MySQL server instances. At least, your clarification text:
SELECT foreign_db.login.username, firstname, lastname from foreign_db.login, user where
suggests that you want to run one query while being logged in as two users (which may or may not reside on the same mysql server instance).
In your question, you said you wanted to query data from two different databases, but it is important to realize that one MySQL instance can have many, many databases. For multiple databases managed by the same mysql instance, the solution proposed in the question you linked to simply works: just prefix the table name with the name of the databases, separating database and table names with a dot: . .
But, like i pointed out, this only works if:
- all databases you access in one query reside on the same server — that is, are managed by the same MySQL instance
- the user that is connected to the database has the right privileges to access both tables.
Scenario1: databases on same host: grant appopriate privileges and qualify table names
So if the tables actually reside on the same mysql instance, there is no need for a second login or connection — simply grant the database user you use to connect to the datbase the appropriate privileges to select from all tables you need. You can do that with the GRANT syntax, documented here: http://dev.mysql.com/doc/refman/5.1/en/grant.html
For example, GRANT SELECT ON sakila.film TO ‘test’@’%’ will allow the user test@% to select data from the film table in the sakila database. After doing that, said user can refer to this table using sakila.film (so-called qualified table name), or if the current database is set to sakila , simply as film
Scenario2: databases managed by different MySQL instances: FEDERATED engine
If the tables you want to access are actually managed by two different MySQL instances, there is one trick that may or may not work, depending on your configuration. Since MySQL 5.0 mysql supports the FEDERATED storage engine. This lets you create a table that is not actually a table, but a peephole to a table on a remote server. This engine is documented here: http://dev.mysql.com/doc/refman/5.1/en/federated-storage-engine.html
For example, if you know there is this table in the misc database on the remote host:
CREATE TABLE t ( id int not null primary key , name varchar(10) not null unique ) you can make a local ‘pointer’ to that remote table using this:
CREATE TABLE t ( id int not null primary key , name varchar(10) not null unique ) ENGINE = FEDERATED CONNECTION='mysql://@:/misc/t'; Unfortunately, the FEDERATED engine is not always available, so you have to check first if you can even use that. But suppose it is, then you can simply use the local table t in your queries, just like any other table, and MySQL will communicate with the remote server and perform the appropriate operations on the physical table on the other side.
Caveat: there are several optimization issues with FEDERATED tables. You should find out if and to what extent these apply to you. For instance, applying a WHERE to a federated table can in many cases result in the entire table contents being pullled over the wire to your local server, where the actual filtering will be appplied. Another issue is with table creation: you have to be very sure that the definitions of the federated table and the table it is pointing to match exacty, except for the ENGINE clause (and CONNECTION). If you have for example a different character set, the data may arrive completely garbled after travelling over the wire.
If you want to use FEDERATED tables, do read this article http://oreilly.com/pub/a/databases/2006/08/10/mysql-federated-tables.html to decide if its right for your particular use case.
If you think you do need it, I have a utility to create federated tables here: http://forge.mysql.com/tools/tool.php?id=54
Scenario3: can’t use FEDERATED, but tables on different MySQL instances
Finally, if you have tables on different MySQL instances, but cannot for some reason use the federated table engine, your a out of luck I’m afraid. You are simply going to have to execute queries to both MySQL instances, receive the results and do something intelligent with it in PHP. depending on your exact requirements, this may be a perfectly viable solution
I guess you need to decide for yourself which part of my answer best appeals to your problem, and add a comment in case you need more help. TIA Roland.