[PHP-DB] Re: Quering the mySQL DB for statistics results - best way of doing
Thanks for your ideas, im sitting here drinking beer and drinks, so
Im having a little difficulty grasping the whole idea with this new
SQl statement, but it works! Thanks a lot.
To help the other people reading this who might not get it I did
some benchmarking, my method (mentioned in the beginning in
the thread) :
Time -> 0.3663
I altered the routine, from 114 nested queries (my dataset) into
1 single query from Justin Patrin like this :
$sql = "select ip, count(*) as ipcount FROM stats Group By ip";
$allrecords = query($sql);
for ($i = 0; $i < mysql_num_rows($allrecords) ; $i++) {
$record=mysql_fetch_object($allrecords);
echo $record->ipcount . '-';
}
Time -> 0.0101
Remarkable difference indeed! Im just happy that I just made 1 single
report script at the moment using my rather terrible queries... :)
Thanks for the help Justin!
--
Kim Steinhaug
---
There are 10 types of people when it comes to binary numbers:
those who understand them, and those who don't.
---
"Justin Patrin" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Whoops, forgot the rest of the fields.
>
> 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.';
> >> }
> >> echo $i . " Records total";
> >>
> >> 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
[PHP-DB] Re: Quering the mySQL DB for statistics results - best way of doing
Whoops, forgot the rest of the fields.
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.';
}
echo $i . " Records total";
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
[PHP-DB] Re: Quering the mySQL DB for statistics results - best way of doing this
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.';
}
echo $i . " Records total";
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
