One or Two Querys to count rows?

[sonny]

Please can a experienced PHP person take a look at this below

do I need 2 query's or is there a way to do it all at the same time?

 

$query=" SELECT * FROM visits WHERE page='$page' && vcode='1' ORDER BY id DESC LIMIT 5";
$result=mysql_query($query) or die(mysql_error());
while($row =mysql_fetch_array($result))
{
$lip=$row['ip'];
$id=$row['id'];
$page=$row['page'];

$result = mysql_query ( "SELECT count(*) from visits where `ip`='$lip'" );
while($row =mysql_fetch_array($result))
$num_rows = mysql_num_rows($result);



<td>$num_rows</td><td>$lip</td><td>$page</td>

 

 

this is a very simple example of what I am trying to do, I want to

display the row count for a ip address, then the auctual ip, etc

 

This does not work, does anyone know why? if so can you please point

out why? it keeps displaying "1" when I know my test ip address has

about 20 hits in the database

 

[ldougherty]

You are running a nested query which is a BAD idea because that means for every result on the first query you are running another query on that result.

 

There has to be a better way to accomplish what you are trying to do but we'd need to understand more clearly what you want from your database and we'd need to see an example of the data in your database with structure.

[sonny]

Your right about that query on top of a query man that was crazy you should have

seen what happened when I loaded that!!!

 

My test database has a table named "visits" 3 fields named  "id", "ip_address" and country

I would like to display the total row count for the IP followed by the ip address and country Like:

 

4    243.726.9876    United States

 

I was running a Query to get the info,THE IP ADDRESS, then another Querry to count the rows

related to that ip address, which created a bad never ending loop.

 

Thanks for helping me

Sonny

 

[aschk]

I don't quite see how you're getting a relevance between "country" and "ip_address", and why you would want both in visits table. Because 1 IP will ALWAYS be from the same country.

 

What you want is a count of visits by IP. Then you want to correlate that IP to a country. There's no point storing the IP address and the country together in the same table, because you'll just be duplicating the country information every time that IP address occurs.

 

I think you need to define what tables you have better, and also what you want out of them.

 

To get a count for an IP address:

SELECT ip_address, COUNT(*)
FROM visits
GROUP BY ip_address

[sonny]

I don't quite see how you're getting a relevance between "country" and "ip_address", and why you would want both in visits table. Because 1 IP will ALWAYS be from the same country.

 

What you want is a count of visits by IP. Then you want to correlate that IP to a country. There's no point storing the IP address and the country together in the same table, because you'll just be duplicating the country information every time that IP address occurs.

 

I think you need to define what tables you have better, and also what you want out of them.

 

To get a count for an IP address:

SELECT ip_address, COUNT(*)
FROM visits
GROUP BY ip_address

 

Good question, I only display the first hit for any given ip, I then link them

individually to a more detailed type of display like from and visited data.

 

Example this is what it looks like

(27)  123.456.789  Sunnyvale California United States

(15)  1235.736.87  Toronto    Ontario Canada

 

I figured that count query out, this is what I have now and it works great

I used something like this

 

$result1 = mysql_query("SELECT 1 FROM visits where ip='$ip'");

    $num_rows = mysql_num_rows($result1);

 

Note the result1, which distinguished it from result in the first query

which gets the $ip in the first place

 

Thanks