[SOLVED] Counting rows by type?

[lynxus]

Hi all,

 

I have a table that holds x amount of IP addresses and their locations..

 

IE:

x.x.x.x gb

x.x.x.x us

x.x.x.x jp

x.x.x.x jp

 

And so on.

 

What id like to do is attach this data to a chart so i need to have the data in some form of decent values first.

 

How can i do a select * from thistable limit 1000 ( so i get the last 1000 hits )

 

Then put it into php vars like.

 

gb = 1

us = 1

jp = 2

 

( if heres any others it would create the vars also.. )

 

So lets say there was one for ro. it would create

ro = 1

 

 

Thanks for looking.

Regards

Graham

 

 

[kickstart]

Hi

 

Not sure exactly what you want. PArtly sound like you want a count of all the IP addresses for each country, and the following would give you that:-

 

SELECT Country, COUNT(IP) AS IpCount

FROM SomeTable

GROUP BY Country

ORDER BY IpCount

 

However also sound like you want the data from another table.

 

All the best

 

Keith

[lynxus]

Hi,

 

What i want to do is count how many IP's belong to what country.

 

so if the table had 13 ips with its country value of gb then in php i would like

 

$gb = "13";

 

and so on for all other ips so afterwards i can then plot these on a graph.

 

But i would need to build the vars dynamiccly as i have no idea wha country values are in the table.

Sometimes it may be full of gb's and other  times it could have upto 10 different countrys.

 

I hope this clarifys?

 

[lynxus]

Anyone?

heres a copy of a small bit of the table:

And what i would like outputted to php?

 

select ip,country from visitorips;

 

      41.6.154.66  za

198.54.202.214 za

41.3.237.233 za

203.210.254.5 vn

113.22.181.123 vn

113.22.24.175 vn

113.22.181.123 vn

123.19.160.61 vn

190.204.66.112 ve

190.204.66.112 ve

166.137.4.14 us

71.197.58.199 us

68.214.87.74 us

68.154.244.219 us

 

I would then want php to make a bunch of vars like:

$us = "4";

$ve = "2";

$vn = "5";

$za = "3";

 

Any idea how to achieve this?

 

I can then put into a graph some values ie:

4 hits from us

2 hits from ve

etc.

 

 

Thanks

G

[kickstart]

Hi

 

Some example code to get you an array of countries, keyed by the country abreviation, with each member having the count of ips in that country:-

 

<?php
$dbms = 'mysql';
$dbhost = 'db.host.com';
$dbname = 'yourdbname';
$dbuser = 'yourdbuserid';
$dbpasswd = 'yourdbpassword';

// Make the database connection.
$conn = mysql_connect($dbhost,$dbuser,$dbpasswd) or die(mysql_error());

mysql_select_db($dbname,$conn);

$sql = "SELECT Country, COUNT(IP) AS IpCount FROM SomeTable GROUP BY Country ORDER BY IpCount";

$IpCount = array();

if ( ($result = mysql_query($sql,$conn)) )
{
while ($row = mysql_fetch_array($result))
{
	$IpCount[$row['Country']] = $row['IpCount'];
}
}

?>

 

All the best

 

Keith

[lynxus]

Hi,

Thankyou for your time on this.

 

Im reallllly new when i comes to arrays and i find it hard to get to grips with them.

 

How would i echo the data? so i can use the result data?

 

I think teh main problem is, i have no idea what country codes will be in the array?

 

So if i were to create a graph based on this data.

IE: gb = 123 users.

I would need to know what countrys are included already?

 

for example, heres an output from the array:

ArrayArray ( [:us] => 1 [it] => 1 [ir] => 1

---

=> 1 [ar] => 1 [ro] => 1 [ch] => 1 [cy] => 1 [ua] => 1 [network:organization-usa] => 1 [mx] => 2 [pk] => 2 [mk] => 2 [nl] => 2 [kw] => 2 [ba] => 2 [dk] => 2 [nz] => 2 [my] => 2 [vn] => 2 [sa] => 2 [ps] => 2 [ie] => 3 [th] => 3 [se] => 3 [gr] => 4 [de] => 4 [jp] => 4 [ph] => 4 [AE] => 4 [sg] => 5 [be] => 5 [au] => 5 [ca] => 6 [eg] => 6 [pt] => 7 [iL] => 8 [tc] => 8 [hk] => 10 [id] => 13 [in] => 14 [cz] => 17 [za] => 19 [no] => 21 [gb] => 345 [uS] => 961 [] => 3174 )

 

i suppose maybe a while loop would do the job?

humm i dunno lol

 

Sorry

Thanks again

Graham

[lynxus]

Ah ha,

Ive figured it

YAY

 

I dont suppose you know how to limit the query to only the last 1000 rows in the DB ?

There is a primary key of ID.

 

[lynxus]

using LIMIT 1000 at the end doesnts eem to work

 

[kickstart]

Hi

 

LIMIT 1000 would limit it to 1000 output rows. Doubt there are 1000 countries.

 

I presume what you mean is that you just need the country counts from the last 1000 rows. As such you might have to do a subselect:-

 

SELECT Country, COUNT(IP) AS IpCount

FROM (SELECT Country, IP FROM SomeTable ORDER BY DateStored DESC LIMIT 1000) Deriv1

GROUP BY Country

ORDER BY IpCount

 

All the best

 

Keith

[lynxus]

Thankyou , All works perfectly now

 

Thanks again.