Merging results from two mysql databases

deansatch · February 21, 2007

I have made two websites on seperate servers both with a database of their own.

They are identical but with different results in the databases.

I want to make a page on one of the sites that will merge the information from both databases.

e.g.

site 1 has a list in date order

site 2 has a list in date order

I want site 1 to have the list merged together to form one big list but still be in date order. Also I want to be able to have the results of site 2 in a different font colour to site 1.

i.e.

Site 1 results

10th jan - something

15th jan - something else

19th jan - more

Site 2 results

11th jan - even more

13th jan - some more

16th jan - more stuff

Merged page

10th jan - something

11th jan - even more

13th jan - some more

15th jan - something else

16th jan - more stuff

19th jan - more

can anyone help with this?

effigy · February 21, 2007

Create a new table based off the existing ones, but add a site_id column. Afterwards, run two inserts:

INSERT into merged_db.new_table (date, text, site_id) SELECT date, text, 1 from site_1_db.table

INSERT into merged_db.new_table (date, text, site_id) SELECT date, text, 2 from site_2_db.table

deansatch · February 21, 2007

I'm confused.

Basically, these are two sites, one for a band and one for a duo.

I want to be able to merge their two gig lists and highlight the difference between band and duo gigs but only on one site

each site is hosted on completely different servers.

effigy · February 21, 2007

So you want to merge the data for output, but not truly merge the data by creating a new table?

See UNION.

deansatch · February 21, 2007

I did try union at first but I couldn't get it to work from the separate databases. Is it possible this way or does union only work from separate tables in the same database?

I did a sort of

select * from db1

union

select db2

select * from db2

kind of thing but it didn't work. First time using union so I don't really know if I did it right. I just scrapped the whole thing and decided to start again.

Also, once it is merged in my output, will I be able to output it as shown in original post with separate font colours so that each set of results can be distinguished?

i.e.

Site 1 results

10th jan - something

15th jan - something else

19th jan - more

Site 2 results

11th jan - even more

13th jan - some more

16th jan - more stuff

Merged page

10th jan - something

11th jan - even more

13th jan - some more

15th jan - something else

16th jan - more stuff

19th jan - more

effigy · February 21, 2007

Try specifying your tables as database_name.table_name. If you still have trouble, please post the SQL you're using.

deansatch · February 22, 2007

This is what I have so far

$q = mysql_query("(SELECT * FROM table_3)UNION (SELECT * FROM table_3)");

If I do this:

$q = mysql_query("(SELECT * FROM $db1.table_3)UNION (SELECT * FROM $db2.table_3)");

I get an error.

How can I make it so that it takes the details from the right databases and uses the correct login for each database?

Also db1 has different username to db2

magic2goodil · February 22, 2007

Have you tried a 2d array?

Perhaps separate output with it.

Although I can;t think of how to sort them right off the bat besides ksort...But I don't know that it would work correctly on a 2d array but I have yet to try.

effigy · February 22, 2007

What kind of error? If one user cannot access both tables in both databases, I think you have no option but to do this solely in PHP: make two separate connections, two separate queries, array the results, merge the arrays, modify as needed, and output.

deansatch · February 23, 2007

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-content.table_3)' at line 1

This is the error I get. I am not really sure how to connect to the 2 databases before running the query.

TRI0N · February 23, 2007

UNION isn't going to work with 2 differnt Database with differnt Username and Passwords, let alone 2 differnt Databases. It will work with 2 differnt Tables in 1 Database.

The only way to do this is to extract the data like so...

<?
//DATABASE 1 CONNECTION HERE Username/Password etc.

$q1 = mysql_query("SELECT * FROM table_3") ;
while($row = mysql_fetch_row($q1)) {
$example1 = $row[1] ;

//DATABASE 2 CONNECTION HERE Username/Password etc.

$q2 = mysql_query("SELECT * FROM table_3") ;
while($row = mysql_fetch_row($q2)) {
$example2 = $row[1] ;

echo $example1." ".$example2 ;

}
}

deansatch · February 23, 2007

I have half got it. This is what I have so far but it only outputs the results of $db2. Somehow I need to merge the results of each array before sorting them out.$example2 is overriding $example1 in my variable assignments i.e. $date, $venue etc...

How can I make for example, $date1 = $example1 results together with $example2 results?

