SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    imagine no limitations exbabylon's Avatar
    Join Date
    Dec 2000
    Location
    Idaho, USA
    Posts
    452
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Query Help Needed

    I am trying to save hours of coding to have a script tun cron, and a whole big mess... and I'm positive it can be done... I just lack the knowledge to do so... I am trying to find out the top, say 10 posters in my system. However, the posts are located on two seperate tables depending upon the type of post. Here is my table structure:

    data1
    Code:
    id
    author_name
    date
    content
    data2
    Code:
    id
    author_name
    date
    content
    user
    Code:
    id
    username
    date
    password
    Of course each table has more columns, but that should be enough to be able to deal with this I hope. I am trying to get a list of the top 10 posters in the last, say, 5 days... Here is my current query... it works for one of the tables, but not for both of them...

    Code:
    SELECT user.username, COUNT(*) AS NumPosts FROM data1, user WHERE data1.author_name = user.username and data1.date + 0 > CURDATE() - 5 GROUP BY data1.author_name ORDER BY NumPosts DESC;
    You can see that that works like it should... no problems!

    However, I need to have a SQL which draws the data out of TWO tables of identical build. I tried this but it didn't work.

    Code:
    SELECT user.username, COUNT(*) AS NumPosts FROM data1, data2, user WHERE (data1.author_name = user.username and data1.date + 0 > CURDATE() - 5) and (data2.author_name = user.username and data2.date + 0 > CURDATE() - 5) GROUP BY data1.author_name and data2.author_name ORDER BY NumPosts DESC;
    Any Ideas?

    Thank You all!
    Blamestorming: Sitting around in a group discussing why a deadline was missed or a project failed and who was responsible.

    Exbabylon- Professional Internet Services

  2. #2
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If MySQL were a true RDMS supporting ANSI SQL92 you would be able to use UNION to achieve exactly what you want to do. Quite frankly, I don't know why we are all using MySQL as much as many of us here on this forum do. Most of the time performance is not an issue and I've heard it rumoured that PostgreSQL 7 is pretty fast an certainly suitable for most web sites. Then there is interbase too. There are things that MySQL probably still excels at like full text searching of large text fields (which are just case-sensitive blobs), etc. I'm sure you didn't want to hear a rant. However, I just wonder why we all compromise ourselves so much with MySQL. Its implementation of SQL is pathetic. It forces the programmer to write code to do the things that any self-respecting RDMS would do for itself. Foreign key integrity, cascading updates and deletes, sub-queries, UNION!

    End of rant

    I remember reading in the manual about simulating a union using joins. It did not look pretty and I didn't retain any of it. The other thing they recommend is to create a temporary table to simulate the union. I've never used temporary tables.

    I would do something like this. Execute two seperate sql queries to pull the data out of the two tables. Grab the top ten posters from each table. Then put the results into an associative array like this:
    PHP Code:

    $tables 
    = array("Data1""Data2");

    foreach(
    $tables as $tableName) {
       
    $sql "SELECT username, COUNT(*) AS numPosts 
               FROM 
    $tableName, user 
               WHERE author_name = username 
               AND data1.date + 0 > CURDATE() - 5 
               GROUP BY author_name 
               ORDER BY NumPosts DESC
               LIMIT 10"
    ;
            
       
    $result mysql_query($sql);

       while(
    $row mysql_fetch_array($result)) {
          
    extract($row);
          
    $topPosters[$username] += $numPosts;
       }
    }        

    arsort($topPosters);

    // arsort($topPosters) will reverse sort (descending order) the
    // elements of the associative array maintaining the association
    // between the element key and its value. So, the first ten elements
    // should now contain the top ten posters in descending order.
    // To print them out ...

    for ( $i 0$i 10$i++) {
       list(
    $key$val) = $topPosters[$i]; // hmm, will this work*
       
    echo "Username $key posts $val";

    * if that doesn't work then you will have to invent some other creative way of only extracting the first ten elements from the sorted array. But I think my code should work.

  3. #3
    imagine no limitations exbabylon's Avatar
    Join Date
    Dec 2000
    Location
    Idaho, USA
    Posts
    452
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thank You...

    Thank you... really appreciate it, I wanted to try to do it with SQL, but apparently I can't Oh well, your code worked, although it was a little bit different than the code I was going to use. Your code was producing some errors... oh well, it was that line of code which you weren't sure on. I just threw it into a foreach()... simple enough.

    PHP Code:
    $i 0;
    foreach(
    $topPosters as $username=>$posts){
        if(
    $i 10){
            echo(
    "$username has $posts posts<br>");
        }
        
    $i++;

    I've used PostgreSQL, not positive what version, and noticed ZERO difference in speed.... mabey I should consider starting to work with that.

    Thank you again!

    God Bless,

    Alex Stanton
    Blamestorming: Sitting around in a group discussing why a deadline was missed or a project failed and who was responsible.

    Exbabylon- Professional Internet Services

  4. #4
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Current versions of MySQL (as of 3.23.25) support MERGE tables, which allow you to define a table that is simply a combination of the records of two or more other tables with identical columns/indexes/etc.

    Here's all the info you need: MERGE Tables
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference


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
  •