top 10 results

[scooby545]

hi,

 

i have created a search bar for my site which records the users search data into a table.

 

the table name is `websmart_searches` and its structure is :

 

  `key` bigint(20) NOT NULL auto_increment,
  `search` varchar(255) NOT NULL,
  `user` varchar(255) NOT NULL,
  `blocked` varchar(255) NOT NULL default 'no',
  `date` varchar(255) NOT NULL,
  `IP` varchar(255) NOT NULL,
  `time` varchar(255) NOT NULL,

 

if the user searches for "phpfreaks" it will add a row like :

 

INSERT INTO `websmart_searches` VALUES(6, 'phpfreaks', 'me', 'no', '27th November 2009', '192.168.1.1', '13:52pm');

 

duplicate entries are allowed and needed.

 

what im trying to do is look at the data in the table and extract out the 10 most popular searches, based on an exact match ? does anyone know if this is possible using an sql query ?

 

thanks in advance

 

dave 

 

[fenway]

Why is everything a varchar?

[abazoskib]

hi,

 

i have created a search bar for my site which records the users search data into a table.

 

the table name is `websmart_searches` and its structure is :

 

  `key` bigint(20) NOT NULL auto_increment,
  `search` varchar(255) NOT NULL,
  `user` varchar(255) NOT NULL,
  `blocked` varchar(255) NOT NULL default 'no',
  `date` varchar(255) NOT NULL,
  `IP` varchar(255) NOT NULL,
  `time` varchar(255) NOT NULL,

 

if the user searches for "phpfreaks" it will add a row like :

 

INSERT INTO `websmart_searches` VALUES(6, 'phpfreaks', 'me', 'no', '27th November 2009', '192.168.1.1', '13:52pm');

 

duplicate entries are allowed and needed.

 

what im trying to do is look at the data in the table and extract out the 10 most popular searches, based on an exact match ? does anyone know if this is possible using an sql query ?

 

thanks in advance

 

dave 

 

You could do SELECT count(*) as count,search FROM websmart_searches GROUP BY count ORDER BY count DESC LIMIT 10

 

Your table is highly inefficient. If you are storing both a date and a time, why are you not using a datetime column? An IP address will NEVER be 255 characters long. Neither will a time, or date, username, or whatever a `blocked` is. Even a search term would become a sentence and a half at that point. You should rethink your schema.