array_merge sort by date from 3 different DB's

pikeymick · November 30, 2010, 10:05pm

Hi I’m trying to aggregate news items from 3 different databases, sort them by date (the combined individual rows from each database), and then print them in order by date (regardless of source). This is the code I’ve come up with, but I’m stuck on getting array_merge to work, and then to be able to sort by date…

<?php
$connection1="localhost";
$username1="user";
$password1="pass";
$database1="db1";

$connection2="localhost";
$username2="user";
$password2="pass";
$database2="db2";

$connection3="localhost";
$username3="user";
$password3="pass";
$database3="db3";
	
	//1 Database Connection
	$1db = mysql_connect($connection1,$username1,$password1) or die("Website is temporarily down for maintenance. We will bring it up as soon as possible. Sorry for any inconvenience. " . mysql_error());
	echo "<br> Connected to 1 Database";
	$sel1 = mysql_select_db($database1, $1db);
	$query1 = "SELECT * FROM db1 ";
	$result1 = mysql_query($query1, $1db) or die("<br> Query result 1 error " . mysql_error());;   
	
	//2 Database Connection
	$2db = mysql_connect($connection2,$username2,$password2) or die("Website is temporarily down for maintenance. We will bring it up as soon as possible. Sorry for any inconvenience. " . mysql_error());
	echo "<br> Connected to 2 Database";
	$sel2 = mysql_select_db($database2, $2db);
	$query2 = "SELECT * FROM adanews ";
	$result2 = mysql_query($query2, $2db) or die("<br> Query result 2 error " . mysql_error());;
	
	//3 Database Connection
	$3db = mysql_connect($connection3,$username3,$password3) or die("Website is temporarily down for maintenance. We will bring it up as soon as possible. Sorry for any inconvenience. " . mysql_error());
	echo "<br> Connected to 3 Database";
	$sel3 = mysql_select_db($database3, $3db);
	$query3 = "SELECT * FROM news ";
	//$result3 = mysql_query("SELECT * FROM table;") or die(mysql_error());
	$result3 = mysql_query($query3, $3db) or die("<br> Query result 3 error " . mysql_error());

	//Here is the loop that will collect news items into an array, sort by date, and echo out the sorted results
			
		$result = array_merge($result2, $result3, $result1);
		$headline = stripslashes($result['headline']);
		print_r($result);	
?>

I have no idea how to now sort $result by date, as I can’t seem to test whether the array’s have merged… crap. Thanks for any help/advice!

SpacePhoenix · December 1, 2010, 7:22am

Do all three databases reside on the same server? If so you can save a little overhead by only connecting to the server once and just selecting which database you want to use.

In any case the arrays won’t have merged as there would have been no results sets to merge, for each lot of news you need to use a while loop to read the results into their own results set.

What fields does the “news” table have in each db, depending on what fields they have (and field types) it may be possible to do all the merging and sorting with one query.

system · December 1, 2010, 8:06am

I think you’ll need to do something along these lines.

create an array for the date and news data

 
$newsA = array();

then for each db, run a query to get the data from that database and add it to the array in 1)

 
$query = 'select fldDate, fldNews from tblNews';
$rs = mysql_query($query,$conn);
while($row=mysql_fetch_assoc($rs)) {
    $newsA[$row['fldDate']] = $row['fldNews'];
}

then sort the news array on date (the array key)

 
ksort($newsA);

Blue1 · December 1, 2010, 9:13am

Kalon, your example “works” only if date field is unique. What if there are multiple news with the same date?

system · December 1, 2010, 9:24am

yep, good point

I missed that “little” detail :injured:

that makes it a little more tricky.

Paul_Wilkins · December 1, 2010, 9:32am

When dealing with multiple items with potentially the same key, just store them in an ordinary array, then sort using usort where you specify a comparison function that compares the date key of each item.

Example #2 on the usort documentation page shows how where it compares the ‘fruit’ key of each item.

If the dates are on differently named keys, you can either pre-process the arrays so that the dates all have the same key, or you can make the comparison function smarter and have it get the possible date from one of multiple keys.

system · December 1, 2010, 10:02am

another way is to assign each distinct date to a key and then the value of each date will be an array containing the news items for that date.

perhaps something like this

 
<?php
$newsA = array('2010-11-5' => array('news1','news2'),
    '2010-11-1' => 'news3',
        );
 
