Retrieving values from several tables in a MySQL database

Question

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

Answer 1

Try something like this. All of this hasn't been tested so please add comments for changes. I'll work with you to get the code right.

// After getting your array of tables formated like 
$tableArray = array("`tableA`", "`tableB`", "`tableC`");

// create a table statement
$tableStatement = implode(", ", $tableArray);

// create a join statement
$joinStatement = "";
for ($i = 1; $i < count($tableArray); $i++) {
    if ($joinStatement != "")
        $joinStatement .= " AND "; 

    $joinStatement .= $tableArray[0] . ".site = " . $tableArray[$i] . ".site"
}

$firstTable = $tableArray[0];

$sql = "SELECT SUM(votes_up) FROM " . $tableStatement . " WHERE " . $joinStatement . " AND " . $firstTable . ".site LIKE '" . $entry . "' GROUP BY " . $firstTable . ".site ORDER BY SUM(votes_up) DESC";

Edit -------- I now realize that the query above won't work perfectly because votes_up will be ambiguous. Also because you probably want to be doing joins that grab records that are only in one table. I think the concept is the right direction even though the query may not be perfect.

You can do something like $selectStatement = "SUM(tableA.votes_up) + SUM(tableB.votes_up) as total_votes_up"