$connection = mysql_connect($host,$usr,$pwd);
mysql_select_db($db,$connection);
$q1 = mysql_query("SELECT * FROM table_$id WHERE id > 0 ORDER BY date desc") ;
while($row1 = mysql_fetch_array($q1)) {
$example1 = $row1;



$connection = mysql_connect($host2,$usr2,$pwd2);
mysql_select_db($db2,$connection);
$q2 = mysql_query("SELECT * FROM table_1 WHERE id > 0 ORDER BY date desc") ;
while($row2 = mysql_fetch_array($q2)) {
$example2 = $row2;

$date1 = $example1["date"];
$date1 = $example2["date"];
$date = date("l jS F Y", strtotime($date1) );
$venue = $example1["venue"];
$tickets = $example1["tickets"];
$time = $example1["time"];
$address = $example1["address"];
$venue = $example2["venue"];
$tickets = $example2["tickets"];
$time = $example2["time"];
$address = $example2["address"];

TRI0N · February 23, 2007

Alll the examples1 are in conflict becasue $example1 is what ever is in row 1 of the table you are running the query on in $q1

Lets say row 1 is date row 2 is venues...

$q1 = mysql_query("SELECT * FROM table_$id WHERE id > 0 ORDER BY date desc") ;

while($row = mysql_fetch_array($q1)) {

$record_id = $row[0];

$date1 = $row[1];

$venue = $row[2];

$ticket = $row[3];

ETC......

$q2 = mysql_query("SELECT * FROM table_1 WHERE (id = '$record_id')") ;

while($row2 = mysql_fetch_array($q2)) {

$date2 = $row[1];

ETC....

Notices how I removed the ORDER BY from the second query becase its already sorting based on Database 1 results.. Can't sort it again or it will stop parsing data.

deansatch · February 23, 2007

Why (id = '$record_id') ?

I am getting closer to a result with this method. No errors but it isn't putting them in any specific order. I have the merged results though.

$connection = mysql_connect($host,$usr,$pwd);
mysql_select_db($db,$connection);
$q1 = mysql_query("SELECT * FROM table_$id WHERE id > 0 ORDER BY date desc") ;
while($row = mysql_fetch_array($q1)) {
$record_id = $row[0];
$date1 = $row[1];
$date = date("l jS F Y", strtotime($date1) );
$venue = $row[2];
$tickets = $row[3];
$time = $row[5];
$address = $row[4];



$connection2 = mysql_connect($host2,$usr2,$pwd2);
mysql_select_db($db2,$connection2);
$q2 = mysql_query("SELECT * FROM table_1 WHERE (id = '$record_id')") ;
while($row2 = mysql_fetch_array($q2)) {
$date2 = $row2[1];
$date3 = date("l jS F Y", strtotime($date2) );
$venue2 = $row2[2];
$tickets2 = $row2[3];
$time2 = $row2[5];
$address2 = $row2[4];

if ($date1 < $todaysdate){
    
echo '<div class="container">
		<div class="thegig">
		<table>

		<tr>
		<td class="info"></td>

		<td class="dategiggrey">'. $date .'</td>
		<td class="venuegrey">'. $venue .'</td>
		</tr>
		</table>
		</div >
	<div class="information">
		<table class="gigaddressgrey">
		<tr>
		<td>'. $address .'</td>
		</tr><tr>
		<td> '. $time .'</td>
		</tr><tr>
		<td>'. $tickets .'</td>

		</tr>
		</table>
	</div >
	</div >'. "\n";

    $rgb -= $speed;
}  

if ($date2 < $todaysdate){
    
echo '<div class="container">
		<div class="thegig">
		<table>

		<tr>
		<td class="info"></td>

		<td class="dategiggrey">'. $date3 .'</td>
		<td class="venuegrey">'. $venue2 .'</td>
		</tr>
		</table>
		</div >
	<div class="information">
		<table class="gigaddressgrey">
		<tr>
		<td>'. $address2 .'</td>
		</tr><tr>
		<td> '. $time2 .'</td>
		</tr><tr>
		<td>'. $tickets2 .'</td>

		</tr>
		</table>
	</div >
	</div >'. "\n";

    $rgb -= $speed;
}  



if ($date1 >= $todaysdate){
echo '<div class="container">
<div class="thegig">
		<table>
		<tr>
					<td class="info"></td>
<td class="dategigblack">'. $date .'</td>
		<td class="venueblack">'. $venue .'</td>
		</tr>
		</table>
</div >
		<div class="information">
		<table class="gigaddressblack">
		<tr>
		<td>'. $address .'</td>
		</tr><tr>
		<td> '. $time .'</td>
		</tr><tr>
		<td>'. $tickets .'</td>
		</tr>
		</table>
	</div >

	</div >'. "\n";
	    $rgb -= $speed;
			}


if ($date2 >= $todaysdate){
echo '<div class="container">
<div class="thegig">
		<table>
		<tr>
					<td class="info"></td>
<td class="dategigblack">'. $date3 .'</td>
		<td class="venueblack">'. $venue2 .'</td>
		</tr>
		</table>
</div >
		<div class="information">
		<table class="gigaddressblack">
		<tr>
		<td>'. $address2 .'</td>
		</tr><tr>
		<td> '. $time2 .'</td>
		</tr><tr>
		<td>'. $tickets2 .'</td>
		</tr>
		</table>
	</div >

	</div >'. "\n";
	    $rgb -= $speed;
			}

}

}

deansatch · February 23, 2007

$connection = mysql_connect($host,$usr,$pwd);
mysql_select_db($db,$connection);
$q1 = mysql_query("SELECT * FROM table_$id WHERE id > 0 ORDER BY date desc") ;
while($row = mysql_fetch_array($q1)) {
$record_id = $row[0];
$date1 = $row[1];
$date = date("l jS F Y", strtotime($date1) );
$venue = $row[2];
$tickets = $row[3];
$time = $row[5];
$address = $row[4];

echo "<div>";
echo "$date"; 
echo "---"; 
echo "$venue"; 
echo "</div>";

$connection2 = mysql_connect($host2,$usr2,$pwd2);
mysql_select_db($db2,$connection2);
$q2 = mysql_query("SELECT * FROM table_1 WHERE (id = $record_id)") ;
while($row2 = mysql_fetch_array($q2)) {
$date2 = $row2[1];
$date3 = date("l jS F Y", strtotime($date2) );
$venue2 = $row2[2];
$tickets2 = $row2[3];
$time2 = $row2[5];
$address2 = $row2[4];

echo "<div><font style='color:#ccf'>";
echo "$date3"; 
echo "---"; 
echo "$venue2"; 
echo "</font></div>";
}
}

This is the closest I have gotten. This shows $db results in date desc order and merged in to them results it shows $db2 in date random order. All the dates are there they just aren't sorted right.

What is it I am doing wrong?

TRI0N · February 23, 2007

Try this line for the second query.

$q2 = mysql_query("SELECT * FROM table_1 WHERE (id = $record_id AND date = $date1)") ;

deansatch · February 23, 2007

Thanks

That just seems to eliminate the $db2 results completely.

Still not sure why I have the record_id bit aswell

TRI0N · February 23, 2007

Well you need to determin if you need that then.. I'm trying to figure out what it is between the 2 database is suppost to match?

If its just the date then remove the record_id and extract from database 2 the date that is to match whatever row.

deansatch · February 23, 2007

Here is the page in question

http://www.pennysbackinblack.co.uk/pages.php?id=3&c=giglistcomb

The white text is $db

The Blue text is $db2

I want them all in date order

Hope that makes more sense

deansatch · February 23, 2007

*bump*

deansatch · February 24, 2007

Can anyone help with this?

TRI0N · February 24, 2007

Hey sorry I got busy lately.. I do believe you will need to add a new row to your database that has the date in another format other the YYYY/MM/DD in order to do this easy.

You could explode the date before it is entered into a data base and added to 3 differnt colums. year, month, day.

Then extract the data from the database with ORDER BY year DESC, month DESC, day DESC.

TRI0N · February 24, 2007

Ahh I see you didn't have the blue text on the page when I looked yesterday.. Now I see it.. Hummmm thats sure gonna be tricky to have them extract accordingly from 2 Databases and 2 Tables with 2 Logins to sort out correctly...

Perhaps thinking about making them so that Band 1 is in a colum with its listing of events while Band 2 is in its own. Side by side.

Like:

Band 1 | Band 2

Date 1 | Date 1

Date 2 | Date2

Date 3

Date 4

Since there is alot more white listing then blue.

deansatch · February 24, 2007

Thanks

Something I don't understand is why on the second mysql query do I have to use id = $record_id. Why can't it be the same query as the first "WHERE id > 0 ORDER BY date desc"?

And why is it not ordering my second database results?

TRI0N · February 24, 2007

You don't need to use $record_id in fact you should try to pull resulst from the second query from the first query's date. That may fix your problem all together.

WHERE date = '$date1'

Istead of id = '$record_id'

Before you showed me your code as being WHERE id > 0 so I assumed you were trying to match ID's but its date you are trying to match between the 2.

Use the date for the WHERE and see what happens.

Sign In

Merging results from two mysql databases

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information