Counting and displaying number of distinct records

Anon

04/11/2000

I need some help with php/mysql.

I got a db with list of topics and another one that stores which topics where selected, when an order/request is processed.

What i would like to be able to do is :

show on the page that for topicA there are 10 orders , topicB has 5 orders etc

Thanks

Anon

$sql = "select topic, count(*) from orderfile group by topic";

Anon

Sorry but i dont think i am using this correctly. As there are two seperate tables,

topics , has topicid, topicname
orders, has topic and a few other fields

what i want to show is :

topicname (x amount of orders) and so on for each topicname

Thanks

Anon

Is the "topic" field in the orders table the same as the "topicid" or "topicname" in the topics table?

Anon

The topic field in the orders table is same as the topicID in the topics table.

Anon

Try:

SELECT topic, topicname, COUNT(*) from orders, topics WHERE topic=topicid GROUP BY topic, topicname

Anon

Thanks for all your help, i had just managed to figure out the sql bit before u posted your sql response. Unfortunately i am also having problems on how to display the result once the sql has run. I having trouble getting my head round to the php bits 🙁

Thanks

Anon

In PostgreSQL:

$result = pg_exec($conn, $sql);

$row = 0;
while ($data = @pg_fetch_object($result, $row)):
print $data->topicname." : ".$data->count."<br>\n";
$row++;
endwhile;

pg_close($conn);

Anon

Once again, thanks for all your help on this. Being a total newbie on mysql & php, and not knowing anything about postgresSQL, i dont know how to display the results using php. I have got it kind of working, but it shows the incorrect info. My code is :

$result = mysql_db_query($db,$query ,$connection)or die("Error in query");

$countx = mysql_num_rows($result);

for ($i=0;$i<$countx;$i++) {

$A = mysql_fetch_array($result);

print $A["TopicName"] . "(" . $countx . ")";

}

and it shows :

Test1(2)Test2(2)

when there a 2 records of test1 and 3 of test 2

Kass

Anon

Well, you retrieve the number or result rows into $countx and then use that in your output. It never changes, so every time you output, it will always print "2" because thats how many topics you have (test1 & test2).

Since mysql_fetch_array() puts each result row into an associative array, you should reference the count for each row (in your example) as $A["count"] since that is what your querry will name the count total by default.

Also, since the mysql_fetch_array() function automatically increments the result row each time you call it, you don't even need the retrieve the number of result rows with the mysql_num_rows() function or use a FOR loop. To output the results, you can more simply use:

<?php

$sql = "SELECT topic, topicname, COUNT(*) from orders, topics ".
" WHERE topic=topicid GROUP BYtopic, topicname";

mysql_connect($host,$user,$password);
$result = mysql_db_query("YourDatabaseName", $sql);

while($row = mysql_fetch_array($result)) {
print $row['topicname']. "(" .$row['count']. ")<br>.";
}

mysql_free_result($result);

Hope this helps.

-- Rich Rijnders
-- Irvine, CA US

Anon

Thanks once again. I hope i am not taking up too much of your time.

I have run the code u sent & now all i get is

Test1()
Test2()
Test3()

it prints the topic name, but does not do the count.

The sql code that i am using is :

// formulate the SQL query

// i have changed the topics to TopicID

$query = "SELECT qnatopics.TopicID, qnatopics.TopicName, qnaquestions.TopicID, COUNT(*) FROM qnatopics, qnaquestions WHERE qnaquestions.TopicID = qnatopics.TopicID GROUP BY qnatopics.TopicID, TopicName" or die("Error in query");

// run the query on the database

$result = mysql_db_query($db,$query ,$connection)or
die("Error in query");

while($row = mysql_fetch_array($result)) {

print $row['TopicName']. "(" .$row['count']. ")<br>";
}

mysql_free_result($result);

I have run the above sql in phpmyadmin & display the correct info.

Anon

It's no problem at all...

Your program looks good, the only part I am not sure about is if mySQL returns "count" as the column name for the COUNT(*) function. PostgreSQL does for sure, but I am not sure about mySQL. When you run the query in phpmyadmin, does it display a column heading over the count? If so, use that as your array reference (ie $row['whatever']) to reference the count.

Also, mySQL may be case sensitive regarding column names so try "COUNT" or "Count", etc in your array references.

If that doesn't work, try putting "AS myTotal" or something like that after COUNT(*) in your query to name the column yourself. Then use that name to reference the column in your output.

-- Rich --

Anon

At last , its working.. i has to use COUNT(*) to print out the count value

Thanks alot

Anon

No problem, glad to help.

-- Rich --