Using two database.

Magneto

OK, I've made a very simple news script:

<?php
mysql_connect("$dbhost", "$dbuser", "$dbpass") or die(mysql_error());
mysql_select_db("jk") or die(mysql_error());

$result = mysql_query("SELECT * FROM news ORDER BY time DESC LIMIT 10")
or die(mysql_error());

echo "<table border='0' align='center' width='100%'>";
echo "<tr> <th><div align='left'><u>Date</u></div></th> <th><div align='left'>&nbsp;<u>Title</u></div></th> <th><div align='left'><u>Category</u></div></th></tr>";

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

echo "<tr><td>";
echo $row['date'];
echo "</td><td width='100%'>";
echo "&nbsp;<a href=news.php?id=$row[id]>".$row['title']."</a>";
echo "</td><td>";
echo "<div align='center'>".$row['category']."</div>";
echo "</td></tr>";
}
echo "</table>";

?>

As you can probably see the script just selects the data from the DB and outputs it in a table. What I need this to do is somehow connect to two databases, retrive and combine the results, then output it in the same manor. I have no idea where to start to tell the truth, can anyone help me?

leatherback

You could add the output into an array, and append to that array for the second database?

Magneto

Sounds good, but how would I go about doing that? An example would be good. 🙂

toplay

Magneto wrote:
OK, I've made a very simple news script:
As you can probably see the script just selects the data from the DB and outputs it in a table. What I need this to do is somehow connect to two databases, retrive and combine the results, then output it in the same manor. I have no idea where to start to tell the truth, can anyone help me?

Did you mean to say another table instead of database? If it's another database, then the syntax to use for columns and in the 'FROM' is:

database_name.table_name.column_name

For different tables in the same database, then you can leave off the "database_name." part.

From what I understand you want, you can do this in one SQL query using Joins. Joins can get complicated depending on what you want.

There are MySQL/Join tutorial links found in my signature line under "MySQL:".

This one is pretty good:
http://www.keithjbrown.co.uk/vworks/mysql/mysql_p5.shtml

Magneto

Sorry, I don't think I explained this right. 🙂

I have five web sites and each of them has It's own database. I have created another database (called network). I intend to use this new database as sort of the network backbone and one of the things I intend it to do is store all network news. I want to alter the script above (which is used on all sites) so it includes news from the network database.

I want this to be integrated into that script, so any global and individual site news are combined and ordered by date (like above). Does that make sense?

Thanks for all the help so far. 🙂

halojoy

hey!
🙂

Of course this can be done,

but is always a bit trickier when you already have 4 different databases
and maybe 4 different websites with (different) php scripts

When you get a new idea for a feature, to add on to an existing script/application
it is not sure this idea can easily be tweaked into your code.
I have seen forum applications under development, where the code
from beginning was not fit to be ready for some new requested features.

If this idea would have been present already from beginning, the whole forum scipts, with variables and includes and pages, would have been arranged different.
To be able to feature this idea in a proper, fast, effective and easy way.

It is always better, if and when we can re-start from scratch,
and take knowledge, experiences we learned along the way with us,
when plan, outline and writing this latest new fully featured version.

Now forum application developers, that go public with BETA or NOT FINAL versions,
will face backwards compatibility problem in same way as OS-system, like Windows, and Browser devlopers do.
You want to be able to run your old programs and old web pages in new OS-system/browser.

And you, in this case, want to be able to use your old databases' data,
even if you re-write your scripts.

I am sure, if you show more details and some code you have, that is involved,
this will be solved.
Sounds not too difficult, even if it takes a lot of work and tweaking before it runs okay.

Good luck!
/halojoy

🙂

Magneto

Thanks for your post, halojoy. 🙂

As I said I have five site's, all of which run off of more or less the same code. This news script however is constant thoughout. The script is as follows:

<?php
mysql_connect("$dbhost", "$dbuser", "$dbpass") or die(mysql_error());
mysql_select_db("jk") or die(mysql_error());

$result = mysql_query("SELECT * FROM news ORDER BY time DESC LIMIT 10")
or die(mysql_error());

echo "<table border='0' align='center' width='100%'>";
echo "<tr> <th><div align='left'><u>Date</u></div></th> <th><div align='left'>&nbsp;<u>Title</u></div></th> <th><div align='left'><u>Category</u></div></th></tr>";

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

echo "<tr><td>";
echo $row['date'];
echo "</td><td width='100%'>";
echo "&nbsp;<a href=news.php?id=$row[id]>".$row['title']."</a>";
echo "</td><td>";
echo "<div align='center'>".$row['category']."</div>";
echo "</td></tr>";
}
echo "</table>";

?>

My database structure of the news table (this structure is used on all five sites and the 'network' database):

CREATE TABLE `news` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(255) NOT NULL default '',
  `body` text NOT NULL,
  `date` date default NULL,
  `views` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=5 ;

All five sites are independant, they have seperate databases, they have their own news. I wish to use the 'network' database as a database to store network news. I wish the network news to be announced globally on all sites.

The above script produces this:

I need this script to open two databases, look for 10 results on each. Essentially at this point we'll have 20 results. I then need to combine these results into ten, ordered by date descending. Does that make a bit more sense to anyone? 😃