Hello,
The code below works great. I have a MySQL database that contains book titles classified in different categories. In the code below, the variable "site" represents a book title. Each category is represented by a different table in the MySQL database.
The code below ranks the top 25 book titles (site) by total votes across all categories (MySQL tables). I am trying to exclude blank book titles (i. e. when site = ''). How can I do this?
I have tried inserting WHERE site != ''
in a few places but I get an error message. So I guess I'm asking, where can I insert WHERE site != ''
?
Thanks in advance,
John
<?
mysql_connect("mysqlv10", "username", "password") or die(mysql_error());
mysql_select_db("database") or die(mysql_error());
$result = mysql_query("SHOW TABLES");
$tables = array();
while ($row = mysql_fetch_assoc($result)) {
$tables[] = '`'.$row["Tables_in_bookfeather"].'`';
}
$subQuery = "SELECT site, votes_up FROM ".implode(" UNION ALL SELECT site, votes_up FROM ",$tables);
// Create one query that gets the data you need
$sqlStr = "SELECT site, sum(votes_up) sumVotesUp
FROM (
".$subQuery." ) subQuery
GROUP BY site ORDER BY sum(votes_up) DESC LIMIT 25";
$result = mysql_query($sqlStr);
$arr = array();
echo "<table class=\"samples2\">";
while ($row = mysql_fetch_assoc($result)) {
echo '<tr>';
echo '<td class="sitename2"><a href="booklookup3.php?entry='.urlencode($row["site"]).'&searching=yes&search=search">'.$row["site"].'</a></td>';
echo '<td>'.$row["sumVotesUp"].'</td>';
echo '</tr>';
}
echo "</table>";
?>