Retrieving values from several tables in a MySQL database

[ArizonaJohn]

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

 

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";

[trq]

First a question, why do you have multiple tables of the same structure?