[RESOLVED] query issues

asucrews

i am running in to block on how to add a feature to my new project.

Project Link:
http://beta.jeremycrews.com

Issue:
i can not find away to query my database to get the information i needing. what i like to do is when click on the link that showing how many visitors. it this displays list of the country and how many visitors form there.

what i can't seem to figure out is how query need to be made to give me list of the country. i know after i get list of the county i can run another query to get the count for each country.

any helpful hints or links to further reading?

bradgrafelman

Where is the visitor information coming from? Do you have a database table that stores information about active users? You'll have to tell us more about your database schema before we can suggest how to get the information you want.

asucrews

the information coming lookup by ip address, and then being store in a database.

i know that lookup by ip not the most accurate way, so i am planing on having a form to update ther location infomation if need be.

bradgrafelman

Can you show us (or explain) the structure of the table(s) where this information is stored in your database?

asucrews

does this help, or did i miss something?

CREATE TABLE IF NOT EXISTS `visitors` (
  `id` int(3) NOT NULL,
  `time` int(11) NOT NULL,
  `ip` varchar(255) NOT NULL,
  `countryCode` varchar(3) NOT NULL,
  `country` varchar(255) NOT NULL,
  `timezone` varchar(255) NOT NULL,
  `pageOn` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

bradgrafelman

Nope - that's exactly what we needed to know.

So you just want to show a list of countries and a total # of users online from that country? Something like this should work:

SELECT countryCode, country, SUM(countryCode) AS numVisitors
FROM visitors
GROUP BY countryCode

If that query seems to execute slowly, try adding an index on the 'countryCode' column.

asucrews

worked great. made one change, i change sum to count

bradgrafelman

Er, yeah... count... that's what I meant. :o