scooby545 Posted November 27, 2009 Share Posted November 27, 2009 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 Quote Link to comment Share on other sites More sharing options...
fenway Posted November 28, 2009 Share Posted November 28, 2009 Why is everything a varchar? Quote Link to comment Share on other sites More sharing options...
abazoskib Posted November 29, 2009 Share Posted November 29, 2009 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.