Quering the mySQL DB for statistics results - best way of doing this

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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, :)

-- 
Kim Steinhaug
---------------------------------------------------------------
There are 10 types of people when it comes to binary numbers:
those who understand them, and those who don't.
---------------------------------------------------------------

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux