MySQL Count(*) Problems....

[wsjb78]

Hello,

I have a table in a database with two fields:

1.) id
2.) incoming

I need now to know what kind of query I do have to do in PHP to count the number of entries for each id (where incoming='1'), sort them descending and then I need to access the data (id and the number).
I've tried this the following code but I just get error messages returned (Supplied argument is not a valid MySQL result resource).

$i=0;

$query = "SELECT id, COUNT() FROM $table1 WHERE incoming='1' and id!='' GROUP BY id order by COUNT() DESC";
$insert = MYSQL_QUERY($query);

$id = mysql_result($insert,$i,"id");
$id = stripslashes($id);
$in = mysql_result($insert,$i,"COUNT(*)");

[Anon]

hum... I guess you'll have to use two querys:

$query1="SELECT COUNT(*) FROM $table1 WHERE incomin='1' and id>''";
$query2="SELECT id FROM $table1 WHERE incoming='1' and id!='' GROUP BY id order by id DESC";

but your code will be more efficent (less calls to MySQL) if you only use:

$query="SELECT id FROM $table1 WHERE incoming='1' and id!='' GROUP BY id order by id DESC";
$result = musql_query($query);
$data = mysql_fetch_array($result);
$n_of_rows=count($data);

that is, get all your SELECT statement retrives, and count them with PHP.

BTW as far as I know you can't order rows by count() in your SELECT because count() result is only a number and is not associated in any way to any row of your table.

[Anon]

... and a simpler form to count results from a query:

$n_or_rows = mysql_numrows($result);

[wsjb78]

Hello Alejandro,

well, thanks for your help, but it's not quite what I'm looking for...

Well, I'm working on a stats application. That query should display all referrers ordered by how many hits they sent me in the last 24h. Each access to the homepage is being logged in the MySQL DB with a new entry. That's why I want to do this combined "id, count(*)" query.
Incoming='1' means that this id (referrer) hast sent me one hit, incoming='0' means I have sent one user to the referrer.

Well, I try to do now a little list of what it should look like then:

ID | incoming

ref1 1
ref1 1
ref1 0
ref10 1
ref1 1
ref100 0
ref100 1
ref1 1
ewf100 1

I need then to order this by ID and I need to retrieve how many times a unique ID has a value of '1' in the incoming column and this should then be ordered desc the count value

ID | Count

ref1 4
ref100 2
ref10 1

and then I need somehow to access the ID and its count value....

What I've been also thinking on is just making a query like this:

$query = "SELECT id, COUNT(*) FROM $table1 WHERE incoming='1' and id!='' GROUP BY id;
$insert = MYSQL_QUERY($query);

wouldn't that return some kind of array, which then could be ordered by PHP??? Well, I admit, my knowledge on arrays is not very good and I try to avoid them as often as possible.

Thanks for your help,

Stephan

[steved]

Try this:

$query = "SELECT id, COUNT(*) AS idcount FROM $table1 WHERE incoming='1' and id!='' GROUP BY id order by idcount DESC";
$insert = MYSQL_QUERY($query);

$id = mysql_result($insert,$i,"id");
$id = stripslashes($id);
$in = mysql_result($insert,$i,"idcount");

[wsjb78]

Hello Steve,

some friend of mine has just the same thing suggested and guess what, it works absolutly fine!

Thanks for all your help.

Stephan