SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Member
    Join Date
    Nov 2010
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    array_merge sort by date from 3 different DB's

    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 Code:
    <?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!

  2. #2
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,014
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    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.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  3. #3
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think you'll need to do something along these lines.

    1) create an array for the date and news data

    Code:
     
    $newsA = array();
    2) then for each db, run a query to get the data from that database and add it to the array in 1)

    Code:
     
    $query = 'select fldDate, fldNews from tblNews';
    $rs = mysql_query($query,$conn);
    while($row=mysql_fetch_assoc($rs)) {
        $newsA[$row['fldDate']] = $row['fldNews'];
    }
    3) then sort the news array on date (the array key)

    Code:
     
    ksort($newsA);

  4. #4
    SitePoint Zealot
    Join Date
    Jun 2010
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Kalon, your example "works" only if date field is unique. What if there are multiple news with the same date?

  5. #5
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yep, good point

    I missed that "little" detail

    that makes it a little more tricky.

  6. #6
    Unobtrusively zen silver trophybronze trophy
    paul_wilkins's Avatar
    Join Date
    Jan 2007
    Location
    Christchurch, New Zealand
    Posts
    14,702
    Mentioned
    101 Post(s)
    Tagged
    4 Thread(s)
    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.
    Programming Group Advisor
    Reference: JavaScript, Quirksmode Validate: HTML Validation, JSLint
    Car is to Carpet as Java is to JavaScript

  7. #7
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

    Code:
     
    <?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 so I'm not sure if it is a better solution - probably not.

  8. #8
    SitePoint Zealot
    Join Date
    Jun 2010
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  9. #9
    SitePoint Member
    Join Date
    Nov 2010
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  10. #10
    SitePoint Member
    Join Date
    Nov 2010
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think we go it!
    PHP Code:
    $news_array = array ();
            
    $news_query "select date, headline from $db1 ORDER by date";
            
    $news_results mysql_query$news_query$ADAdb ) or die ( "$news_querymysql_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_querymysql_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_querymysql_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 $i $total_count $i++) 
            { 
                echo 
    $news_array[$i][0]. "  ".$news_array[$i][1]."<br><br>" ;
            } 

  11. #11
    SitePoint Zealot
    Join Date
    Jun 2010
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So every time someone connects to the website, you query 3 databases for every single possible item and then you sort it via php?

  12. #12
    SitePoint Member
    Join Date
    Nov 2010
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  13. #13
    SitePoint Zealot
    Join Date
    Jun 2010
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  14. #14
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Blue. View Post
    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.
    yep agree in general

    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •