Create A Trending Box In PHP With MySQL & HTML

Trending boxes can be seen is social networks. Facebook, Twitter, Google + have it. Why not create one of your own. You can create your own using HTML and add styling by CSS. We will use PHP for updating most searched queries in MySQL database.

How the trending box looks like :

We need a table for storing the search queries. For this we are going to create a table named “trends”. Execute this SQL code to create the table

CREATE TABLE IF NOT EXISTS `trends` (
 `title` varchar(50) NOT NULL,
 `hits` int(11) NOT NULL,
 PRIMARY KEY (`title`),
 UNIQUE KEY `title` (`title`),
 KEY `title_2` (`title`),
 KEY `hits` (`hits`),
 FULLTEXT KEY `title_3` (`title`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

When a user searches for a query in your search page, it should be automatically added to the “trends” table. To automatically add the queries, append the following code at the end or beginning of your search page. The $q variable should contain the search query.

<?
$q="query";/* The Search Query */
if($q!=''){
 $sql=$dbh->prepare("UPDATE trends SET hits=hits+1 WHERE title=?");
 $sql->execute(array($q));
}
/* If There isn't a seacrh query in table, insert it */
if($sql->rowCount()==0 && $q!=""){
 $sql=$dbh->prepare("INSERT INTO trends(title,hits) VALUES(?,'1')");
 $sql->execute(array($q)); 
}
?>

When a user searches for a query, the script updates the value “hits” to +1 (“hits” + 1). If it wasn’t successfully updated, the query is inserted in to the table with “hist” as “1”.

The “hits” field determines the popularity of the query. The query with the most “hits” value is the most trending query.

To display the trending box, we get the first 10 popular queries (highest “hits” value) from the table. Then it is displayed in the descending order of the “hits”.

<div class="trending">
 <font size="5" style="color:rgb(0,250,120);">Trending</font>
 <div class="trends">
  <?
   $sql=$db->query("SELECT * FROM trends ORDER BY hits DESC LIMIT 9");
   foreach($sql as $r){
    $query=$r['title'];
    $wdot=strlen($query)>=14 ? "....":"";
    $sp_t=str_split($query,14);
    $sp_t=$sp_t[0];
    echo '<div style="padding:1px;">';
     echo '<a href="search.php?query='.urlencode($query).'" title="'.$query.'">'.$sp_t."$wdot</a>";
     echo '<div style="margin-top:5px;"></div>'; // Adds A Break like <br/>
    echo "</div>";
   }
  ?>
 </div>
</div>

If you want to delete the old non popular queries from table, add the following code where as you like :

<?
$db->query("DELETE FROM trend WHERE hits=(SELECT MIN(hits) FROM (SELECT * FROM trend HAVING COUNT(hits)>150) x);");
?>

Yes, the “x” should be in the query or you would get the derived table error (#1248).

Add this CSS code to design the trending box :

<style>
.trending{
 background: black;
 border-radius: 10px;
 padding:15px;
}
.trending .trends{
 color:#fff;
 padding-left:10px;
 margin-top:10px;
}
</style>

The finished product will look something like this :