SELECT COUNT(*) AS count, * FROM stats GROUP BY ip
Note that this will not work on Oracle and some other DBs. It will work in MySQL. Oracle will only let you do:
SELECT COUNT(*) count, ip, FROM stats GROUP BY ip
Justin Patrin wrote:
You can do it all in one query, just as you're already doing it:
SELECT COUNT(*) AS count FROM stats GROUP BY ip
Kim Steinhaug wrote:
Hello,
Im about to write a script that will generate several reports from a database which loggs all visits. I see that my script will need alot of queries against the database whenever a report is generated, and I would love to get some input from you if my way of solving this is bad.
Example, I have a database filled with informations stored from each visit, I will only focus on the IP data just here. What I want to do is, pull out the visits ald present theese on the screen. This is 1 query, and the result gives me a mile long page. To make it a little better I want to group all the IP, this will give me a much shorter list. From what Ive understood the "group by" statement will solve this, but then I wont know the exact number of each IP. I therefore need to do an extra database query for each and every IP, right?
Look at this code :
function query(){ // DB Abstraction layer }
$allrecords = query("select * from stats group by ip");
for ($i = 0; $i < mysql_num_rows($allrecords) ; $i++) {
$record=mysql_fetch_object($allrecords);
$temp = mysql_fetch_object(query("select count(*) as count from stats where
ip='".$record->ip."'"));
$ipcount = $temp->count;
echo 'Echo out needed $record data, this ip has ' . $ipcount . '
entries.<br>';
}
echo $i . " Records total<br>";
This code will generate a heck of a lot of queries againt the mySQL database, but is it possible to do this another way? This is just one problem, other reports may dig further into the database and require more queries if using the above method.
By the way, if you know of any recourses for report making and such I would love to see them, :)
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
