Printing out Unique Values From Several Tables in Reverse Chronological Order

Question

Hello,

I have a MySQL database with multiple tables, each of which have the same structure. The structure is:

id INT(11) NOT NULL auto_increment, site VARCHAR(350) NOT NULL, votes_up BIGINT(9) NOT NULL, votes_down BIGINT(9) NOT NULL, PRIMARY KEY(id), UNIQUE (site)

The PHP code below prints out each unique "site" from across all the tables and sums the "votes_up" it has from all tables. It then lists the top 25 values for "site" (based on total "votes_up") in descending order.

This code works great, but I would like to do the exact same thing based on create time in reverse-chronological order. How do I do this?

Thanks in advance,

John

<?

mysql_connect("mysqlv10", "username", "password") or die(mysql_error());
mysql_select_db("bookfeather") 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);

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

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


?>

Answer 1

As said in the comments, you will have to add a column which would store the time when the row was created. This column can be of type INT if you are going to store UNIX timestamps or DATETIME if you wish to use date strings. Also, if you don't want to do that, you can use ORDER BY id DESC, because auto-increment columns most often correspond to the chronological order, but that's not a stable solution, because when the AUTO_INCREMENT value runs out of the range of the INT data type, it starts using unused values (e.g. from deleted rows).