//add a news item to '2010-11-5'
array_push($newsA['2010-11-5'],'news4');
 
//now sort the array
ksort($newsA);
 
//display the array
print_r($newsA);
?>

this works but my head is starting to :weyes: so I’m not sure if it is a better solution - probably not.

Blue1 · December 1, 2010, 12:59pm

If there’s something I dislike, it’s pulling a dataset from the database and then use PHP to sort it. There is ALWAYS a better solution - for example, let the database handle it.
What’s the reason you have to pull the news from 3 different databases anyway? Are the databases on the same host? Are the news tables of the same layout? How many news items are there? Are you sure you want to show every single news entry from each of the 3 databases? What if there are tens of thousands of entries in each of the tables?
Why are news stored in 3 databases in the first place?
There are so many questions regarding this “feature”, it seems so pointless using PHP to sort a database result. Let the database do what it does best - store and manipulate data. Use PHP only to display the result set. That’s how things should work.

pikeymick · December 1, 2010, 4:17pm

Hey All,

Thanks again for all of your ideas! To answer a couple of questions:

The news is to be pulled from three different databases because they are three different websites with three different databases, but they are all on our VPS server so they are sharing the database server. However they have different login credentials as they are three separate projects.

The tables are of similar layout in that we will be pulling the same keys, $date, $headline, and $abstract.

pikeymick · December 1, 2010, 4:48pm

I think we go it!

$news_array = array ();
		$news_query = "select date, headline from $db1 ORDER by date";
		$news_results = mysql_query( $news_query, $ADAdb ) or die ( "$news_query" . mysql_error() );
		$total_count = mysql_num_rows($news_results) ;
		
			 while ( $news_item = mysql_fetch_row ($news_results) )
				{
				   array_push($news_array, $news_item);
				}
		
		$news_query = "select date, headline from $db2 ORDER by date";
		$news_results = mysql_query( $news_query, $PASdb ) or die ( "$news_query" . mysql_error() );
		$total_count += mysql_num_rows($news_results) ;
		
			 while ( $news_item = mysql_fetch_row ($news_results) )
				{
				   //echo $news_item['0']." ".$news_item['1']."<br>";
				   array_push($news_array, $news_item);
				}
		
		$news_query = "select date, headline from $db3 ORDER by date";
		$news_results = mysql_query( $news_query, $CODdb ) or die ( "$news_query" . mysql_error() );
		$total_count += mysql_num_rows($news_results) ;
		
			 while ( $news_item = mysql_fetch_row ($news_results) )
				{
				   //echo $news_item['0']." ".$news_item['1']."<br>";
				   array_push($news_array, $news_item);
				}
		
		sort($news_array);
		for ($i = 0 ; $i < $total_count ; $i++) 
		{ 
			echo $news_array[$i][0]. "  ".$news_array[$i][1]."<br><br>" ;
		}

Blue1 · December 1, 2010, 7:03pm

So every time someone connects to the website, you query 3 databases for every single possible item and then you sort it via php?

pikeymick · December 1, 2010, 7:29pm

No, every time someone visits the website we query three databases for the latests 3 articles.

That code was just getting it to work.

What would you have as the best way to do this reworking mysql to output this information seems like a lot more work than writing php to do it?

Blue1 · December 1, 2010, 7:36pm

Well first off, it depends on the level of control I have over the system, PHP / OS and so on.
I’d definitely go for something like a file that caches the result of the queries so they would be executed only when new entries are added.
Now, as for implementation of such thing - as I said, it depends on the level of control you have.
You can either modify the scripts that store those news items so that every time you add a new entry, you query remaining 2 databases and you create a file that caches the result, or there are more sophisticated ways such as modifying MySQL with UDFs so you can execute external programs, which means you can create a trigger invoking a php script that does the caching. There’s also a cron job and so on, many ways how you can achieve this. But one thing is for sure - I’d definitely cache it and create the cache once new items are added to any of the databases/tables.

system · December 1, 2010, 9:19pm

yep agree in general :agree:

but at the time, not knowing the full circumstances behind the OP’s scenario and given he/she wanted to sort mutiple news items for a gven date by date, sorting them using php as I did is one way given that it’s not a lot of extra code.

if nothing else, it’s an exercise in manipulating and sorting 2 dimensional arrays