1

I have an old and a new database with customer data. And now I have to remember if the customer I'm looking for is part of the new system, or the old system before i search - in my application. That's kind of a hassel.
I would like to join the two result-sets together and throw them into one single mega overview in my application - which is a table with a search function.

What would be the best practise for this?

Both the old and new database structure (system) consists of multiple tables joined together in order to get the necessary information needed. And they're not entirely equal.
The new system is splitted into more tables, and there are added a few new ones, to meet the needs when the business expanded their services.

But the result that is returned to the viewer is the same.

Would it be possible to just kind of merge the two arrays, that holds the result-set from the database query, together? - and then do a while-loop or foreach-loop on that?

Not really sure how yet, but if that's a solution I would appreciate some help looking in the right direction..

ThomasK
  • 2,210
  • 3
  • 26
  • 35
  • 2
    This seems odd. Why cant you migrate the old data into the new database? – Steve Dec 02 '13 at 13:05
  • 1
    Are both databases on the same mysql service? Or are they different installations/machines ? Or isn't it even mysql ? – nl-x Dec 02 '13 at 13:12
  • You can do it using query also, provided that you correctly map the old columns into new one. – pratim_b Dec 02 '13 at 13:12
  • @nl-x, why would you assume that he is using mysql? The question is not tagged as such and I don't see anything in the prose. – Dan Bracuk Dec 02 '13 at 13:17
  • Earlier the business only provided tuning to cars, but now they also provide tuning for tractor, trucks and marine. Also they needed a way for each retailer to have the opportunity to register the tuning to them self as the owner, and have the customer assigned as the user... This made it quite hard to migrate directly. So I created this new system for "better flexibility". Anyway. It is MySQL. Both databases is on the same server.. – ThomasK Dec 02 '13 at 13:18
  • @DanBracuk : that's why I ask if it's Mysql... And I guess 80%+ of php uses mysql for database. – nl-x Dec 02 '13 at 13:18

2 Answers2

1

Generally, you can combine results from multiple selects or tables using UNION or even UNION ALL on the database level. You just have to take care that your tables/selects return the same columns.

For example:

-- old table/database
select t.customername as name, t.street as street, t.city as city 
from old_data.customers t
where t.name like 'M%'
union 
- new table/database
select p.name as name, a.street as street, a.city as city
from new_data.person p
inner join new_data.address a on p.address_id = a.id
where p.name like 'M%'

With UNION it does not matter which names the tables or columns in the single SELECT statements have. What counts is that there are the same amount of columns and that they have compatible types.

The actual problem may lie in accessing the tables from multiple databases. The solution for that depends on the DBMS you are using and where your old and new databases are located.

If both databases are stored on the same server, as in your case, most DMBS (such as MySQL) will work for the query above (you prepend the database name to the table name as in database.table and you are good).

If both databases were not stored on the same server, then the solution would depend on the DBMS. Oracle, for example, lets you specify database links, which you can use to span queries across multiple servers. Or, you can cache remote tables using materialized views.

With MySQL, there are different possibilities, which are discussed in this question, e.g.

Community
  • 1
  • 1
Stefan Winkler
  • 3,871
  • 1
  • 18
  • 35
  • In yhis case. Is it possible to do JOINs. The old database has its own joins, and the new has its own? Or could this be too heavy on the server? – ThomasK Dec 02 '13 at 13:31
  • I don't think that should be a problem. As long as the tables have indexes and the search does not return too many results. (If so, the database would have to temporarily store retrieved rows in a file instead of in-memory. But you'd get those problems on the client-side as well, if you'd first fetch all results from both databases and combine them there.) – Stefan Winkler Dec 02 '13 at 15:09
  • Quick question; How do I make a connection spanning multiple databases with PDO? I have: `$con_all = new PDO('mysql:host=localhost; dbname=single_db_name', 'username', 'password', $con_options);` The user assigned to this particular connection has access to the needed tables with the necessary permissions... – ThomasK Dec 02 '13 at 15:50
  • You can create a connection exactly like that. The dbname is just the default database name. So if you write `SELECT foo FROM bar` then MySQL knows that you mean `single_db_name.bar`. From the same connection you can also execute `SELECT foo from other_database.bar` to access the other database (as long as the user has access to the specified table). – Stefan Winkler Dec 03 '13 at 14:03
0

Since both databases are on the same machine/service and we're talking mysql, you can actually even write 2 queries and union them. Or use any clever query while pointing to any database you'd like:

select id, name from db1.user UNION select id, name from db2.user;

This will even work if db1.user has a different structure then db2.user. As long as at least id and name are the same types...

nl-x
  • 11,762
  • 7
  • 33
  • 61
  • Do the column-names have to be the same in both tables? When I created the new system i named it a bit different without wven thinking about it. I used the name I was used to use.. Big mistake maybe? – ThomasK Dec 02 '13 at 13:27
  • @YatinTrivedi what do you mean? `db1.` points to the first db. `db2.` to the second... You don't need to select database, like with `mysqli_select_db()` , if that is what you mean. – nl-x Dec 02 '13 at 13:36