SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2009
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Retrieving values from several tables in a MySQL database

    Hello,

    I have a MySQL database called "bookfeather" with several tables that contain list books. Under each table, each book has a given number of votes. The PHP code below allows the user to enter in a book title ($entry), and then returns the total number of votes that book has in all tables ($sum).

    How could I use PHP to make a 2-column, 25-row table that lists the 25 books in the database with the highest value for $sum (in descending order)?

    Thanks in advance,

    John

    Code:
    mysql_connect("mysqlv10", "username", "password") or die(mysql_error());
    mysql_select_db("bookfeather") or die(mysql_error());
    
    // We preform a bit of filtering
      
    $entry = strip_tags($entry);
    $entry = trim ($entry);
    $entry = mysql_real_escape_string($entry);
    
    
    $result = mysql_query("SHOW TABLES FROM bookfeather") 
    or die(mysql_error()); 
    
    $table_list = array();
    while(list($table)= mysql_fetch_row($result))
    {
      $sqlA = "SELECT COUNT(*) FROM `$table` WHERE `site` LIKE '$entry'";
      
      $resA = mysql_query($sqlA) or die("$sqlA:".mysql_error());
      list($isThere) = mysql_fetch_row($resA);
      $isThere = intval($isThere);
      if ($isThere)
      {
         $table_list[] = $table;
      }
    
    }
    
    //$r=mysql_query("SELECT * , votes_up - votes_down AS effective_vote FROM `$table[0]` ORDER BY effective_vote DESC");
    
    
    if(mysql_num_rows($resA)>0){
    foreach ($table_list as $table) { 
        $sql = "SELECT votes_up FROM `$table` WHERE `site` LIKE '$entry'"; 
        $sql1 = mysql_query($sql) or die("$sql:".mysql_error());
       while ($row = mysql_fetch_assoc($sql1)) {
       
    
    	
    
       $votes[$table] = $row['votes_up'];
    
    	
    
       $sum += $row['votes_up'];
           //echo $table . ': "' . $row['votes_up'] . " for $entry from $table\"<br />";
       } 
       
    }
    }
    else{
    print "<p class=\"topic2\">the book \"$entry\" has not been added to any category</p>\n";
    }
    
    
    //within your loop over the DB rows
    //$votes[$table] = $row['votes_up'];
    
    //afterwards
    
    if($sum>0){
    
    
    
    print "<table class=\"navbarb\">\n";
    print "<tr>";
    print "<td class='sitenameb'>".'<a type="amzn" category="books" class="links2b">'.$entry.'</a>'."</td>";
    print "</tr>\n";
    print "</table>\n";
    
    //echo "<p class=\"topic3\">".'<a href="http://'.$entry.'" class="links3">'.$entry.'</a>'. "</p>\n";
    echo "<p class=\"topic4\">". number_format($sum) . ' votes in total.'."</p>\n";

  2. #2
    SitePoint Member
    Join Date
    Dec 2007
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not knowing the exact structure of the table(s), I'll post a generic query:
    select book, count(votes) as vote_count from table group by book order by vote_count desc limit 25

    The above select statement assumes that there is a record for each vote for a given book.
    That's probably not the way you have things structured, but it'll give you the general idea.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you should really consider merging your various books tables into one

    for one thing, you won't have to issue so many queries just to get overall counts

    a query inside a loop, like you have, is inefficient

    even a single UNION query would be better than multiple queries (in a loop)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Member
    Join Date
    Dec 2007
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I agree with r937.

    I also am curious why you've segmented the books into separate tables.

    If it was a database size limitation, that would be understandable, but they're in the same database.


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
  •