How to create a "Top Queries Cloud" with PHP

For site owners who would like to add a very important feature to their sites.
The Cloud represents the search queries you want visitors to notice. It is a number of links to a search results page, whether you are using your own search engine, a Google Custom Search, or you want to link to an exterior search engine in this post I will give you a fully detailed tutorial and code for creating the Cloud.

This code I will put here is in PHP, you don't need to be some kind of super genius to apply it ! I am not genius neither for making it !

The main particular thing about Queries Clouds is the different size between different queries, this is proved to have a great effect to attracting visitors, that will increase your chances of visitors clicking on the queries.

Look at this example from my site http://www.torrento.free.fr/ (Torrents Search) :

Top Queries Cloud :

phineas and ferb season 1 . 2008 Incredible Hulk . Pineapple Express . softcam viaccess . music ace . Snow.Queen.2002 . Wow:TBC . Snow Queen . Get Smart Season 1



You may have noticed that you are more likely to read each querie, that's the magic of Queries Clouds.

I will give you the full thing to get a similar result, and as a bonus I will give you a code with different colors ; )


So let's get started :

We will make three different PHP files and one SQL query for the table .

- query_treat.php : it will take care of getting the searched query and inserting it into the database, with counting the most searched queries. This file you must include it into the search results page. If you your cloud will link to an exterior search engine for which you don't have access to the results page you will have to fill the database manually or anyway you chose.

- show_cloud.php : will read the queries from the database and show in the magic way you saw on my site.

- cloud_config.php : has the config inside it, such as database access details and cloud's preferences.

Create SQL table 'queries'

CREATE TABLE IF NOT EXISTS `queries`
(
`id` int(4) NOT NULL auto_increment,
`querie` varchar(255) collate latin1_general_ci NOT NULL,
`number` decimal(10,0) NOT NULL default '1',
PRIMARY KEY (`id`)
)

cloud_config.php



< ?php

//MySql

$host = 'localhost';
$user = 'USERNAME';
$pass = 'PASSWORD';
$base = 'BASENAME';

//The number of queries you wish to appear within the cloud

$top_q_max = '50';
?>


query_treat.php



< ?php

require ('cloud_config.php');
// Connect to the database
$db = mysql_connect($host,$user,$pass) or die ("erreur de connexion");


mysql_select_db('$base',$db) or die ("erreur de connexion base");


// GET the q value and name it query

$querie = $_GET['q'];


//First we check if the query is already in the table


$sql= sprintf ("SELECT * FROM
`queries` WHERE 1 AND querie = '%s';",
mysql_real_escape_string($querie));


$sql2 = $sql;


$req = mysql_query($sql2);



$rad = @mysql_fetch_array($req);


//Count the number of entries with the same query

$numberofqueries = mysql_num_rows($req) ;//Insert if new

if($numberofqueries == '0' )
{

$jhk = sprintf ("INSERT INTO queries (querie) VALUES ('%s')",
mysql_real_escape_string($querie));


mysql_query ($jhk) ;
}

//End insert if new

else{

//Insert of already in


$newnumber= ($rad['number'] + 1); //Make the number of times this query was searched increase by adding 1


//Update table


$zyrg = sprintf ("Update queries
Set number ='%s' where querie ='%s'
;",mysql_real_escape_string($newnumber),mysql_real_escape_string($querie));


mysql_query ($zyrg);

} //End insert of already in
mysql_close();

?>


show_cloud.php


< ?php
require ('cloud_config.php');

// Connect to database

$db = mysql_connect('localhost',$user,$pass) or die ("erreur de connexion");

mysql_select_db('$base',$db) or die ("erreur de connexion base");

echo ' Top Queries Cloud : '; // Title, you can replace with anything you want

// read the queries from the database while selecting the first $top_q_max queries, ordered DESC by NUMBER

$repmp = mysql_query("SELECT * FROM `queries` ORDER BY `queries`.`number` DESC LIMIT 0 , $top_q_max") or die
("erreur sql ".mysql_error());

while ( $result = mysql_fetch_array($repmp))

{
$p = rand(10,27) ; // will give us a random size in pixels for each query

$ligne = $result['querie'];

$ligne = stripslashes($ligne); //strip slashes we don't need

$ligne2 = str_replace(' ','+',$ligne); //replace the space by +

echo "< a style=\"font-size: $p px\" href=\"search.php?cx=partner-pub-9445783807919064%3A1upz35-igze&cof=FORID%3A10&q=$ligne2\">< font face=\"Verdana\" color=\"#2BA94F\" >$ligne< /font>< /a>".' . ';



}mysql_close();

/*Must replace my search engine url "http://www.torrento.free.fr/search.php?cx=partner-pub-9445783807919064%3A1upz35-igze&cof=FORID%3A10&q=" by yours or leave it this way if you want to
link to TorrentO.

The color is set to #2BA94F it is the
green you see on my site, you can set it to any color you like, in the rest of
the tutorial I will put a random choser for colors.
*/


echo "< a href="\http://net-insider.blogspot.com\">Cloud by Net-Insider< /a>" ; // if you like my job leave this line
?>


Random colors choser

Wait a a little bit, I will add it with a few days I promise ; )