Chris Haensel wrote:
> Hi guys,
>
> maybe I am too stupid, but I can not see a solution for this. Have been
> cracking up my brain for the last hours, so I finally dare to ask.
>
> I have a database table with some columns
>
>
> text1 | text2 | text3 | text4
> ----------------------------------------------
>
>
> Now, for some stats thingy, I am trying to get the COUNT of distinct entries
> So, i would like to get the count of entries where text2 is foo OR text3 is
> foo
>
> I have 20.863 entries at the moment, and it takes quite a lot of time
> reading through that stuff.
> I have tried
>
> SELECT COUNT(*) as mycount FROM mytable WHERE text2 = 'foo' and text3 =
> 'foo'
>
> but I neither get an error nor any output. It just loads and loads without
> any output...
>
> I am out of ideas ( , anybody? ))
>
> Chris
>
>
>
>
The amount of records is not that big to slow things down. Did you use
indexes on your database? And did you try running the query through
commandline or phpMyAdmin?

-:- -----Original Message-----
-:- From: Thijs Lensselink [mailto:dev@lenss.nl]
-:- Sent: Friday, August 29, 2008 10:31 AM
-:- Cc: php-general@lists.php.net
-:- Subject: Re: [php] Braindead
-:-
-:- Chris Haensel wrote:
-:- > Hi guys,
-:- >
-:- > maybe I am too stupid, but I can not see a solution for
-:- this. Have been
-:- > cracking up my brain for the last hours, so I finally
-:- dare to ask.
-:- >
-:- > I have a database table with some columns
-:- >
-:- >
-:- > text1 | text2 | text3 | text4
-:- > ----------------------------------------------
-:- >
-:- >
-:- > Now, for some stats thingy, I am trying to get the COUNT
-:- of distinct entries
-:- > So, i would like to get the count of entries where text2
-:- is foo OR text3 is
-:- > foo
-:- >
-:- > I have 20.863 entries at the moment, and it takes quite
-:- a lot of time
-:- > reading through that stuff.
-:- > I have tried
-:- >
-:- > SELECT COUNT(*) as mycount FROM mytable WHERE text2 =
-:- 'foo' and text3 =
-:- > 'foo'
-:- >
-:- > but I neither get an error nor any output. It just loads
-:- and loads without
-:- > any output...
-:- >
-:- > I am out of ideas ( , anybody? ))
-:- >
-:- > Chris
-:- >
-:- >
-:- >
-:- >
-:- The amount of records is not that big to slow things down.
-:- Did you use
-:- indexes on your database? And did you try running the query through
-:- commandline or phpMyAdmin?
-:-
-:-
-:- --
-:- PHP General Mailing List (http://www.php.net/)
-:- To unsubscribe, visit: http://www.php.net/unsub.php
-:-
-:-

Hi Thijs,

thanks for the reply. I got mixed up with my tables ) It's got 178.456
entries ))

Anyhow, I tried it in a PHP script.

$gq = "SELECT planned_dep_ap, COUNT(*) as apcount FROM flightdb
GROUP BY planned_dep_ap";
$gd = mysql_query($gq) or die(mysql_error());
while($ga = mysql_fetch_assoc($gd)) {
$icao = $ga['planned_dep_ap'];
$apcount = $ga['apcount'];
if($apcount >= 100) {
echo $icao." -> ".$apcount."<br>";
}
}

this should get me the count of all entries and output it. It looks like it
does it now, as I do get an output. It just takes like 25+ seconds to show
me the output ( Any idea how I can get it faster?

Cheers for your !

Chris

P.S.: Is there a way to order the output by the apcount value? So I can get
highest numbers first?

On Fri, Aug 29, 2008 at 4:38 AM, Chris Haensel <phpmailing@auto-deppe.de> wrote:
>
>
> -:- -----Original Message-----
> -:- From: Thijs Lensselink [mailto:dev@lenss.nl]
> -:- Sent: Friday, August 29, 2008 10:31 AM
> -:- Cc: php-general@lists.php.net
> -:- Subject: Re: [php] Braindead
> -:-
> -:- Chris Haensel wrote:
> -:- > Hi guys,
> -:- >
> -:- > maybe I am too stupid, but I can not see a solution for
> -:- this. Have been
> -:- > cracking up my brain for the last hours, so I finally
> -:- dare to ask.
> -:- >
> -:- > I have a database table with some columns
> -:- >
> -:- >
> -:- > text1 | text2 | text3 | text4
> -:- > ----------------------------------------------
> -:- >
> -:- >
> -:- > Now, for some stats thingy, I am trying to get the COUNT
> -:- of distinct entries
> -:- > So, i would like to get the count of entries where text2
> -:- is foo OR text3 is
> -:- > foo
> -:- >
> -:- > I have 20.863 entries at the moment, and it takes quite
> -:- a lot of time
> -:- > reading through that stuff.
> -:- > I have tried
> -:- >
> -:- > SELECT COUNT(*) as mycount FROM mytable WHERE text2 =
> -:- 'foo' and text3 =
> -:- > 'foo'
> -:- >
> -:- > but I neither get an error nor any output. It just loads
> -:- and loads without
> -:- > any output...
> -:- >
> -:- > I am out of ideas ( , anybody? ))
> -:- >
> -:- > Chris
> -:- >
> -:- >
> -:- >
> -:- >
> -:- The amount of records is not that big to slow things down.
> -:- Did you use
> -:- indexes on your database? And did you try running the query through
> -:- commandline or phpMyAdmin?
> -:-
> -:-
> -:- --
> -:- PHP General Mailing List (http://www.php.net/)
> -:- To unsubscribe, visit: http://www.php.net/unsub.php
> -:-
> -:-
>
> Hi Thijs,
>
> thanks for the reply. I got mixed up with my tables ) It's got 178.456
> entries ))
>
> Anyhow, I tried it in a PHP script.
>
> $gq = "SELECT planned_dep_ap, COUNT(*) as apcount FROM flightdb
> GROUP BY planned_dep_ap";
> $gd = mysql_query($gq) or die(mysql_error());
> while($ga = mysql_fetch_assoc($gd)) {
> $icao = $ga['planned_dep_ap'];
> $apcount = $ga['apcount'];
> if($apcount >= 100) {
> echo $icao." -> ".$apcount."<br>";
> }
> }
>
> this should get me the count of all entries and output it. It looks like it
> does it now, as I do get an output. It just takes like 25+ seconds to show
> me the output ( Any idea how I can get it faster?

This isn't really the same thing you asked in your original post. The
original included a WHERE clause and this version does not. (Granted,
your original post seemed slightly confused as well, since you first
said you wanted results "where text2 is foo OR text3 is
foo", but then your example said "WHERE text2 = 'foo' and text3 =
'foo' ", so I'm not sure if you wanted a union (OR) or an intersection
(AND) of the two matching sets.)

I see in your script that you are only echoing rows where the value of
$apcount is >= 100. Why not limit your query to that? Then you aren't
waiting for a bunch of records to get passed from MySQL to PHP that
you aren't even interested in seeing, and PHP doesn't have to waste
the cycles to filter them out?


> Cheers for your !
>
> Chris
>
> P.S.: Is there a way to order the output by the apcount value? So I can get
> highest numbers first?
>

Absolutely.

SELECT planned_dep_ap, COUNT(*) as apcount
FROM flightdb
GROUP BY planned_dep_ap
HAVING apcount >= 100
ORDER BY apcount DESC

That should do it.

Andrew