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" <jpatrin@xxxxxxxxxxx> wrote in message
news:20031124175521.55812.qmail@xxxxxxxxxxxxxxx
> 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.<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
