CODEWITHSUNDEEP
phpmysql
John
John

Reputation: 4944

Excluding a variable when its value is blank

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



?>

Upvotes: 0

Views: 153

Answers (2)

too much php
too much php

Reputation: 90988

It's probably safest to put it in the subquery:

$subQueryParts = array();
foreach($tables as $table)
    $subQueryParts[] = "SELECT site, votes_up FROM $table WHERE LENGTH(site)";
$subQuery = implode(" UNION ALL ", $subQueryParts);

If possible, you should follow Bill Karwin's advice and store all your books in one table. Dynamic table names are very hard to search and manage, and they do not optimize well.

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562270

You shouldn't have separate tables for each book category. I can't believe you have so many books that any of these tables would grow too large. Any scalability benefits you might gain by splitting the table are offset by the complexity of having to do these UNION queries.

Here's what I'd do:

  • Unify the tables into one table.
  • Add a Categories table.
  • Relate books to categories with a many-to-many table.

Then your SQL query becomes much simpler:

$sqlStr = "SELECT site, votes_up FROM Books
           WHERE site IS NOT NULL AND site <> ''
           ORDER BY votes_up DESC LIMIT 25";

Upvotes: 8

Related Questions