deansatch Posted February 21, 2007 Share Posted 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? Quote Link to comment Share on other sites More sharing options...
effigy Posted February 21, 2007 Share Posted 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 Quote Link to comment Share on other sites More sharing options...
deansatch Posted February 21, 2007 Author Share Posted 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. Quote Link to comment Share on other sites More sharing options...
effigy Posted February 21, 2007 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
deansatch Posted February 21, 2007 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
effigy Posted February 21, 2007 Share Posted February 21, 2007 Try specifying your tables as database_name.table_name. If you still have trouble, please post the SQL you're using. Quote Link to comment Share on other sites More sharing options...
deansatch Posted February 22, 2007 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
magic2goodil Posted February 22, 2007 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
effigy Posted February 22, 2007 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
deansatch Posted February 23, 2007 Author Share Posted 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. Quote Link to comment Share on other sites More sharing options...
TRI0N Posted February 23, 2007 Share Posted 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 ; } } Quote Link to comment Share on other sites More sharing options...
deansatch Posted February 23, 2007 Author Share Posted 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"]; Quote Link to comment Share on other sites More sharing options...
TRI0N Posted February 23, 2007 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
deansatch Posted February 23, 2007 Author Share Posted 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; } } } Quote Link to comment Share on other sites More sharing options...
deansatch Posted February 23, 2007 Author Share Posted 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? Quote Link to comment Share on other sites More sharing options...
TRI0N Posted February 23, 2007 Share Posted February 23, 2007 Try this line for the second query. $q2 = mysql_query("SELECT * FROM table_1 WHERE (id = $record_id AND date = $date1)") ; Quote Link to comment Share on other sites More sharing options...
deansatch Posted February 23, 2007 Author Share Posted February 23, 2007 Thanks That just seems to eliminate the $db2 results completely. Still not sure why I have the record_id bit aswell Quote Link to comment Share on other sites More sharing options...
TRI0N Posted February 23, 2007 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
deansatch Posted February 23, 2007 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
deansatch Posted February 23, 2007 Author Share Posted February 23, 2007 *bump* Quote Link to comment Share on other sites More sharing options...
deansatch Posted February 24, 2007 Author Share Posted February 24, 2007 Can anyone help with this? Quote Link to comment Share on other sites More sharing options...
TRI0N Posted February 24, 2007 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
TRI0N Posted February 24, 2007 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
deansatch Posted February 24, 2007 Author Share Posted 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? Quote Link to comment Share on other sites More sharing options...
TRI0N Posted February 24, 2007 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.