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